Monday, May 4, 2009

Fastest way to check for existence in a table

In order to improve performance on one of the pages in our Java code, I was making a SQL query which, along with the typical section grade information, also pulls in a field to tell whether that particular grade type is in use.  Between my own brains and some quick Google searching, this was the best query I could come up with:
1
2
3
select sg.*,
(select top 1 1 from section_roster sr where sr.section_grade_id = sg.section_grade_id) as isUsed
from section_grade sg
I assumed that in the absence of any ordering, "select top 1 1" would be converted by SQL Server into a sort of "exists" statement, and would therefore be the fastest query for the job.  But just out of curiosity, I ran a similar LINQ query in LINQPad to see what SQL would be generated.  Based on those results, I created the following query:
1
2
3
4
5
select sg.*,
(case when exists(select null from section_roster sr where sr.section_grade_id = sg.section_grade_id) then 1
else null
end) as isUsed
from section_grade sg
Although it's not as simple a query, I was able to drop the execution time from about 27 milliseconds to about 9 milliseconds.