SQL Server Fast Way to Determine IF Exists

Question

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.

I don't see how count(*) could be faster than the exists query -- Unless SQL Server is smart enough to know that an aggregation query with no GROUP BY or HAVING always returns one row, so EXISTS is always true. — Gordon Linoff
– Gordon Linoff, Commented Mar 23, 2020 at 19:01
This must be the case, IIF always return one row, so it can optimize the actual reading of data. — Ricky Eng
– Ricky Eng, Commented Mar 23, 2020 at 19:23
. . IIF() has nothing to do with it. It is the COUNT(*) that returns one row. — Gordon Linoff
– Gordon Linoff, Commented Mar 23, 2020 at 19:36
Under different conditions, the select returns either 1 or NULL as a resultset. Unfortunately, the NULL resultset EXISTS. — Ricky Eng
– Ricky Eng, Commented Mar 23, 2020 at 19:48

Joel Coehoorn · Accepted Answer · 2020-03-23 19:19:01Z

1

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.

answered Mar 23, 2020 at 19:19

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ricky Eng Over a year ago

There is an index on the columns in the where clause. The execution plan shows that an index seek is being used. Selecting a single column does not change the performance.

Ricky Eng · Accepted Answer · 2020-03-23 20:45:22Z

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

Gordon Linoff · Accepted Answer · 2020-03-23 20:45:33Z

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.

2 revs · Accepted Answer · 2024-02-02 20:08:18Z

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

Collectives™ on Stack Overflow

SQL Server Fast Way to Determine IF Exists

4 Answers 4

1 Comment

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Related