Today I’m referring back to an old technique that still has some value when it comes to optimizing queries. If you are consistent in the order you join tables, and in the order of columns you use for filtering tables, you can sometimes gain a more optimal performance on your queries.
Why would this be important? Most of your SQL engines have query tuning built into the engine; some are more sophisticated than others, and have less benefit from this practice; others find this practice quite helpful. It works because if you are consistent in the way you join and/or filter tables, you will ultimately define and maintain indexes and statistics for those paths. Moreover, if there are fewer paths you used to filter or join, then the number of indexes and statistics needed to perform your queries are reduced, resulting in better performance because there are less indexes and statistics to maintain.
So, while we can spend a lot of time analyzing our statistics, and trying to consolidate them into fewer, more complex statistics, by simply eliminating the need by using common search paths can provide some additional boost.
.
If you have servers with SSD and lots of memory, you will probably have less value returned from this practice. However, if you are running personal databases or in memory databases, this technique can be highly useful. As our industry is adopting more in memory sql storage, such as Sqlite, or even embedded SQL Server, this practice should, in my opinion, become a standard method for your development team. It isn’t difficult to learn or implement. Even if the performance boost isn’t there, you communicate your intentions more clearly if things are consistent.
Am I suggesting you should always follow this practice? I don’t think so. If it isn’t hard, and it simplifies the understanding of your team, and if there is the potential for better performance, then go for it.
I’m sure there are some practices you have that you just like. They may have minimal value, but you still exercise them. How about sharing some of your preferences in our comments?
Cheers,
Ben