Quick and Dirty
By on February 21, 2022
I recently ran across a poorly performing query that needed a quick fix. See if you can spot an issue:
Show me all the records from Table1 whose UniversalID exists in Table2 but does -NOT- exist within Table3. Certainly not ideal but there is a very simple tweak that could be done to improve performance with a simple tweak. Enter our friend EXCEPT.
If you are not familiar with the EXCEPT operator it is a simple way to compare two result sets of two queries. Query EXCEPT Query. This returns distinct rows from the left query that do not exist in the right output query. The INTERSECT operator would return distinct rows that exist in both the left and right queries. You can read up more on these two operators here.
Here is the quick change I introduced:
Could this be tuned further? Most certainly.
Does this improve performance? Execution time dropped by about 92% and drastically reduced blocking on an active system.
Don’t let perfect become the enemy of good, especially when working on a very short turn-around timeframe. Sometimes a quick and dirty code update can work nicely.
Categorised in: Blog
