Thursday, November 13, 2008

Be Discrete

Well, sometimes being discreet is being efficient :)
I recently had to optimize some reports for speed and I found one query that took around 8 seconds.
The query, a pretty simple select was doing

SELECT blah, blah
FROM theTable
WHERE Year <= @Year
AND Year > @Year - 10

What I did was using the ugly, but efficient

SELECT blah, blah
FROM theTable
WHERE Year IN (@Year-9, @Year-8, @Year-7, @Year-6, @Year-5, @Year-4, @Year-3, @Year-2, @Year-1, @Year)


The execution time dropped from 8 seconds to about 900 ms.