October 12, 2010 at 5:33 am
Hi All
Could you please help me to write this query with out CASE statement?
Also wanted to know writing query with CASE will improve the performance?
part of the query:
CASE WHEN CARE_BOOKSERIES.Id IS NULL THEN 'j' ELSE 'b'
END
= 'j'
AND CASE WHEN SUBSTRING(REVROLE_ROLEREVU_REVIEWERROLEID.NAME,1,11) = 'LE REVIEWER' THEN 'true' ELSE 'false' END = 'true'
October 12, 2010 at 5:47 am
CASE expressions are non-SARGable: when possibile, avoid using them. Use SARGable expressions on properly indexed tables instead.
What do you mean with "Improve the performance"? Improve compared to what?
-- Gianluca Sartori
October 12, 2010 at 5:51 am
WHERE CARE_BOOKSERIES.Id IS NULL AND REVROLE_ROLEREVU_REVIEWERROLEID.NAME LIKE 'LE REVIEWER%'
October 12, 2010 at 6:08 am
hallidayd (10/12/2010)
WHERE CARE_BOOKSERIES.Id IS NULL AND REVROLE_ROLEREVU_REVIEWERROLEID.NAME LIKE 'LE REVIEWER%'
For instance, these are one SARGable expression ( REVROLE_ROLEREVU_REVIEWERROLEID.NAME LIKE 'LE REVIEWER%') and a non-SARGable expression (CARE_BOOKSERIES.Id IS NULL ). If there's an index to support the first predicate, it will run much faster without the CASE expression.
-- Gianluca Sartori
October 12, 2010 at 6:34 am
Gianluca Sartori (10/12/2010)
hallidayd (10/12/2010)
WHERE CARE_BOOKSERIES.Id IS NULL AND REVROLE_ROLEREVU_REVIEWERROLEID.NAME LIKE 'LE REVIEWER%'For instance, these are one SARGable expression ( REVROLE_ROLEREVU_REVIEWERROLEID.NAME LIKE 'LE REVIEWER%') and a non-SARGable expression (CARE_BOOKSERIES.Id IS NULL ).
???
USE test;
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.numbers2'))
BEGIN
DROP TABLE dbo.numbers2
END
CREATE TABLE dbo.numbers2
(
number INT NULL
, CONSTRAINT ix_numbers2 UNIQUE CLUSTERED (number) WITH (FILLFACTOR = 100)
) ON [PRIMARY]
GO
INSERT INTO dbo.numbers2
(
number
)
SELECT number = NULLIF(number, 0)
FROM dbo.numbers
SET STATISTICS PROFILE ON
SELECT number
FROM dbo.numbers2
WHERE number IS NULL
SET STATISTICS PROFILE OFFRows,Executes,StmtText,StmtId,NodeId,Parent,PhysicalOp,LogicalOp,Argument,DefinedValues,EstimateRows,EstimateIO,EstimateCPU,AvgRowSize,TotalSubtreeCost,OutputList,Warnings,Type,Parallel,EstimateExecutions
1,1,SELECT number
FROM dbo.numbers2
WHERE number IS NULL,1,1,0,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,0.0032831,NULL,NULL,SELECT,0,NULL
1,1, |--Clustered Index Seek(OBJECT: ([test].[dbo].[numbers2].[ix_numbers2]), SEEK: ([test].[dbo].[numbers2].[number]=NULL) ORDERED FORWARD),1,2,1,Clustered Index Seek,Clustered Index Seek,OBJECT: ([test].[dbo].[numbers2].[ix_numbers2]), SEEK: ([test].[dbo].[numbers2].[number]=NULL) ORDERED FORWARD,[test].[dbo].[numbers2].[number],1,0.003125,0.0001581,11,0.0032831,[test].[dbo].[numbers2].[number],NULL,PLAN_ROW,0,1
October 12, 2010 at 6:51 am
I see the point you're trying to make, but "column IS NULL" is not a SARGable predicate.
The index CAN be used anyway, but this doesn't make the predicate SARGable.
Here's a reference for SARGable predicates:
SQL Server Transact-SQL WHERE - Brad McGehee
-- Gianluca Sartori
October 12, 2010 at 7:16 am
Gianluca Sartori (10/12/2010)
I see the point you're trying to make, but "column IS NULL" is not a SARGable predicate.The index CAN be used anyway, but this doesn't make the predicate SARGable.
Could you define sargable for me please? A seek on an index has always been my definition of sargable.
From the article:
If a WHERE clause is sargable, this means that it can take advantage of an index (assuming one is available) to speed completion of the query. If a WHERE clause is non-sargable, this means that the WHERE clause (or at least part of it) cannot take advantage of an index, instead performing a table/index scan, which may cause the query's performance to suffer.
Although I differ with his definition of taking advantage of an index (an index scan is still taking advantage of one), this definition is essentially what I work to. This is contradictory to saying that "IS NULL" is non-sargable since, according to Brad's own definition, it clearly is.
October 12, 2010 at 7:41 am
Well, I don't know what to say. I agree with you that the index gets used, that would make me think that IS NULL is SARGable.
It's not my definition, you can look it up in any manual or article on the subject: IS NULL is not a SARGable predicate.
Always from Brad's article:
Non-sargable search arguments in the WHERE clause, such as "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" generally prevents (but not always) the query optimizer from using an index to perform a search.
I agree with you that there's some oddness going on here.
For instance, see this:
IF OBJECT_ID('Tempdb..#sargTest') IS NOT NULL DROP TABLE #sargTest
CREATE TABLE #sargTest (
number INT NOT NULL PRIMARY KEY CLUSTERED,
indexedColumn INT NULL
)
CREATE NONCLUSTERED INDEX IX_SARGTest ON #sargTest (indexedColumn)
INSERT INTO #sargTest
SELECT DISTINCT number, NULLIF(number % 5,0)
FROM master.dbo.spt_values
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
SELECT number
FROM #sargTest
WHERE indexedColumn IS NULL
SELECT number
FROM #sargTest
WHERE indexedColumn IS NOT NULL
SET STATISTICS IO ON
SET STATISTICS PROFILE OFF
You will see that both IS NULL and IS NOT NULL are implemented with an index seek.
I can be in doubt about IS NULL, but IS NOT NULL is for sure non-SARGable.
Moreover, if you look at the messages (SET STATISTICS IO ON), you will see a "scan cont=1".
-- Gianluca Sartori
October 12, 2010 at 8:03 am
It might be in the list (and is often quoted as unsargable), but sargability surely must be defined by behaviour; hell, the nonsense name is derived from a description of the behaviour. The list is a convenient crib sheet but every item in it surely should be derived from observation of the behaviour Brad initially defines?
Likewise, for all the others we can not only empirically observe the behaviour we can clearly explain why inequality or searches that bypass the start of strings cannot be satisfied by indexes.
For me, a list of unsargable terms that includes predicates that do result in index seeks is pretty useless - it is after all whether or not a predicate will seek and not some arbitrary label that matters to a DBA. If IS NULL seeks then that is all I care about! 🙂
Thanks for exploring the topic with me. I would certainly be interested if you or anyone else figure out why IS NULL is listed despite not satisfying the definition.
October 12, 2010 at 8:41 am
This might explain the difference between what you're saying and what you're seeing.
Brad
...not every WHERE clause that has a non-sargable expression in it is doomed to a table/index scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use an index (if one exists) to help access the data quickly...
Just remember that it isn't always possible to use sargable expressions in a WHERE clause. In those cases, you do what you can to improve performance without sacrificing your requirements on the altar of sargability.
October 12, 2010 at 8:45 am
Thanks for chiming in, Brandie.
What I find surprising, is the predicate "IS NOT NULL" using an index seek.
WTF???!??!
-- Gianluca Sartori
October 12, 2010 at 8:45 am
Gianluca Sartori (10/12/2010)
You will see that both IS NULL and IS NOT NULL are implemented with an index seek. I can be in doubt about IS NULL, but IS NOT NULL is for sure non-SARGable.
IS NULL and IS NOT NULL are indeed both SARGable. Before seeing this thread, I'd never considered that anyone might think otherwise. Perhaps things used to be different in some older version?
Don't be mislead into thinking that the 'scan count' reported by STATISTICS IO relates to an index or table scan - it doesn't.
October 12, 2010 at 8:48 am
I would think that for the evaluation of IS NULL, it would depend greatly on the distribution of data... if you have a million plus rows of data with 90% of that column being null, I don't think that any index on that column would help out much. The best you could hope for there would be an index scan, but I would have to classify that as non-sargable.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 8:54 am
Before seeing this thread, I'd never considered that anyone might think otherwise.
Brad McGhee did in 2007, and he's no thicky pants.
I wonder if in previous versions the engine used the NULL bitmap? Can't see why it would, but you see it repeated a lot that IS NULL is not sargable.
October 12, 2010 at 8:56 am
WayneS (10/12/2010)
I would think that for the evaluation of IS NULL, it would depend greatly on the distribution of data... if you have a million plus rows of data with 90% of that column being null, I don't think that any index on that column would help out much. The best you could hope for there would be an index scan, but I would have to classify that as non-sargable.
But that's no different to any other "value". Besides, whether or not the engine seeks is (more or less) based on the results of these two options:
Index B-Tree depth + number of leaf pages satisfing the query (i.e. range scan, which is a type of seek)
Vs
Number of leaf level pages (i.e. index scan)
the lower number wins.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply