Wouldn't it be easier to have any search request pass parameters to a sql server stored procedure so that the searching can occur in the actual sql server database ? This would of course require the writing of the invoke call and the stored procedure.
Not really.
Well, overall it's always a good idea to use stored procs, especially on frequently-called routines. Because that gives SQL Server an opportunity to build an execution plan for the query to figure how best to deal with it, and on subsequent calls, it uses that execution plan.
With Transactional-SQL (complete SELECT statements being sent to the db by the webserver), SQL doesn't have a pre-built execution plan it can use. The overhead involved in determining the best method of execution (which I assume mostly involves SQL determining which indexes are available to service the incoming query) isn't extremely high, but it's high enough that stored procs are *always* preferable to T-SQL for a query that might be getting run hundreds of times per second. Those are the queries where that little bit of overhead can seriously add up. That's why routines like readmsg and the one that gets all of your user data (userlevel, account number, preferences, etc) definitely must be run as stored procs.
But a pet peeve of mine (reading this, Dave?) is using stored procs for routines that are called relatively rarely and wouldn't run noticeably more slowly or expensively with T-SQL. It's because, to me, the tiny performance gain from making SP's for everything isn't worth the cost in my annoyance and productivity when having to scroll through hundreds of SP's to get to the one I want to edit.
And in the case of Advanced Search, handling it with SP's would require a large number of SP's and I'd have to programmatically determine which SP to use based on which fields the user decided to use as criteria.
I build the joins and where clause (and even the FROM) on the fly depending on which fields the user selected. And I think there are 5 selections possible besides the search text input box. I'm not sure how many different scenarios that makes. 2^5? 3^5? 3 might be the number since the choices for boxes like Author are All, All Peoplemarked, or a specific author. I'm not a heavy-duty math guy, so am not sure how to determine how many procs would be needed, but it looks like it'd be in the "a lot" range.
And I'm not sure that you can, for example pass "INNER JOIN table1 ON table2" (and keeping in mind that there could be 1 through ~4 joins) as an SP parm then do a "SELECT * from message @joinparm". If you can, it'd reduce the number of SP's needed, but I think it'd completely circumvent the query execution planner, making the SP perform no faster than the T-SQL.
Even then, it wouldn't address the real bottleneck: MSSearch. MSSearch has to be called either way.
In this case, T-SQL is far easier on the programmer (yours truly) and even if it were possible to see performance gains from using SP's, it'd break my rule of a routine having to be used very often and being very noticeably easier on the system before it becomes an SP. |
