• 13
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

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?

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.

  • 7
Reply Report
      • 1
    • You're right, Number is VarChar(25) and when I change the variable to be a VarChar(25) it uses an index seek. Thanks!

Trending Tags