Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

NC index seek vs NC index scan with implicit conversion Expand / Collapse
Author
Message
Posted Tuesday, November 12, 2013 7:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:45 AM
Points: 58, Visits: 238
Hi all,

I've been looking thru our production plan cache and have found a number of implicit conversions. Most are of similar "type", such as NVARCHAR(4) --> VARCHAR(15). I built a small test script to see if there is any significant impact of the conversion: I discovered when there is no conversion, I get a NC index seek; when there is conversion, I get a NC index scan. But the cost, estimated vs. actual rows, etc. is the same (perhaps my sample size is too smal, or not "diverse" enough?)

Any info is appreciated.

SCRIPT:

IF OBJECT_ID(N'JSHTest', N'U') IS NOT NULL
DROP TABLE JSHTest;
GO

CREATE TABLE JSHTest
(
Col01 INT NOT NULL
,Col02 VARCHAR(15)
);

INSERT INTO JSHTest
VALUES
( 1, 'ABC' ),
( 2, 'DEF' ),
( 3, 'GHI' ),
( 4, 'JKL' ),
( 5, 'MNO' ),
( 6, 'PQR' ),
( 7, 'STU' ),
( 8, 'GHI' ),
( 9, 'YZ' ),
( 10, 'GHI' );

CREATE NONCLUSTERED INDEX JSHTest_NCIX01 ON JSHTest
(Col02);
ALTER TABLE JSHTest
ADD CONSTRAINT JSHTest_PK PRIMARY KEY (Col01);

UPDATE STATISTICS JSHTest
WITH FULLSCAN;

DECLARE @VarNVC NVARCHAR(4) = N'GHI';
DECLARE @VarVC VARCHAR(15) = 'GHI';

SELECT
*
FROM
JSHTest
WHERE
Col02 = @VarNVC;

SELECT
*
FROM
JSHTest
WHERE
Col02 = @VarVC;

Post #1513451
Posted Tuesday, November 12, 2013 8:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 42,493, Visits: 35,561
Your sample set is way too small. Everything's fast on 10 rows.

Estimated and actual rows will be the same for the seek and scan, because it's the number of rows returned by the operator, not the number of rows it read.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1513485
Posted Tuesday, November 12, 2013 8:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:45 AM
Points: 58, Visits: 238
Thanks, I'll cook up a test on a much larger scale. I was hoping the "quick" test would show not only the operator difference but some indicator why the difference.

Thanks Gail.
Post #1513489
Posted Tuesday, November 12, 2013 8:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 42,493, Visits: 35,561
The why is pretty simple. Any function when used on a column makes the predicate non-SARGable, or not usable as a seek predicate. Implicit conversions are same as explicit conversions, same as any other function on the column.

SELECT
*
FROM
JSHTest
WHERE
Col02 = '' + @VarNVC;

SELECT
*
FROM
JSHTest
WHERE
Col02 = UPPER(@VarNVC);

Same thing in both of those cases



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1513491
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse