February 1, 2006 at 3:08 pm
greeting,
im using sql server 2000 for a server client application.
I need to filter a HUGE query by lots of criteria , now lets suppose we have the string in "where" format or in "recordest filter" format.
I need to know if its better to put the result of a query in a temp table, and then sp_execsql the where conditions(which i send as a parameter to the procedure) on that table , all this in a stored procedure offcourse
or
simply run the huge query and then, on the client apply the filter on the recordset returned by the filter
February 1, 2006 at 3:51 pm
It depends what you mean by huge
I had a requirement to produce a search that had certain compulsory search terms and a number of optional ones.
What I did was have a master calling procedure that evaluated the passed arguments and decided which way to route the search.
If the query simply required the mandatory arguments then it ran the mandatory argument query.
If the query required mandatory arguments plus certain complex logic then it ran the complex argument query.
If the query required searching on non-indexed monetary values then it bounced the results from either of the first two queries into a temp table then applied a filter to that temp table to reduce the recordset further.
The beauty of doing it this way is that each query is optimised to carry out a specific task. Trying to do it all in one is likely to require a query that recompiles every time that it runs.
February 1, 2006 at 11:00 pm
lets suppose that the applications returns some data for partners, products, field agents, accounts (and 10 more categories), each categories is shown in a grid in which the user can check uncheck each item.
and the final filter will look like (depending on what is checked in the grids) :
'(partner="A" or partner="B") and (product'X')and (productY)' and so on and so on.
i was asking if its better to run the initial querry(unfiltered) in a stored proc, and place the result in a table, afterwhich run another query with sp_execsql on that table
to filter it, the second querry will have the where clause sent as a parameter to the proc
this involves the creation of a temp table , and a slower procedure
the second option is to simply filter the results in the client, using recordset's filter property. But this means that the entire unfiltered data will be sent to the client, faster procedure but more network traffic.
I simply can't decide which option is best.
and the initial querry is quite big, but that doesnt matter, some unions, some joins, some grouppings etc
February 5, 2006 at 9:04 pm
Alternate option. Why not change your initial query to use sp_execsql ? That way, you get the result the first time. Instead of query result to temp table and then sp_execsql from temp table to client.
February 6, 2006 at 9:38 am
because i wanna reuse the plan (or maybe i am completely wrong) of at least for a big part of the sql, and then simply requery the result with some more conditions ....
February 6, 2006 at 3:15 pm
Have you considered using a derived table inside the stored procedure rather than a temp table or are the parameters too varied?
February 7, 2006 at 7:15 am
does the derived table make a big difference? i dont know ....
because all your answers were server solutions ... i will choose the server (procedure, table,query) solution instead of the client(ado,record filter) solution
February 7, 2006 at 7:17 am
thank you for your answers
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply