Sometimes we get the chance to optimize code. It feels like those opportunities are too rare; but, they do come every once in a while, through good planning or dire need. Either way, we look for those small things that add up, resulting in better performance.
I gave a few tips a while back with things such as using common data types. This works well in application code or database code. The cost for casting a few data types is pretty small. Anyone who has writing code that loops knows that continued casting can drastically reduce performance.
Loops are another thing that can reduce the performance of your application. Let’s talk about a common SQL filter syntax. There are two operations that can diminish the performance of your query, irrespective of the available indexes. Those operations are OR and IN.
In reality, OR and IN can result in the same thing when the query is executed. OR allows you to specify more than one condition. If both conditions are against the same variable, it can be the equivalent of the IN syntax. The following two filters are the same.
WHERE State IN ('CA', 'AL', 'MI')
WHERE State = 'CA' OR State = 'AL' OR State = 'MI'
When either of these queries executed on older versions of SQL Server, an individual pass was made through the table or index, locating the matching values for each scalar comparison. In this case, the three sets would be unioned together providing a final composite result. That meant that our example query performed the loop three times through an index, or a table if no index was available.
SQL Server has optimized the query engine to reduce the redundant loops. No surprise here. Getting rid of loops in code increases performance; especially when you are looping over the same set each time.
Let’s say you are working with an older version of SQL Server, or another engine that is not optimized for multiple OR conditions. Do you have any options that perform better? It turns out that you may. One of the things all SQL engines have to do very well is join sets together. That’s what a relational database is all about. Back in the day, one way to optimize OR queries was to use a filtering table. Place the desired search values into the table, and then simply join your filter table to your data table. If your database doesn’t support temporary tables, you can create a permanent filter table for this purpose.
Keep an eye out for those filters with many OR operations on the same column, or using IN. You may find a good opportunity for optimization.
Cheers,
Ben