I have a class of queries that test for the existence of one of two things. It is of the form
SELECT CASE
WHEN EXISTS (SELECT 1 FROM ...)
OR EXISTS (SELECT 1 FROM ...)
THEN 1 ELSE 0 END;
The actual statement is generated in C and executed as an ad-hoc query over an ODBC connection.
It has recently come to light that the second SELECT will probably be faster than the first SELECT in most cases and that switching the order of the two EXISTS clauses caused a drastic speedup in at least one abusive test case we had just created.
The obvious thing to do is just go ahead and switch the two clauses, but I wanted to see if someone more familiar with SQL Server would care to weigh in on this. It feels like I am relying on coincidence and an "implementation detail".
(It also seems like if SQL Server were smarter, it would execute both EXISTS clauses in parallel and let whichever one one completed first short-circuit the other.)
Is there a better way to get SQL Server to consistently improve the running time of such a query?
Update
Thank you for your time and interest in my question. I was not expecting questions about the actual query plans, but I am willing to share them.
This is for a software component that supports SQL Server 2008R2 and greater. The shape of the data can be quite different depending on configuration and use. My coworker thought of making this change to the query because the (in the example) dbf_1162761$z$rv$1257927703
table will always have greater or equal to the number of rows in it than the dbf_1162761$z$dd$1257927703
table -- sometimes significantly more (orders of magnitude).
Here is the abusive case I mentioned. The first query is the slow one and takes about 20 seconds. The second query completes in an instant.
For what it's worth, the "OPTIMIZE FOR UNKNOWN" bit was also added recently because parameter sniffing was trashing certain cases.
Original query:
SELECT CASE
WHEN EXISTS (SELECT 1 FROM zumero.dbf_1162761$z$rv$1257927703 rv INNER JOIN zumero.dbf_1162761$t$tx tx ON tx.txid=rv.txid WHERE tx.generation BETWEEN 1500 AND 2502)
OR EXISTS (SELECT 1 FROM zumero.dbf_1162761$z$dd$1257927703 dd INNER JOIN zumero.dbf_1162761$t$tx tx ON tx.txid=dd.txid WHERE tx.generation BETWEEN 1500 AND 2502)
THEN 1 ELSE 0 END
OPTION (OPTIMIZE FOR UNKNOWN)
Original plan:
|--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1007] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1007] = [PROBE VALUE]))
|--Constant Scan
|--Concatenation
|--Nested Loops(Inner Join, WHERE:([scale].[zumero].[dbf_1162761$z$rv$1257927703].[txid] as [rv].[txid]=[scale].[zumero].[dbf_1162761$t$tx].[txid] as [tx].[txid]))
| |--Clustered Index Scan(OBJECT:([scale].[zumero].[dbf_1162761$z$rv$1257927703].[PK__dbf_1162__97770A2F62EEAE79] AS [rv]), WHERE:([scale].[zumero].[dbf_1162761$z$rv$1257927703].[txid] as [rv].[txid]>(0)))
| |--Index Seek(OBJECT:([scale].[zumero].[dbf_1162761$t$tx].[gendex] AS [tx]), SEEK:([tx].[generation] >= (1500) AND [tx].[generation] <= (2502)) ORDERED FORWARD)
|--Nested Loops(Inner Join, OUTER REFERENCES:([tx].[txid]))
|--Clustered Index Scan(OBJECT:([scale].[zumero].[dbf_1162761$t$tx].[PK__dbf_1162__E3BA953EC2197789] AS [tx]), WHERE:([scale].[zumero].[dbf_1162761$t$tx].[generation] as [tx].[generation]>=(1500) AND [scale].[zumero].[dbf_1162761$t$tx].[generation] as [tx].[generation]<=(2502)) ORDERED FORWARD)
|--Index Seek(OBJECT:([scale].[zumero].[dbf_1162761$z$dd$1257927703].[n$dbf_1162761$z$dd$txid$1257927703] AS [dd]), SEEK:([dd].[txid]=[scale].[zumero].[dbf_1162761$t$tx].[txid] as [tx].[txid]), WHERE:([scale].[zumero].[dbf_1162761$z$dd$1257927703].[txid] as [dd].[txid]>(0)) ORDERED FORWARD)
Fixed query:
SELECT CASE
WHEN EXISTS (SELECT 1 FROM zumero.dbf_1162761$z$dd$1257927703 dd INNER JOIN zumero.dbf_1162761$t$tx tx ON tx.txid=dd.txid WHERE tx.generation BETWEEN 1500 AND 2502)
OR EXISTS (SELECT 1 FROM zumero.dbf_1162761$z$rv$1257927703 rv INNER JOIN zumero.dbf_1162761$t$tx tx ON tx.txid=rv.txid WHERE tx.generation BETWEEN 1500 AND 2502)
THEN 1 ELSE 0 END
OPTION (OPTIMIZE FOR UNKNOWN)
Fixed plan:
|--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1007] THEN (1) ELSE (0) END))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1007] = [PROBE VALUE]))
|--Constant Scan
|--Concatenation
|--Nested Loops(Inner Join, OUTER REFERENCES:([tx].[txid]))
| |--Clustered Index Scan(OBJECT:([scale].[zumero].[dbf_1162761$t$tx].[PK__dbf_1162__E3BA953EC2197789] AS [tx]), WHERE:([scale].[zumero].[dbf_1162761$t$tx].[generation] as [tx].[generation]>=(1500) AND [scale].[zumero].[dbf_1162761$t$tx].[generation] as [tx].[generation]<=(2502)) ORDERED FORWARD)
| |--Index Seek(OBJECT:([scale].[zumero].[dbf_1162761$z$dd$1257927703].[n$dbf_1162761$z$dd$txid$1257927703] AS [dd]), SEEK:([dd].[txid]=[scale].[zumero].[dbf_1162761$t$tx].[txid] as [tx].[txid]), WHERE:([scale].[zumero].[dbf_1162761$z$dd$1257927703].[txid] as [dd].[txid]>(0)) ORDERED FORWARD)
|--Nested Loops(Inner Join, WHERE:([scale].[zumero].[dbf_1162761$z$rv$1257927703].[txid] as [rv].[txid]=[scale].[zumero].[dbf_1162761$t$tx].[txid] as [tx].[txid]))
|--Clustered Index Scan(OBJECT:([scale].[zumero].[dbf_1162761$z$rv$1257927703].[PK__dbf_1162__97770A2F62EEAE79] AS [rv]), WHERE:([scale].[zumero].[dbf_1162761$z$rv$1257927703].[txid] as [rv].[txid]>(0)))
|--Index Seek(OBJECT:([scale].[zumero].[dbf_1162761$t$tx].[gendex] AS [tx]), SEEK:([tx].[generation] >= (1500) AND [tx].[generation] <= (2502)) ORDERED FORWARD)