By using OPTION (RECOMPILE), we will incur a cost to compile the plan every time it runs, but for a stored proc that use to run in 20 minutes to now run in 20 seconds, this works out well. We don’t want to add a heap of new indexes, or maintain some sort of indexed view for this query. This stored proc is run frequently for about 4 weeks of the year. SQL has to take the time to decide again what the best way is to retrieve the data you requested.įor this scenario, it appears to be the best option. OPTION (RECOMPILE) does exactly what it sounds like -it recompiles the code and finds a new execution plan based on the parameters passed in. I ended up deciding to add OPTION (RECOMPILE) to the critical select statement in the stored proc. I spent some time changing indexes and trying to get a plan that would be suitable for all (or almost all) scenarios but I couldn’t find a suitable option. I needed it to pick the best option every single time. If it picked the wrong option, it could run for 10-20 minutes. When the stored proc picked the best plan for the parameters passed in, it would run in about 10-20 seconds. However, it would then cache that plan and use from then on. When I cleared my cache and ran each of the test scenarios, it would always pick a great option to filter the data as quickly and efficiently as possible. The problem was that depending on the data chosen from table1, it could be better to filter by either table2, table3 or table4. The data from all of the tables was then compared with the other 3 tables to get a final result. A very small table (table1) was being joined to 4 large tables (table2, table3, table4). In this case, the data was all over the place. In most cases, this is the best thing for SQL to do, as the plan it comes up with is usually the best option. When SQL Server first runs a stored procedure, it works out the ideal execution plan based on the parameter passed in and saves it for next time. So I tried to work out how to make it run well for all scenarios. This week, I was trying to improve the performance of a bit of SQL and noticed that it generated significantly different query plans depending on the parameters passed in.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |