June 30, 2014 at 10:06 pm
WHERE @var = 0 OR NOT EXISTS (SELECT 'x' FROM ...)
Will MS sql evaluate the NOT EXISTS if @var = 0?
June 30, 2014 at 10:36 pm
russ_hardie@hotmail.com (6/30/2014)
Will MS sql evaluate the NOT EXISTS if @var = 0?
http://en.wikipedia.org/wiki/%5B/quote%5D
Considering the link has nothing to do with the question, is this just SPAM?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2014 at 10:55 pm
No, sorry. Must have fallen off.
http://en.wikipedia.org/wiki/Short-circuit_evaluation
I have found a lot of info on the subject -- none that I enjoyed as it does not appear to be predictable.
CASE may be the best option. Thanks
July 1, 2014 at 7:40 am
russ_hardie@hotmail.com (6/30/2014)
Will MS sql evaluate the NOT EXISTS if @var = 0?
http://en.wikipedia.org/wiki/%5B/quote%5D
Very few things in SQL Server land can/will short-circuit. CASE is one of them, as you mention in a later post.
HOWEVER - you have a DEVASTATINGLY BAD WHERE CLAUSE PATTERN!!! You simply MUST refactor that to a) have NO EXISTence check if @var = 1 and b) REMOVE the @var = 0 when @var actually = 0. Without that you will get totally screwed by potentially stunningly-bad query plans and performance on both sides of that coin.
This is a variant of what you will find here, courtesy of our own Gail Shaw: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 2, 2014 at 8:06 am
Thanks so much! The company am at now uses the 'catch all' you describe for a lot of reports. With your link I may be able to optimize by using more reliable short-circuit patterns
July 2, 2014 at 8:21 am
russ_hardie@hotmail.com (7/2/2014)
Thanks so much! The company am at now uses the 'catch all' you describe for a lot of reports. With your link I may be able to optimize by using more reliable short-circuit patterns
I routinely get 4 to 5 ORDERS OF MAGNITUDE performance gains from such refactors. That has made me look like I am a rock star with quite a few clients over the years!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 2, 2014 at 4:03 pm
TheSQLGuru (7/1/2014)
Very few things in SQL Server land can/will short-circuit.
I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of the optimizer will short-circuit even more.
You can certainly increase the chances of a short-circuit, and perhaps help the optimizer, by explicitly coding the other condition:
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT 'x' FROM ...)) )
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 2, 2014 at 6:57 pm
ScottPletcher (7/2/2014)
TheSQLGuru (7/1/2014)
Very few things in SQL Server land can/will short-circuit.
I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of the optimizer will short-circuit even more.
You can certainly increase the chances of a short-circuit, and perhaps help the optimizer, by explicitly coding the other condition:
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT 'x' FROM ...)) )
1) I didn't think it necessary but I should have been more explicit: few things in TSQL short circuit.
2) Sorry, Scott, but you are absolutely incorrect about your "improved chances for short-circuiting the optimizer" attempt.
Use tempdb
set nocount on
go
IF OBJECT_ID(N'Customers', N'U') IS NOT NULL
DROP TABLE dbo.Customers;
CREATE TABLE dbo.Customers (
customer_nbr INT NOT NULL PRIMARY KEY,
first_name VARCHAR(35) NOT NULL,
last_name VARCHAR(35) NOT NULL);
INSERT dbo.Customers VALUES(1, 'Jeff', 'Hull');
INSERT dbo.Customers VALUES(2, 'George', 'Brown');
INSERT dbo.Customers VALUES(3, 'Peter', 'Green');
INSERT dbo.Customers VALUES(4, 'Dona', 'Johnson');
INSERT dbo.Customers VALUES(5, 'Kevin', 'Boles');
insert dbo.customers
select number, 'asdf', 'zzzz' --just dummy values that are not like Brown
from KGBTools.dbo.BigNumbers --PUT A NUMBERS TABLE IN YOUR BAG OF TSQL TRICKS!!
where number between 6 and 50000
CREATE TABLE dbo.OtherTable (NameField varchar(35))
INSERT dbo.OtherTable VALUES ('Brown')
--make seeks efficient
CREATE NONCLUSTERED INDEX ix_customer_last_name
ON dbo.Customers (last_name);
GO
--show actual execution, note estimated and actual number of rows
DECLARE @var int
SET @var = 0
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
--this first execution SHOULD "short circuit" according to you, but it does not. it still evaluates EVERY row into OtherTable
Actual/Estimated 50000
--You need OPTION (RECOMPILE) to get the "short circuiting", because then it KNOWS that @var = 0 and boolean logic says there is need to test the OR condition
DECLARE @var int
SET @var = 0
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
OPTION (RECOMPILE)
--but if you are going to OPTION RECOMPILE the query, save the optimizer and CPU the work and just code it correctly in the first place!!
--note also that WITHOUT OPTION (RECOMPILE) you are also exposed to some AMAZINGLY-BAD parameter sniffing and plan caching issues
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 2, 2014 at 8:27 pm
TheSQLGuru (7/2/2014)
ScottPletcher (7/2/2014)
TheSQLGuru (7/1/2014)
Very few things in SQL Server land can/will short-circuit.
I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of the optimizer will short-circuit even more.
You can certainly increase the chances of a short-circuit, and perhaps help the optimizer, by explicitly coding the other condition:
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT 'x' FROM ...)) )
1) I didn't think it necessary but I should have been more explicit: few things in TSQL short circuit.
2) Sorry, Scott, but you are absolutely incorrect about your "improved chances for short-circuiting the optimizer" attempt.
Use tempdb
set nocount on
go
IF OBJECT_ID(N'Customers', N'U') IS NOT NULL
DROP TABLE dbo.Customers;
CREATE TABLE dbo.Customers (
customer_nbr INT NOT NULL PRIMARY KEY,
first_name VARCHAR(35) NOT NULL,
last_name VARCHAR(35) NOT NULL);
INSERT dbo.Customers VALUES(1, 'Jeff', 'Hull');
INSERT dbo.Customers VALUES(2, 'George', 'Brown');
INSERT dbo.Customers VALUES(3, 'Peter', 'Green');
INSERT dbo.Customers VALUES(4, 'Dona', 'Johnson');
INSERT dbo.Customers VALUES(5, 'Kevin', 'Boles');
insert dbo.customers
select number, 'asdf', 'zzzz' --just dummy values that are not like Brown
from KGBTools.dbo.BigNumbers --PUT A NUMBERS TABLE IN YOUR BAG OF TSQL TRICKS!!
where number between 6 and 50000
CREATE TABLE dbo.OtherTable (NameField varchar(35))
INSERT dbo.OtherTable VALUES ('Brown')
--make seeks efficient
CREATE NONCLUSTERED INDEX ix_customer_last_name
ON dbo.Customers (last_name);
GO
--show actual execution, note estimated and actual number of rows
DECLARE @var int
SET @var = 0
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
--this first execution SHOULD "short circuit" according to you, but it does not. it still evaluates EVERY row into OtherTable
Actual/Estimated 50000
--You need OPTION (RECOMPILE) to get the "short circuiting", because then it KNOWS that @var = 0 and boolean logic says there is need to test the OR condition
DECLARE @var int
SET @var = 0
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
OPTION (RECOMPILE)
--but if you are going to OPTION RECOMPILE the query, save the optimizer and CPU the work and just code it correctly in the first place!!
--note also that WITHOUT OPTION (RECOMPILE) you are also exposed to some AMAZINGLY-BAD parameter sniffing and plan caching issues
Of course I never said it "should" short-circuit. I said I believed the chances were increased, and perhaps would help the optimizer.
While it's true that SQL doesn't short-circuit, or fully re-write code nearly as often, as, say, the DB2/UDB optimizer, it will short-circuit at times, presumably when it "believes" it safely can. Certainly we should never rely on it not short-circuiting an OR condition.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 6, 2015 at 11:22 am
Something that "might" help the optimizer generate a better execution plan is really no help at all. You need something that will deliver predictable performance. A query that results in a good execution plan for some data sets (but not others) is not a good query. A query that results in a good execution plan until that plan is recompiled (and then results in a bad execution plan) is also not a good query.
The main take-away from this issue is that the optimizer can transform your query into any "logically equivalent" execution plan. By "logically equivalent", I mean equivalent within the mathematical model of relational algebra. There is no such thing as a short-circuit in relational algebra. The entire query is evaluated as a unit.
As a general rule, when working with T-SQL, you are best off working within the relational model. Of all the relational database engines (e.g. Oracle, DB2, etc.), SQL Server holds most closely to the relational model. The theorists will quickly tell you that it does not hold to it perfectly, but it is clearly a large design concern within the SQL engine team. So, when you work with the relational model, you are working with the SQL Server system. When you step outside the relational model by trying to impose concepts foreign to it, like short-circuiting, then you are working against the system. Occasionally, by dint of great effort and ingenuity, you can make the system do something it wasn't designed to do, but not reliably and not economically.
This is really a nasty problem. It is particularly nasty because it is a common problem. I run into it all the time. I don't know of any "general" solution to the problem other than dynamic SQL. By "general" solution, I mean one that works for all (or almost all) instances of the problem.
If someone else has a good, general solution for this problem other than dynamic SQL, I would love to hear about it. 🙂
September 6, 2015 at 2:09 pm
For this simple example, I believe this form may produce a good result:
(only tested on SQL 2016 CTP 2.3 using Kevin's test data from a few posts back)
SELECT first_name, last_name
FROM (select 1 where @var=0) a(a)
join dbo.Customers c on a.a=1
union all
SELECT first_name, last_name
FROM (select 1 where @var>0) a(a)
join dbo.Customers c on a.a=1
WHERE NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')
In the case where @var = 0, it performs a CI scan of Customers as expected (as does the original query and the OPTIMIZEd version).
However, when @var = 1, it performs a single read of OtherTable, which is enough for it not to read Customers at all.
Of course, if your catch-all query is more complicated, you might not be able to achieve this kind of split.
Complete test script:
Use tempdb
set nocount on
go
IF OBJECT_ID(N'Customers', N'U') IS NOT NULL
DROP TABLE dbo.Customers;
CREATE TABLE dbo.Customers (
customer_nbr INT NOT NULL PRIMARY KEY,
first_name VARCHAR(35) NOT NULL,
last_name VARCHAR(35) NOT NULL);
INSERT dbo.Customers VALUES(1, 'Jeff', 'Hull');
INSERT dbo.Customers VALUES(2, 'George', 'Brown');
INSERT dbo.Customers VALUES(3, 'Peter', 'Green');
INSERT dbo.Customers VALUES(4, 'Dona', 'Johnson');
INSERT dbo.Customers VALUES(5, 'Kevin', 'Boles');
insert dbo.customers
select N, 'asdf', 'zzzz' --just dummy values that are not like Brown
from dbo.Tally --PUT A NUMBERS TABLE IN YOUR BAG OF TSQL TRICKS!!
where N between 6 and 50000
IF OBJECT_ID(N'OtherTable', N'U') IS NOT NULL
DROP TABLE dbo.OtherTable;
CREATE TABLE dbo.OtherTable (NameField varchar(35))
INSERT dbo.OtherTable VALUES ('Brown')
--make seeks efficient
CREATE NONCLUSTERED INDEX ix_customer_last_name
ON dbo.Customers (last_name);
GO
--show actual execution, note estimated and actual number of rows
DECLARE @var int
SET @var = 0
print '@var = 0'
print '========'
print ''
print 'OP query'
print '--------'
set statistics io,time on;
SELECT first_name, last_name
FROM dbo.Customers
WHERE @var = 0 OR NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')
set statistics io,time off;
print ''
print 'Scott''s suggestion'
print '-------------------'
set statistics io,time on;
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
set statistics io,time off;
print ''
print 'Kevin''s suggestion'
print '-------------------'
set statistics io,time on;
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
OPTION (RECOMPILE)
set statistics io,time off;
print ''
print 'Magoo''s suggestion'
print '-------------------'
set statistics io,time on;
SELECT first_name, last_name
FROM (select 1 where @var=0) a(a)
join dbo.Customers c on a.a=1
union all
SELECT first_name, last_name
FROM (select 1 where @var>0) a(a)
join dbo.Customers c on a.a=1
WHERE NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')
set statistics io,time off;
SET @var = 1
print ''
print '@var = 1'
print '========'
print ''
print 'OP query'
print '--------'
set statistics io,time on;
SELECT first_name, last_name
FROM dbo.Customers
WHERE @var = 0 OR NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')
set statistics io,time off;
print ''
print 'Scott''s suggestion'
print '-------------------'
set statistics io,time on;
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
set statistics io,time off;
print ''
print 'Kevin''s suggestion'
print '-------------------'
set statistics io,time on;
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
OPTION (RECOMPILE)
set statistics io,time off;
print ''
print 'Magoo''s suggestion'
print '-------------------'
set statistics io,time on;
SELECT first_name, last_name
FROM (select 1 where @var=0) a(a)
join dbo.Customers c on a.a=1
union all
SELECT first_name, last_name
FROM (select 1 where @var>0) a(a)
join dbo.Customers c on a.a=1
WHERE NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')
set statistics io,time off;
And the results:
@var = 0
========
OP query
--------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 158 ms.
Scott's suggestion
-------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 120 ms.
Kevin's suggestion
-------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Customers'. Scan count 1, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 153 ms.
Magoo's suggestion
-------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 102 ms.
@var = 1
========
OP query
--------
Table 'OtherTable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Scott's suggestion
-------------------
Table 'OtherTable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Kevin's suggestion
-------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'OtherTable'. Scan count 1, logical reads 10999, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 24 ms.
Magoo's suggestion
-------------------
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OtherTable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);September 6, 2015 at 3:13 pm
mister.magoo (9/6/2015)
Complete test script:
To test this kind of thing, you need to have multiple batches, not a single batch with lots of statements. The reason these kinds of queries perform badly is cached plan reuse, and your test generates a single execution plan with multiple queries, a plan which is used once, hence is not a valid performance test for this kind of query.
Put each type of query into a procedure, then replace the queries in your test script with calls to the stored procedure. Each procedure gets its own plan, and so you get plan reuse which is more realistic for testing these kinds of queries.
If you do so, then what you get is:
CREATE PROCEDURE OPQuery (@var int)
AS
SELECT first_name, last_name
FROM dbo.Customers
WHERE @var = 0 OR NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')
GO
CREATE PROCEDURE ScottQuery (@var int)
AS
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
GO
CREATE PROCEDURE KevinQuery (@var int)
AS
SELECT first_name, last_name
FROM dbo.Customers
WHERE ( (@var = 0) OR (@var > 0 AND NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')) )
OPTION (RECOMPILE)
GO
CREATE PROCEDURE MagooQuery (@var int)
AS
SELECT first_name, last_name
FROM (select 1 where @var=0) a(a)
join dbo.Customers c on a.a=1
union all
SELECT first_name, last_name
FROM (select 1 where @var>0) a(a)
join dbo.Customers c on a.a=1
WHERE NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')
GO
Results (because the test script is the one posted above, with the changes I said are needed made, and hence does not need to be reposted)
One other chance I made, first @var = 1, then @var = 0. This is because the problems with this form of query occur when a plan compiled for few rows (@var = 1) is run with many rows.
@var = 1
========
OP query
--------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'OtherTable'. Scan count 8, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 9, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Scott's suggestion
-------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'OtherTable'. Scan count 8, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 9, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Kevin's suggestion
-------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'OtherTable'. Scan count 8, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 9, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
Magoo's suggestion
-------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OtherTable'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
@var = 0
========
OP query
--------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 9, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 491 ms.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 491 ms.
Scott's suggestion
-------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 9, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 324 ms.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 324 ms.
Kevin's suggestion
-------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 9, logical reads 505, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 344 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 346 ms.
Magoo's suggestion
-------------------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OtherTable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 1, logical reads 100095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 489 ms.
Tested on SQL 2008 R2, because my 2014 instance isn't handy.
If the execution plans are examined, every query other than Magoo's shows a clustered index scan, which performs decently with either variable value. Magoo's, when the plan is compiled with @var = 1, shows an index scan and key lookup, which is fine when @var = 1, with estimated rows = 1, actual rows = 0, but is devastatingly bad when @var = 0, with the key lookup getting estimated rows = 1, actual rows = 50000.
The reason we don't see the optimiser short circuiting is because of plan caching. The plans generated are cached and reused, and the optimiser MUST ensure that the plan generated will be safe for reuse (return correct values) no matter what parameter values are passed to the plan. Hence it cannot generate a plan with part of the query 'short circuited' out, because if it did, the plan would no longer be safe for reuse, running the query with the other parameter value would return incorrect results.
The 'safe for reuse' rule is relaxed when the recompile query hint is added, because then the plan doesn't have to be safe for reuse because it's not cached and not reused. Hence Kevin's suggestion. Not all forms of potential short circuit are recognised yet, the optimiser's still not perfect.
There's a simple solution to these kinds of queries, and Kevin said it. Just Option(Recompile), then you get a plan that's optimal for each execution.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2015 at 12:38 pm
Hi Gail,
Thanks for explaining all that, I'll have to have a few reads to make sure I understand it fully.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);September 7, 2015 at 1:01 pm
mister.magoo (9/6/2015)
For this simple example, I believe this form may produce a good result:(only tested on SQL 2016 CTP 2.3 using Kevin's test data from a few posts back)
SELECT first_name, last_nameFROM (select 1 where @var=0) a(a)
join dbo.Customers c on a.a=1
union all
SELECT first_name, last_name
FROM (select 1 where @var>0) a(a)
join dbo.Customers c on a.a=1
WHERE NOT EXISTS (SELECT * FROM OtherTable WHERE NameField = 'Brown')
If I might throw in another two cent opinion - this bit of code is so complex that ordinary mortals like me can't understand it without serious effort. In my opinion, most developers dramatically under-emphasize readability of their code. Yet code readability (again, in my opinion) is more important than good comments. I'd rate it equally important as good documentation (another dramatically under-emphasized discipline). In fact, readable code is your level-1 documentation.
For that reason alone I wouldn't accept this as a general solution for this type of problem. (Plus, as Gail pointed out, it doesn't work. 🙂 )
Just for the record, I believe a good solution has the following characteristics:
1. It gets the job done
2. It performs well and it scales well for a wide range of possible inputs
3. It is readable: its intent is clear and - to the extent practical - it is self-documenting.
In my personal experience, a good solution has one more characteristic: it is usually longer. Sometimes it is much longer. So much so, that I almost consider compact T-SQL code a "Code Smell". Some developers write code like typing hurts. Seriously, when did typing become hard work? Try bussing tables for a living. 🙂
September 7, 2015 at 1:55 pm
David Moutray (9/7/2015)
Just for the record, I believe a good solution has the following characteristics:1. It gets the job done
2. It performs well and it scales well for a wide range of possible inputs
3. It is readable: its intent is clear and - to the extent practical - it is self-documenting.
Agreed. Queries should be written in the simplest form possible, and only made more complex if the simple form has been proven not to scale/perform and the complex form has been proven to scale/perform much better
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply