This can work, but it's kind of a hack to force the optimizer to work more effectively for your query. Now make your original stored procedure check the parameter values and dispatch to the appropriate one of the two stored procedures from the previous paragraph. You might have to tinker with the query to get this to happen, or this may not be possible to achieve for your query, in which case this approach won't work. Look at the query plans - one should be optimized for one set of parameters, the other for the other set. Add where clauses to each such that between them they cover all possible cases. The technique is this - break the stored procedure into 2, one meant for one set of parameters, one for another. You could also attempt to decide for the database which plan to use, though you would be fighting with the optimizer a little bit, so it's more brittle than you would hope. Is there any other way to deal with this issue? This feels like a step back to me and I feel like there must be a way around this. The options presented to me are the move that problem query from a stored proc and back into dynamic SQL that has it's execution plan created on every run. He said that it was a good plan for that set of parameters, but if you throw a certain set of parameters at it, then the plan will not be the best plan for that data, and so you will see it running slow. He has told me that the database created a query plan when we created the stored proc. So we recompile the stored proc and -bang- it runs now in 300ms. So we examine the query, run it in SSMS and and find that it takes 30s. The problem: what we've experienced is that suddenly we will get a number of errors (typically Execution Timeout Expired or similar) for a particular client while they try execute that stored proc. Now depending on the dates, and the customer, this query can return anything from zero to 1000s of rows. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, and two dates. I'm trying to understand an issue we're having with SQL Server 2000.
0 kommentar(er)
