Optimization of stored procedures in MS SQL Server 2000/2005

Some time ago I wrote about cases of poor performance of stored procedures: razbezhkin.habrahabr.ru/blog/24231.html



I describe how you can fight it.



The logic is very simple: let's say that poor performance is due to the fact that when the stored procedure was first executed, the execution plan was optimized to retrieve one row. The next time you run the same stored procedure, but using the input parameter that results in a selection of a large number of rows, the same execution plan and compiled code will be used as in the first case.



This problem is solved very simply: if it is possible to predict the dependence of the number of rows in the sample on input parameters, for example, in such a query: SELECT * FROM Tab1 WHERE x <@ param1

then the solution will be as follows:

Make two copies of the stored procedure, call them with different names, but their program code is the same. Write a third stored procedure that, depending on the parameter value, calls either one or another procedure.

')

This leads to the fact that each of the two procedures will be optimized and compiled according to its number of rows returned: the first for small and the second for large.