Efficient way to check if a SQL query will return results

Question

I would like to write a query that simply returns 1 or 0 depending if there will be results.

I am thinking to use this

IF EXISTS(
      select * from myTable 
      where id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
)
SELECT 1
ELSE
SELECT 0

That's the general premise.

The final results will actually be a far more complex query, taking one to many parameters and the string built up and executed using sp_executesql

My question is lets say the 'count' would return 376986 and takes 4 seconds to calculate. Is using the IF EXISTS going to stop as soon as it find 1 row that satisfies the criteria.

I'm deciding wether to use IF EXISTS or just query the @@ROWCOUNT and see if it is greater than zero.

I did try some tests and both pretty much ran at the same speed but in 2 years time when there's alot more data is it likely using IF EXISTS is going to be a performance gain or not?

Thanks

Answer 1

Do you have an index on id and date?

maybe you just want:

select top 1  1 from myTable where id=7 and rowInsertDate > '01/01/2009'

note: this would return 1 if data exists, or nothing otherwise.

another edit. This won't return a row with the value null if there is no data, but rather will not return any rows. More like null in its more figurative sense.

Answer 2

IF EXISTS should be more efficient, because it is optimised to stop as soon as it find the first row. This is how I would always do this kind of check, not using a COUNT().

For performance comparison, just ensure you are testing fairly by clearing down the data and execution plan caches (non-production db server only) before each test:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Answer 3

If you don't need 376986 rows and just want to know if something exists then IF EXISTS makes a lot more sense. Also, another helpful bit is to ask for an indexed column (primary key) instead of * because you don't care about the actual data.

Answer 4

This is the fastest i could get in my projects:

SELECT CASE WHEN EXISTS (
  select top 1 1 
  from myTable 
  where id=7 
  and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
) THEN 1 ELSE 0 END AS AnyData

Answer 5

First off, you should try to dummy up a database containing as much data as you think you (or your successors) might have to deal with in two years. Then your tests will be a lot more productive.

IF EXISTS() will be faster, since the database engine only has to find a first matching record to your criteria. It will of course be faster still with proper indexes.

Another hint, don't use *, since you don't actually need to retrieve columns.

IF EXISTS(select 1 from myTable where id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE())

...should (from what I've read) work a bit faster.

Answer 6

I would just write it this way :

IF EXISTS(
      SELECT 0 FROM myTable 
      WHERE id=7 and rowInsertDate BETWEEN '01/01/2009' AND GETDATE()
)
SELECT 1
ELSE
SELECT 0

That way you don't return any data just check for conditions. I find this query structure super fast.

Answer 7

The final results will actually be a far more complex query, taking one to many parameters and the string built up and executed using sp_executesql

I think you, at least, need the full FROM, JOIN and WHERE syntax, otherwise your actual query may find nothiong (e.g. by adding an INNER JOIN that was not in the original IF EXISTS query and turns out to not be satisfied).

If you are going to that trouble you might want to get the PKs into some sort of "Batch ID Holding Table" so that you can just reference the PKs for the second "Presentation" part of your query.

What are you planning to do if you get 376,986 results? If you are going to show them to the user on screen, with some sort of paging, then having the results in a "Batch ID Holding Table" might assist with that (although, obviously, any additions / deletions etc. to the udnerlying data will muck up the paged display).

Alternatively, if you are going to be using paging just use TOP / LIMIT / SET ROWCOUNT to restrict the results to the first page full (make sure you have an ORDER BY so the sequence is repeatable), and then sort out what to do for Page 2 when the user presses the NEXT-PAGE button (we tackle that by the NEXT-PAGE button containing the PK of the last record displayed, in sort-order, so that the Next Page can resume from that point onwards).

The Query Optimiser will do different things depending on what the SELECT list is - so asking "IF EXISTS" followed by "SELECT Col1, COl2, ... FROM ..." may in effect mean that you run the complete query twice, differently, using different cached data and query plans, so overall that may be more of a strain on your server, and cause the users to wait longer, than just geting the first page / 100 rows etc.

SQL Server will cache the query plan for sp_ExecuteSQL, but make sure you parameterise the query so that the cached plan is resued where possible

Answer 8

I think Alex Bagnolini's answer is correct. The system wouldn't let me comment on his answer (new acct). The only modification I'd make is to change the second 1 to id.

Sometimes reducing the list in the project section (that's the column list) allows the db engine to hit the index only, and not the table, thus faster. This depends on your DB engine, and index structure/size, of course. (all rowInsertDate dates should be < getDate(), so you can skip that comparison)

SELECT CASE WHEN EXISTS ( select top 1 id from myTable where id=7 and rowInsertDate > '01/01/2009' ) THEN 1 ELSE 0 END AS AnyData