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

Whydoes 'N before the WHERE value causes an Index SCAN Expand / Collapse
Author
Message
Posted Thursday, November 29, 2012 3:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:27 PM
Points: 77, Visits: 306
Hello, I am trying to work out why an N' before the value in the WHERE clause would make a difference in the index plan used and in this case a large performance difference.
I am getting different stats when the [N'] is before the value. The values being sent are numeric not unicode.

The queries from a Java app were flagged as being slow. When run in SSMS they were OK. I noticed the difference was the profiler had an N' before the WHERE clause value and when I copied the query to SSMS I had omitted this.

Table Size:570691 rows
Non Clustered Index on Card_Number
Card_Number Data Type: VARCHAR(19) [although only holds numeric values of BIGINT length]

I run this query from the profiler.

SELECT
card0_.CARD_ID AS CARD1_1058_
,card0_.CARD_NUMBER AS CARD2_1058_
.
[other fields]
.
FROM dbo.CARD card0_
WHERE card_number = N'123456789'

The stats are:
Table 'CARD'. Scan count 1, logical reads 1868, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 156 ms, elapsed time = 168 ms.

The query plan does a non clustered index SCAN (on card_number) and then a key lookup for the rest of the fields
I have tried to attach the query plans.

I then run this query:

SELECT
card0_.CARD_ID AS CARD1_1058_
,card0_.CARD_NUMBER AS CARD2_1058_
.
[other fields]
.
FROM dbo.CARD card0_
WHERE card_number = '123456789'

Table 'CARD'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
CPU time = 15 ms, elapsed time = 1 ms.

The query plan does a non clustered index SEEK (on card_number) and then a key lookup for the rest of the fields

So what is annoying me is trying to find out why this is happening as the only difference is the N'?

I can see the times in the profiler for duration, reads and CPU match my SSMS findings so it certainly looks like the N' Query is being sent and executed by SQL.

On my test box I converted the datatype for the card_number field from VARCHAR(19) to BIGINT and re-created the index. When running the query again with 'N the output was only 6 reads.

Can anybody explain why this is happening as it definitely looks like a straight SEEK can not be achieved from the non clustered index when the WHERE clause is prefixed with an N', thus a SCAN is forced? Why I do not know!!

From someones experience of this issue can this be prevented in any way i.e by seeing if the front end app can be stopped from sending the unicode N' with every value?

If anymore information is needed please let me know.

thanks for any help




  Post Attachments 
Index_Scan.JPG (3 views, 19.73 KB)
Index_Seek.JPG (4 views, 19.41 KB)
Post #1390897
Posted Thursday, November 29, 2012 4:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
Could be due to the implicit conversion from nvarchar to varchar you introduce by putting the N at the front of the string (N'1234567').



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1390904
Posted Thursday, November 29, 2012 4:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:27 PM
Points: 77, Visits: 306
thanks for the reply, for my own reference I am trying to understand how this would make any difference in the way the index is seeked or scanned.
Post #1390915
Posted Thursday, November 29, 2012 5:43 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
I had it backwards. SQL Server needed to implicitly convert each of the varchar values in your table to nvarchar prior to comparing it to your constant value N'123456789', thus requiring it to scan the index rather than just doing a seek.


If you right click on the index scan in your actual execution plan and click on properties, then scroll down the properties page to the bottom and look at the Predicate, you will see the implicit conversion that SQL Server needed to accomplish for the comparision.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1390929
Posted Thursday, November 29, 2012 9:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:40 PM
Points: 100, Visits: 500
thanks, I see the conversion but what I do not get at this point is if a conversion is made then why wouldn't a SEEK happen like if there was no N'?

As mentioned if I convert the table field to BIGINT and run the query with the N' clause a SEEK is performed, but of course it does not if the table field type is VARCHAR.


Assuming this is the case and a SCAN must be performed then the overhead from a lot of transactions is very high in extra reads and cpu time.



Post #1391007
Posted Thursday, November 29, 2012 11:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
UncleBoris (11/29/2012)
thanks, I see the conversion but what I do not get at this point is if a conversion is made then why wouldn't a SEEK happen like if there was no N'?

As mentioned if I convert the table field to BIGINT and run the query with the N' clause a SEEK is performed, but of course it does not if the table field type is VARCHAR.


Assuming this is the case and a SCAN must be performed then the overhead from a lot of transactions is very high in extra reads and cpu time.





Converting the VARCHAR column to BIGINT changes where the implicit conversion occurs. Instead of converting the value of the column in each row, the constant value is implicitly converted. Again, you can see this by looking at the properties of the seek operator in your actual execution plan.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1391045
Posted Friday, November 30, 2012 7:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:58 PM
Points: 20,705, Visits: 32,356
The following link should help you understand data type precedence better:

http://msdn.microsoft.com/en-us/library/ms190309(v=sql.100).aspx



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1391300
Posted Tuesday, December 4, 2012 12:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:27 PM
Points: 77, Visits: 306
Hello, I have read your replies and looked at the link so hopefully I have the cause of the SEEK / SCAN action clear???

When passing in a NVarchar to be compared to the NC Index of VarChar type one value has to be converted.
Based on the link you sent the Varchar is lower in order so this value must be converted. Because this value is in the index ALL values must be converted to compare, therefore a SCAN must be performed.

CONVERT_IMPLICIT(nvarchar(19),[clientname].[dbo].[CARD].[CARD_NUMBER],0)=N'123456789'

When a Varchar is compared to a Varchar no conversion is necessary therefore a SEEK in done.

If I convert the field to a BIGINT datatype a conversion is needed between a NVarchar and a BIGINT. But this time the BIGINT is higher up the list so the NVARCHAR get converted to BIGINT, therefore it only happens once so a SEEK can be performed.

Seek Keys[1]: Prefix: [clientname].[dbo].[CARD].CARD_NUMBER = Scalar Operator(CONVERT_IMPLICIT(bigint,[@1],0))

thanks
Post #1392270
Posted Tuesday, December 4, 2012 11:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
uncle_boris (12/4/2012)
Hello, I have read your replies and looked at the link so hopefully I have the cause of the SEEK / SCAN action clear???

When passing in a NVarchar to be compared to the NC Index of VarChar type one value has to be converted.
Based on the link you sent the Varchar is lower in order so this value must be converted. Because this value is in the index ALL values must be converted to compare, therefore a SCAN must be performed.

CONVERT_IMPLICIT(nvarchar(19),[clientname].[dbo].[CARD].[CARD_NUMBER],0)=N'123456789'

When a Varchar is compared to a Varchar no conversion is necessary therefore a SEEK in done.

If I convert the field to a BIGINT datatype a conversion is needed between a NVarchar and a BIGINT. But this time the BIGINT is higher up the list so the NVARCHAR get converted to BIGINT, therefore it only happens once so a SEEK can be performed.

Seek Keys[1]: Prefix: [clientname].[dbo].[CARD].CARD_NUMBER = Scalar Operator(CONVERT_IMPLICIT(bigint,[@1],0))

thanks


that is exactly right. its also why one of the most common mistakes in writing queries is data type mismatch. as you have found a mismatch between the column data type and the datatype it is being compared to will cause an implicit conversion and which side has the data type of higher importance will take precidence. its why i always like to avoid implicit conversions where ever possible and use CONVERT or CAST to make the types match by converting the constant value in the where clause

SELECT *
FROM MyTable
WHERE SomeCol = CAST('2012-12-11' AS DATETIME)




For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1392633
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse