Why is query with phone = N'1234' slower than phone = '1234'?
I have a field which is a varchar(20)
When this query is executed, it is fast (Uses index seek):
SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'
But this one is slow (uses index scan).
SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'
I am guessing that if I change the field to an nvarchar, then it would use the Index Seek.
sql sql-server
add a comment |
I have a field which is a varchar(20)
When this query is executed, it is fast (Uses index seek):
SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'
But this one is slow (uses index scan).
SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'
I am guessing that if I change the field to an nvarchar, then it would use the Index Seek.
sql sql-server
10
because it needs to perform an implicit data conversion
– Lamak
7 hours ago
2
CausePhone
is varchar andN'2164474477'
is nvarchar.
– Sami
7 hours ago
add a comment |
I have a field which is a varchar(20)
When this query is executed, it is fast (Uses index seek):
SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'
But this one is slow (uses index scan).
SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'
I am guessing that if I change the field to an nvarchar, then it would use the Index Seek.
sql sql-server
I have a field which is a varchar(20)
When this query is executed, it is fast (Uses index seek):
SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'
But this one is slow (uses index scan).
SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'
I am guessing that if I change the field to an nvarchar, then it would use the Index Seek.
sql sql-server
sql sql-server
edited 7 hours ago
Yogesh Sharma
30.5k51437
30.5k51437
asked 7 hours ago
Greg GumGreg Gum
10.8k1576132
10.8k1576132
10
because it needs to perform an implicit data conversion
– Lamak
7 hours ago
2
CausePhone
is varchar andN'2164474477'
is nvarchar.
– Sami
7 hours ago
add a comment |
10
because it needs to perform an implicit data conversion
– Lamak
7 hours ago
2
CausePhone
is varchar andN'2164474477'
is nvarchar.
– Sami
7 hours ago
10
10
because it needs to perform an implicit data conversion
– Lamak
7 hours ago
because it needs to perform an implicit data conversion
– Lamak
7 hours ago
2
2
Cause
Phone
is varchar and N'2164474477'
is nvarchar.– Sami
7 hours ago
Cause
Phone
is varchar and N'2164474477'
is nvarchar.– Sami
7 hours ago
add a comment |
3 Answers
3
active
oldest
votes
Because nvarchar
has higher datatype precedence than varchar
so it needs to perform an implicit cast of the column to nvarchar
and this prevents an index seek.
Under some collations it is able to still use a seek and just push the cast
into a residual predicate against the rows matched by the seek (rather than needing to do this for every row in the entire table via a scan) but presumably you aren't using such a collation.
The effect of collation on this is illustrated below. When using the SQL collation you get a scan, for the Windows collation it calls the internal function GetRangeThroughConvert
and is able to convert it into a seek.
create table [dbo].[phone]
(
phone1 varchar(500) collate sql_latin1_general_cp1_ci_as constraint uq1 unique,
phone2 varchar(500) collate latin1_general_cs_as constraint uq2 unique,
)
SELECT phone1 FROM [dbo].[phone] WHERE phone1 = N'2164474477'
SELECT phone2 FROM [dbo].[phone] WHERE phone2 = N'2164474477'
In the second case the compute scalar emits the following values
Expr1003 = 62
Expr1004 = '2164474477'
Expr1005 = '2164474478'
the seek predicate is on phone2 > Expr1004 and phone2 < Expr1005
so on the face of it would exclude '2164474477'
but the flag 62
means that this does match.
add a comment |
Other answers already explain what happens. We've seen that NVARCHAR
has higher precedence than VARCHAR
. I want to take a moment and explain why the database must cast every row in the column as an NVARCHAR
, rather than casting the single supplied value as VARCHAR
, even though the second option is cleary much faster, both intuitively and empirically.
Casting from NVARCHAR
to VARCHAR
is a narrowing conversion. That is, NVARCHAR
has potentially more information than a similar VARCHAR
value. It's not possible to represent every NVARCHAR
input with a VARCHAR
output, so casting from the former to the latter potentially loses some information. But opposite cast is a widening conversion. Casting from a VARCHAR
value to an NVARCHAR
value never loses information; it's safe.
Now you and I know that your narrowing conversion is also safe for this particular data. But the Sql Server query optimizer doesn't know that. It only sees the data type information when building the execution plan. Sql Server wants to cache it's execution plans and potentially re-use them, to save the work of building the same plans again in the future, and therefore the actual value doesn't matter as much as getting the generic conversion right.
When presented with two mismatched types for a comparison, Sql Server will always choose the safe conversion. It's same old "correctness trumps performance" mantra. Or, to paraphrase Benjamin Franklin, "He who would trade essential correctness for a little performance deserve neither correctness nor performance."
Here's the real kicker: now that we're making this cast, we have to do it for every row in the table. This is true even for rows that would not otherwise match the comparison filter. Morever, the cast values from the columns are no longer the same as the values stored in an index, such that any index on the column is now worthless for this query.
I think you're very lucky to be getting an index scan for this query, rather than a full table scan, and it's likely because there is a covering index that meets the needs of the query (the optimizer can choose to cast all the records int he index as easily as all the records in the table).
But you can fix things for this query by forcing the cast you actually want:
SELECT * FROM [dbo].[phone] WHERE phone = cast(N'2164474477' as varchar(20))
Of course, it's even better to fix it by using a VARCHAR
value in the first place:
SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'
If this query was created from a .Net program, it's probably related to the AddWithValue()
function. I've written about this issue in the past and how to handle it correctly.
add a comment |
SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'
is interpreted as
SELECT * from [dbo].[phone] WHERE CAST(phone as NVARCHAR) = N'2164474477'
which prevents index usage
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54540928%2fwhy-is-query-with-phone-n1234-slower-than-phone-1234%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Because nvarchar
has higher datatype precedence than varchar
so it needs to perform an implicit cast of the column to nvarchar
and this prevents an index seek.
Under some collations it is able to still use a seek and just push the cast
into a residual predicate against the rows matched by the seek (rather than needing to do this for every row in the entire table via a scan) but presumably you aren't using such a collation.
The effect of collation on this is illustrated below. When using the SQL collation you get a scan, for the Windows collation it calls the internal function GetRangeThroughConvert
and is able to convert it into a seek.
create table [dbo].[phone]
(
phone1 varchar(500) collate sql_latin1_general_cp1_ci_as constraint uq1 unique,
phone2 varchar(500) collate latin1_general_cs_as constraint uq2 unique,
)
SELECT phone1 FROM [dbo].[phone] WHERE phone1 = N'2164474477'
SELECT phone2 FROM [dbo].[phone] WHERE phone2 = N'2164474477'
In the second case the compute scalar emits the following values
Expr1003 = 62
Expr1004 = '2164474477'
Expr1005 = '2164474478'
the seek predicate is on phone2 > Expr1004 and phone2 < Expr1005
so on the face of it would exclude '2164474477'
but the flag 62
means that this does match.
add a comment |
Because nvarchar
has higher datatype precedence than varchar
so it needs to perform an implicit cast of the column to nvarchar
and this prevents an index seek.
Under some collations it is able to still use a seek and just push the cast
into a residual predicate against the rows matched by the seek (rather than needing to do this for every row in the entire table via a scan) but presumably you aren't using such a collation.
The effect of collation on this is illustrated below. When using the SQL collation you get a scan, for the Windows collation it calls the internal function GetRangeThroughConvert
and is able to convert it into a seek.
create table [dbo].[phone]
(
phone1 varchar(500) collate sql_latin1_general_cp1_ci_as constraint uq1 unique,
phone2 varchar(500) collate latin1_general_cs_as constraint uq2 unique,
)
SELECT phone1 FROM [dbo].[phone] WHERE phone1 = N'2164474477'
SELECT phone2 FROM [dbo].[phone] WHERE phone2 = N'2164474477'
In the second case the compute scalar emits the following values
Expr1003 = 62
Expr1004 = '2164474477'
Expr1005 = '2164474478'
the seek predicate is on phone2 > Expr1004 and phone2 < Expr1005
so on the face of it would exclude '2164474477'
but the flag 62
means that this does match.
add a comment |
Because nvarchar
has higher datatype precedence than varchar
so it needs to perform an implicit cast of the column to nvarchar
and this prevents an index seek.
Under some collations it is able to still use a seek and just push the cast
into a residual predicate against the rows matched by the seek (rather than needing to do this for every row in the entire table via a scan) but presumably you aren't using such a collation.
The effect of collation on this is illustrated below. When using the SQL collation you get a scan, for the Windows collation it calls the internal function GetRangeThroughConvert
and is able to convert it into a seek.
create table [dbo].[phone]
(
phone1 varchar(500) collate sql_latin1_general_cp1_ci_as constraint uq1 unique,
phone2 varchar(500) collate latin1_general_cs_as constraint uq2 unique,
)
SELECT phone1 FROM [dbo].[phone] WHERE phone1 = N'2164474477'
SELECT phone2 FROM [dbo].[phone] WHERE phone2 = N'2164474477'
In the second case the compute scalar emits the following values
Expr1003 = 62
Expr1004 = '2164474477'
Expr1005 = '2164474478'
the seek predicate is on phone2 > Expr1004 and phone2 < Expr1005
so on the face of it would exclude '2164474477'
but the flag 62
means that this does match.
Because nvarchar
has higher datatype precedence than varchar
so it needs to perform an implicit cast of the column to nvarchar
and this prevents an index seek.
Under some collations it is able to still use a seek and just push the cast
into a residual predicate against the rows matched by the seek (rather than needing to do this for every row in the entire table via a scan) but presumably you aren't using such a collation.
The effect of collation on this is illustrated below. When using the SQL collation you get a scan, for the Windows collation it calls the internal function GetRangeThroughConvert
and is able to convert it into a seek.
create table [dbo].[phone]
(
phone1 varchar(500) collate sql_latin1_general_cp1_ci_as constraint uq1 unique,
phone2 varchar(500) collate latin1_general_cs_as constraint uq2 unique,
)
SELECT phone1 FROM [dbo].[phone] WHERE phone1 = N'2164474477'
SELECT phone2 FROM [dbo].[phone] WHERE phone2 = N'2164474477'
In the second case the compute scalar emits the following values
Expr1003 = 62
Expr1004 = '2164474477'
Expr1005 = '2164474478'
the seek predicate is on phone2 > Expr1004 and phone2 < Expr1005
so on the face of it would exclude '2164474477'
but the flag 62
means that this does match.
edited 4 hours ago
answered 7 hours ago
Martin SmithMartin Smith
344k58578688
344k58578688
add a comment |
add a comment |
Other answers already explain what happens. We've seen that NVARCHAR
has higher precedence than VARCHAR
. I want to take a moment and explain why the database must cast every row in the column as an NVARCHAR
, rather than casting the single supplied value as VARCHAR
, even though the second option is cleary much faster, both intuitively and empirically.
Casting from NVARCHAR
to VARCHAR
is a narrowing conversion. That is, NVARCHAR
has potentially more information than a similar VARCHAR
value. It's not possible to represent every NVARCHAR
input with a VARCHAR
output, so casting from the former to the latter potentially loses some information. But opposite cast is a widening conversion. Casting from a VARCHAR
value to an NVARCHAR
value never loses information; it's safe.
Now you and I know that your narrowing conversion is also safe for this particular data. But the Sql Server query optimizer doesn't know that. It only sees the data type information when building the execution plan. Sql Server wants to cache it's execution plans and potentially re-use them, to save the work of building the same plans again in the future, and therefore the actual value doesn't matter as much as getting the generic conversion right.
When presented with two mismatched types for a comparison, Sql Server will always choose the safe conversion. It's same old "correctness trumps performance" mantra. Or, to paraphrase Benjamin Franklin, "He who would trade essential correctness for a little performance deserve neither correctness nor performance."
Here's the real kicker: now that we're making this cast, we have to do it for every row in the table. This is true even for rows that would not otherwise match the comparison filter. Morever, the cast values from the columns are no longer the same as the values stored in an index, such that any index on the column is now worthless for this query.
I think you're very lucky to be getting an index scan for this query, rather than a full table scan, and it's likely because there is a covering index that meets the needs of the query (the optimizer can choose to cast all the records int he index as easily as all the records in the table).
But you can fix things for this query by forcing the cast you actually want:
SELECT * FROM [dbo].[phone] WHERE phone = cast(N'2164474477' as varchar(20))
Of course, it's even better to fix it by using a VARCHAR
value in the first place:
SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'
If this query was created from a .Net program, it's probably related to the AddWithValue()
function. I've written about this issue in the past and how to handle it correctly.
add a comment |
Other answers already explain what happens. We've seen that NVARCHAR
has higher precedence than VARCHAR
. I want to take a moment and explain why the database must cast every row in the column as an NVARCHAR
, rather than casting the single supplied value as VARCHAR
, even though the second option is cleary much faster, both intuitively and empirically.
Casting from NVARCHAR
to VARCHAR
is a narrowing conversion. That is, NVARCHAR
has potentially more information than a similar VARCHAR
value. It's not possible to represent every NVARCHAR
input with a VARCHAR
output, so casting from the former to the latter potentially loses some information. But opposite cast is a widening conversion. Casting from a VARCHAR
value to an NVARCHAR
value never loses information; it's safe.
Now you and I know that your narrowing conversion is also safe for this particular data. But the Sql Server query optimizer doesn't know that. It only sees the data type information when building the execution plan. Sql Server wants to cache it's execution plans and potentially re-use them, to save the work of building the same plans again in the future, and therefore the actual value doesn't matter as much as getting the generic conversion right.
When presented with two mismatched types for a comparison, Sql Server will always choose the safe conversion. It's same old "correctness trumps performance" mantra. Or, to paraphrase Benjamin Franklin, "He who would trade essential correctness for a little performance deserve neither correctness nor performance."
Here's the real kicker: now that we're making this cast, we have to do it for every row in the table. This is true even for rows that would not otherwise match the comparison filter. Morever, the cast values from the columns are no longer the same as the values stored in an index, such that any index on the column is now worthless for this query.
I think you're very lucky to be getting an index scan for this query, rather than a full table scan, and it's likely because there is a covering index that meets the needs of the query (the optimizer can choose to cast all the records int he index as easily as all the records in the table).
But you can fix things for this query by forcing the cast you actually want:
SELECT * FROM [dbo].[phone] WHERE phone = cast(N'2164474477' as varchar(20))
Of course, it's even better to fix it by using a VARCHAR
value in the first place:
SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'
If this query was created from a .Net program, it's probably related to the AddWithValue()
function. I've written about this issue in the past and how to handle it correctly.
add a comment |
Other answers already explain what happens. We've seen that NVARCHAR
has higher precedence than VARCHAR
. I want to take a moment and explain why the database must cast every row in the column as an NVARCHAR
, rather than casting the single supplied value as VARCHAR
, even though the second option is cleary much faster, both intuitively and empirically.
Casting from NVARCHAR
to VARCHAR
is a narrowing conversion. That is, NVARCHAR
has potentially more information than a similar VARCHAR
value. It's not possible to represent every NVARCHAR
input with a VARCHAR
output, so casting from the former to the latter potentially loses some information. But opposite cast is a widening conversion. Casting from a VARCHAR
value to an NVARCHAR
value never loses information; it's safe.
Now you and I know that your narrowing conversion is also safe for this particular data. But the Sql Server query optimizer doesn't know that. It only sees the data type information when building the execution plan. Sql Server wants to cache it's execution plans and potentially re-use them, to save the work of building the same plans again in the future, and therefore the actual value doesn't matter as much as getting the generic conversion right.
When presented with two mismatched types for a comparison, Sql Server will always choose the safe conversion. It's same old "correctness trumps performance" mantra. Or, to paraphrase Benjamin Franklin, "He who would trade essential correctness for a little performance deserve neither correctness nor performance."
Here's the real kicker: now that we're making this cast, we have to do it for every row in the table. This is true even for rows that would not otherwise match the comparison filter. Morever, the cast values from the columns are no longer the same as the values stored in an index, such that any index on the column is now worthless for this query.
I think you're very lucky to be getting an index scan for this query, rather than a full table scan, and it's likely because there is a covering index that meets the needs of the query (the optimizer can choose to cast all the records int he index as easily as all the records in the table).
But you can fix things for this query by forcing the cast you actually want:
SELECT * FROM [dbo].[phone] WHERE phone = cast(N'2164474477' as varchar(20))
Of course, it's even better to fix it by using a VARCHAR
value in the first place:
SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'
If this query was created from a .Net program, it's probably related to the AddWithValue()
function. I've written about this issue in the past and how to handle it correctly.
Other answers already explain what happens. We've seen that NVARCHAR
has higher precedence than VARCHAR
. I want to take a moment and explain why the database must cast every row in the column as an NVARCHAR
, rather than casting the single supplied value as VARCHAR
, even though the second option is cleary much faster, both intuitively and empirically.
Casting from NVARCHAR
to VARCHAR
is a narrowing conversion. That is, NVARCHAR
has potentially more information than a similar VARCHAR
value. It's not possible to represent every NVARCHAR
input with a VARCHAR
output, so casting from the former to the latter potentially loses some information. But opposite cast is a widening conversion. Casting from a VARCHAR
value to an NVARCHAR
value never loses information; it's safe.
Now you and I know that your narrowing conversion is also safe for this particular data. But the Sql Server query optimizer doesn't know that. It only sees the data type information when building the execution plan. Sql Server wants to cache it's execution plans and potentially re-use them, to save the work of building the same plans again in the future, and therefore the actual value doesn't matter as much as getting the generic conversion right.
When presented with two mismatched types for a comparison, Sql Server will always choose the safe conversion. It's same old "correctness trumps performance" mantra. Or, to paraphrase Benjamin Franklin, "He who would trade essential correctness for a little performance deserve neither correctness nor performance."
Here's the real kicker: now that we're making this cast, we have to do it for every row in the table. This is true even for rows that would not otherwise match the comparison filter. Morever, the cast values from the columns are no longer the same as the values stored in an index, such that any index on the column is now worthless for this query.
I think you're very lucky to be getting an index scan for this query, rather than a full table scan, and it's likely because there is a covering index that meets the needs of the query (the optimizer can choose to cast all the records int he index as easily as all the records in the table).
But you can fix things for this query by forcing the cast you actually want:
SELECT * FROM [dbo].[phone] WHERE phone = cast(N'2164474477' as varchar(20))
Of course, it's even better to fix it by using a VARCHAR
value in the first place:
SELECT * FROM [dbo].[phone] WHERE phone = '2164474477'
If this query was created from a .Net program, it's probably related to the AddWithValue()
function. I've written about this issue in the past and how to handle it correctly.
edited 4 hours ago
answered 6 hours ago
Joel CoehoornJoel Coehoorn
308k95491723
308k95491723
add a comment |
add a comment |
SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'
is interpreted as
SELECT * from [dbo].[phone] WHERE CAST(phone as NVARCHAR) = N'2164474477'
which prevents index usage
add a comment |
SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'
is interpreted as
SELECT * from [dbo].[phone] WHERE CAST(phone as NVARCHAR) = N'2164474477'
which prevents index usage
add a comment |
SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'
is interpreted as
SELECT * from [dbo].[phone] WHERE CAST(phone as NVARCHAR) = N'2164474477'
which prevents index usage
SELECT * FROM [dbo].[phone] WHERE phone = N'2164474477'
is interpreted as
SELECT * from [dbo].[phone] WHERE CAST(phone as NVARCHAR) = N'2164474477'
which prevents index usage
answered 7 hours ago
SimonareSimonare
11.6k11737
11.6k11737
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54540928%2fwhy-is-query-with-phone-n1234-slower-than-phone-1234%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
10
because it needs to perform an implicit data conversion
– Lamak
7 hours ago
2
Cause
Phone
is varchar andN'2164474477'
is nvarchar.– Sami
7 hours ago