Otdfbt

Riley's, assemble!

Why don't B747s start takeoffs with full throttle?

How can Iron Man's suit withstand this?

How were concentration and extermination camp guards recruited?

Word for a small burst of laughter that can't be held back

Is it OK to bring delicacies from hometown as tokens of gratitude for an out-of-town interview?

What does it mean by "d-ism of Leibniz" and "dotage of Newton" in simple English?

What happens to foam insulation board after you pour concrete slab?

Is it legal in the UK for politicians to lie to the public for political gain?

Convert camelCase and PascalCase to Title Case

You've spoiled/damaged the card

Building a road to escape Earth's gravity by making a pyramid on Antartica

Can you please explain this joke: "I'm going bananas is what I tell my bananas before I leave the house"?

Avoiding cliches when writing gods

Pros and cons of writing a book review?

Can a magnetic field of an object be stronger than its gravity?

Bent spoke design wheels — feasible?

How to connect an offset point symbol to its original position in QGIS?

Why is the relationship between frequency and pitch exponential?

I wrote a scene that the majority of my readers loved. How do I get back to that place while writing my new book?

How certain is a caster of when their spell will end?

The ring of global sections of a regular scheme

The term for the person/group a political party aligns themselves with to appear concerned about the general public

Shrink exponential fraction argument

Why won't SQL Server use an index seek when running a parameterized query?

Setting up ASP.NET Membership provider for an applicationWhat can cause long query durations without high resource usage?SQL Server crashes when remote query failsSQL Server: Index utilization statistics?LEFT JOIN Server Performance optimizationSQL Server 2005 running at 97% CPU usageSQL Server won't start due to LUNs being unavailableSQL Server 2005 using 100% CPU, but drops to 70% when SQL Server Profiler is RunningWhy is Postgres sitting 95% idle, with no file I/O?Randomly slow MySQL queries

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

3

When I execute the following query in SQL Server 2005 it uses an index seek, as verified by viewing the execution plan.

SELECT *
FROM Account
WHERE Number = '123456789'

But when I run the same query, but use a parameter, it uses an index scan.

DECLARE @AccountNumber NVarChar(25)
SET @AccountNumber = '123456789'

SELECT *
FROM Account
WHERE Number = @AccountNumber

Since this table has over 10 million rows, the second query takes over 30 seconds while the first query takes only a few milliseconds. Do I really have to go and change all of my queries to not use parameters?

performance sql-server-2005

share|improve this question

edited May 18 at 22:30

window.document

1436

asked Jul 22 '09 at 18:10

Stefan MoserStefan Moser

1407

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Should this belong on stackoverflow?
  
  – gbn
  Jul 22 '09 at 18:20
  

  
  
  
  

add a comment | 

3

When I execute the following query in SQL Server 2005 it uses an index seek, as verified by viewing the execution plan.

SELECT *
FROM Account
WHERE Number = '123456789'

But when I run the same query, but use a parameter, it uses an index scan.

DECLARE @AccountNumber NVarChar(25)
SET @AccountNumber = '123456789'

SELECT *
FROM Account
WHERE Number = @AccountNumber

Since this table has over 10 million rows, the second query takes over 30 seconds while the first query takes only a few milliseconds. Do I really have to go and change all of my queries to not use parameters?

performance sql-server-2005

share|improve this question

edited May 18 at 22:30

window.document

1436

asked Jul 22 '09 at 18:10

Stefan MoserStefan Moser

1407

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Should this belong on stackoverflow?
  
  – gbn
  Jul 22 '09 at 18:20
  

  
  
  
  

add a comment | 

When I execute the following query in SQL Server 2005 it uses an index seek, as verified by viewing the execution plan.

SELECT *
FROM Account
WHERE Number = '123456789'

But when I run the same query, but use a parameter, it uses an index scan.

DECLARE @AccountNumber NVarChar(25)
SET @AccountNumber = '123456789'

SELECT *
FROM Account
WHERE Number = @AccountNumber

Since this table has over 10 million rows, the second query takes over 30 seconds while the first query takes only a few milliseconds. Do I really have to go and change all of my queries to not use parameters?

performance sql-server-2005

share|improve this question

edited May 18 at 22:30

window.document

1436

asked Jul 22 '09 at 18:10

Stefan MoserStefan Moser

1407

SELECT *
FROM Account
WHERE Number = '123456789'

DECLARE @AccountNumber NVarChar(25)
SET @AccountNumber = '123456789'

SELECT *
FROM Account
WHERE Number = @AccountNumber

share|improve this question

edited May 18 at 22:30

window.document

1436

asked Jul 22 '09 at 18:10

Stefan MoserStefan Moser

1407

share|improve this question

edited May 18 at 22:30

window.document

1436

asked Jul 22 '09 at 18:10

Stefan MoserStefan Moser

1407

edited May 18 at 22:30

window.document

1436

edited May 18 at 22:30

window.document

1436

asked Jul 22 '09 at 18:10

Stefan MoserStefan Moser

1407

asked Jul 22 '09 at 18:10

Stefan MoserStefan Moser

1407

1 Answer
1

active

oldest

votes

6

There are differences between using constants and variables because, simply, SQL Server tries to optimise for the general case.

However, in this instance, my first thought is that data type precedence causing implicit conversion. What data type is the Number column?

Example: say it's varchar(25). nvarchar has higher precedence then varchar so the column is implicitly converted before comparison.

I've been bitten recently by comparing varchar vs SUSER_SNAME. And I should know better.

share|improve this answer

answered Jul 22 '09 at 18:16

gbngbn

5,74111421

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You're right, Number is VarChar(25) and when I change the variable to be a VarChar(25) it uses an index seek. Thanks!
  
  – Stefan Moser
  Jul 22 '09 at 18:27
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f44181%2fwhy-wont-sql-server-use-an-index-seek-when-running-a-parameterized-query%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

6

There are differences between using constants and variables because, simply, SQL Server tries to optimise for the general case.

However, in this instance, my first thought is that data type precedence causing implicit conversion. What data type is the Number column?

Example: say it's varchar(25). nvarchar has higher precedence then varchar so the column is implicitly converted before comparison.

I've been bitten recently by comparing varchar vs SUSER_SNAME. And I should know better.

share|improve this answer

answered Jul 22 '09 at 18:16

gbngbn

5,74111421

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You're right, Number is VarChar(25) and when I change the variable to be a VarChar(25) it uses an index seek. Thanks!
  
  – Stefan Moser
  Jul 22 '09 at 18:27
  

  
  
  
  

add a comment | 

6

There are differences between using constants and variables because, simply, SQL Server tries to optimise for the general case.

However, in this instance, my first thought is that data type precedence causing implicit conversion. What data type is the Number column?

Example: say it's varchar(25). nvarchar has higher precedence then varchar so the column is implicitly converted before comparison.

I've been bitten recently by comparing varchar vs SUSER_SNAME. And I should know better.

share|improve this answer

answered Jul 22 '09 at 18:16

gbngbn

5,74111421

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You're right, Number is VarChar(25) and when I change the variable to be a VarChar(25) it uses an index seek. Thanks!
  
  – Stefan Moser
  Jul 22 '09 at 18:27
  

  
  
  
  

add a comment | 

There are differences between using constants and variables because, simply, SQL Server tries to optimise for the general case.

However, in this instance, my first thought is that data type precedence causing implicit conversion. What data type is the Number column?

Example: say it's varchar(25). nvarchar has higher precedence then varchar so the column is implicitly converted before comparison.

I've been bitten recently by comparing varchar vs SUSER_SNAME. And I should know better.

share|improve this answer

answered Jul 22 '09 at 18:16

gbngbn

5,74111421

share|improve this answer

answered Jul 22 '09 at 18:16

gbngbn

5,74111421

answered Jul 22 '09 at 18:16

gbngbn

5,74111421

answered Jul 22 '09 at 18:16

gbngbn

5,74111421