Subscribe Feed (RSS)Check if a record exists using IF EXISTS instead of COUNT(*) for increased performance
August 12th, 2007 by Sameer | Filed under SQL.Summary:
- How to find out if a record exists in a table efficiently using the IF EXISTS keyword, and
- How to test two queries to see which is faster using SQL Server Management Studio. This articles teaches you how to tune and tweak your queries.
If you are writing a query to find out if a record exists in your table, you might not be doing it the fastest way if you are using COUNT(*).
i.e. SELECT COUNT(*) FROM USERS where USERID=@UID
and then from your code you are checking if the count is greater than 0.
This can be efficiently re-written as
IF EXISTS (SELECT * FROM USERS WHERE USERID=@UID) select 1 else select 0
That is very easy. It is much more efficient.
How do we know its more efficient?
You can use SQL Server Management Studio to find out. What you have to do is write your two queries,
--testing variables declare @UID int set @UID = 1 --actual query 1 SELECT COUNT(*) FROM USERS where USERID=@UID --actual query 2 IF EXISTS (SELECT * FROM USERS WHERE USERID=@UID) select 1 else select 0
Select all, Click on the button to generate the query execution plan (screenshot below - click to enlarge)
And you will see that it will give you a percentage breakdown for each query. The first two lines, creating testing variables, you can ignore. However, looking at the last two queries will show you the breakdown of effort required to run those queries. It will be similar to 99% for query 1, and 1% for query 2. This is a quick way to find which query should run faster and you can tweak the queries until you get the results you want.
What that means is 98% of the work has to go into the first query (out of 2) and 2% for the second. That gives you a rough … idea… of which one is faster. Sometimes it will be more evenly broken down, closer to 50/50, and in that case they are roughly equivalent.
