November 29, 2012 at 3:38 pm
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
November 29, 2012 at 4:14 pm
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').
November 29, 2012 at 4:49 pm
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.
November 29, 2012 at 5:43 pm
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.
November 29, 2012 at 9:57 pm
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.
November 29, 2012 at 11:25 pm
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.
November 30, 2012 at 7:24 am
The following link should help you understand data type precedence better:
http://msdn.microsoft.com/en-us/library/ms190309(v=sql.100).aspx
December 4, 2012 at 12:55 am
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
December 4, 2012 at 11:11 am
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply