Today I share a tip for ne SQL developers that will help with performance. As much as is reasonable, if you build your queries, procedures views and functions to access tables in a consistent order, it can have a big impact on reducing contention when multiple queries are execute.
If query A needs a table held by Query B, but query B needs a table locked by query A, you get what is known as a deadlock. If all of your queries request tables in the same order, it makes it harder for a deadlock, or even a block to occur.
Be aware that you really don’t have much control over the order in which tables are accessed in your queries. The is a command that I haven’t used for 15 years, SET FORCEPLAN ON, which does allow you to specify execution order. I have found that a database with proper indexing and statistics will make it virtually impossible for you to write a query using FORCEPLAN that performs as well as the query produced by the query optimizer in SQL Server 2000 and later.
Using CTEs and other techniques, we do have the ability to force query execution. Instead of writing one big query we might use one or more CTEs to break the work up. This is a great situation where you have the opportunity to address tables in a consistent order. This technique is often found in inline table functions, views or stored procedures.
In stored procedures you’re more likely to find similar methods forcing the order of table acces by using inline views, CTEs (Common Table Expressions) or temporary tables work breaking down the queries into manageable pieces.
Share your own tips, or add to this one in the comments provided.
Cheers,
Ben