So, last week I was playing with a script to show me bad queries and I came upon something very interesting. Of the five worst queries via elapsed time, four of them looked very similar. They all had average worker time of just 14 ms, but nearly a million executions. And they were all on the same table.
It didn’t take me long to track down my code:
So, what is this doing? For a given shift and job, it’s getting the amount of time that they coincide. They can coincide in four ways:
Well, this code can and should be rewritten to use a single query:
So, in this code we’re simply worried about finding the shifts that have any sort of overlap. Then we use the code to figure out which dates to use.
I was also able to reduce the amount of times this function was called by 50%.
One last change was that I changed the select statement to only return two fields instead of the entire table. I was wondering if this would make a difference. Turns out, the new query also runs at 14 ms. So, that wasn’t a significant difference.
Joseph's Code of many colors 