How to: Quickly check if data exists in your SQL table
I was confronted with a slow performing stored procedure in SQL server this week. The procedure was to retrieve one row from a table. But before that row could be retrieved, an update procedure was called to make sure all rows in that table had the correct status. That update procedure first checked the number of rows in a table to see if there were rows to be be updated. It used a piece of SQL similar to this:
CREATE PROCEDURE dbo.spMyProcedure
AS
SET NOCOUNT ON
IF (SELECT count(UserID) from UserTable) > 0
BEGIN
-- Process the items in the table
END
GO
This will obviously work. But on a table that contains more than 150,000 rows this can take some time. But why count the number of rows if all we need to know if there is at least one row present. I changed the IF statement a little and gave the procedure a new name so that I could compare both:
CREATE PROCEDURE dbo.spMyProcedureNew
AS
SET NOCOUNT ON
IF EXISTS(SELECT top 1 UserID from UserTable)
BEGIN
-- Process the items in the view
END
GO
I then called both with the Execution plan on and found this result:
A logical conclusion, just checking if there is one row in the table is much faster than checking of the number of records is more than zero. When you compare the subtree cost of the old procedure (0.726) with the subtree cost of the new procedure (0.00641), you can see a performance gain of a factor 113 . But how does SqlProfiler look at both queries? Well, here's that result:
As you can see, the duration of the old procedure averages at 34.4 and the new procedure averages at 12.2. The new proceduer is still a factor 2.8 faster than the old procedure. But you'll also need to look at the difference in CPU time (0 for the new procedure and 31 for the old procedure) and the number of reads (19 for the new compared to 709 for the old procedure) to conclude that checking for at least one row is a lot more efficient than counting the number of rows.