CODEWITHSUNDEEP
sqlsql-serverquery-optimization
Ricky Eng
Ricky Eng

Reputation: 11

SQL Server Fast Way to Determine IF Exists

I need to find a fast way to determine if records exist in a database table. The normal method of IF Exists (condition) is not fast enough for my needs. I've found something that is faster but does not work quite as intended.

The normal IF Exists (condition) which works but is too slow for my needs:

IF EXISTS (SELECT * 
           From dbo.SecurityPriceHistory
           Where  FortLabel = 'EP'
           and    TradeTime >= '2020-03-20 15:03:53.000'
           and    Price >= 2345.26)

My work around that doesn't work, but is extremely fast:

IF EXISTS (SELECT IIF(COUNT(*) = 0, null, 1)   
           From   dbo.SecurityPriceHistory
           Where  FortLabel = 'EP'
           and    TradeTime >= '2020-03-20 15:03:53.000'
           and    Price >= 2345.26)

The issue with the second solution is that when the count(*) = 0, null is returned, but that causes IF EXISTS(null) to return true.

The second solution is fast because it doesn't read any data in the execution plan, while the first one does read data.

Upvotes: 0

Views: 1415

Answers (4)

Simon
Simon

Reputation: 43

Try this:

IF (select count(*) From dbo.SecurityPriceHistory
Where FortLabel = 'EP' and TradeTime >= '2020-03-20 15:03:53.000' and Price >= 2345.26) > 0 begin ---your update statement end

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

For this query:

IF EXISTS (SELECT * 
           From dbo.SecurityPriceHistory
           Where  FortLabel = 'EP' and
                  TradeTime >= '2020-03-20 15:03:53.000' and
                  Price >= 2345.26
          )

You either want an index on:

  • SecurityPriceHistory(Fortlabel, TradeTime, Price)
  • SecurityPriceHistory(Fortlabel, Price, TradeTime)

The difference is whether TradeTime or Price is more selective. A single column index is probably not sufficient for this query.

The third column in the index is just there so the index covers the query and doesn't have to reference the data pages.

Upvotes: 0

Ricky Eng
Ricky Eng

Reputation: 11

Switching to a column store index in my particular use case appears to solve my performance problem.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

I suggested leaving the original code unchanged, but adding an index to cover one (or more) of the columns in the WHERE clause.

If I changed anything, I might limit the SELECT clause to a single non-null small column.

Upvotes: 1

Related Questions