Does LIKE operator have major performance issue with variables?

Hi all,
Below are two similar SQL statements that give the same results:
1. SELECT * FROM InvoiceDtl WHERE IvoNum LIKE ('Ivo-0510-00001')
2. DECLARE @.IvoNum AS NVARCHAR (20)
SET @.IvoNum = 'Ivo-0510-00001'
SELECT * FROM InvoiceDtl WHERE IvoNum LIKE (@.IvoNum)
InvoiceDtl is a big table with 2.3++ million rows. IvoNum is of type
NVARCHAR (20) and has a non-clustered index.
I run both statements seperately in Query Analyzer. Statement 1 takes 1-2
seconds. But statement 2 takes 3-4 minutes (and makes my harddisk run mad)!
Cld anyone pls kindly advise why that is happening? TQ.SQL Server processes batches of SQL statements in 3 steps:
1) Parsing: check for invalid code
2) Compilation: generate an execution plan, which tables/indexes to use, and
the order to access them in etc
3) Execution: execute the execution plan generated in step 2
Now for the first statement SQL knows the value of IvoNum it has to look for
as early as step 2, because it is a literal. The Query optimizer can look up
statistics on the indexes and estimate how often the value 'Ivo-0510-00001'
appears in the column IvoNum, and generate the fastest execution plan to be
executed by step 3.
For the second statement, SQL Server does NOT know the value of IvoNum it
has to look as early as step 2. @.IvoNum is a variable, at the assignment of
a value to this variable only happens during execution in step 3. If T-SQL
had constants, you could declare @.IvoNum as a constant, and the value would
be available in step 2, but T-SQL only has variables not constants. So the
Query Optimizer does not know in step 2 to as to what the value of @.IvoNum
will be during execution. So it uses an estimate for the number of rows that
might match, and IIRC, that estimate is 30%. Remember that the value of
@.IvoNum is unknown during step 2, so it might be 'Ivo-0510-00001' , 'Ivo%'
'%0510-00001' or even '%' in step 3. This estimate leads to a very different
execution plan, which in cases will include scanning all 2.3 million rows in
the table.
Jacco Schalkwijk
SQL Server MVP
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:43932C3C-D0D8-42EE-AE09-7388DBA8D6CE@.microsoft.com...
> Hi all,
> Below are two similar SQL statements that give the same results:
> 1. SELECT * FROM InvoiceDtl WHERE IvoNum LIKE ('Ivo-0510-00001')
> 2. DECLARE @.IvoNum AS NVARCHAR (20)
> SET @.IvoNum = 'Ivo-0510-00001'
> SELECT * FROM InvoiceDtl WHERE IvoNum LIKE (@.IvoNum)
> InvoiceDtl is a big table with 2.3++ million rows. IvoNum is of type
> NVARCHAR (20) and has a non-clustered index.
> I run both statements seperately in Query Analyzer. Statement 1 takes 1-2
> seconds. But statement 2 takes 3-4 minutes (and makes my harddisk run
> mad)!
> Cld anyone pls kindly advise why that is happening? TQ.|||HardKhor,
I got some questions for you here...
1) Why do you have nvarchar as datatype here? wouldnt varchar or char be
better?
2) Why 20 chars at most? If 'Ivo-0510-00001' is the longest, why not
char(14) ?
3) Why use LIKE if 'Ivo-0510-00001' is an exact match? i.e ... WHERE
Something='Ivo-0510-00001'
/Lasse
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:43932C3C-D0D8-42EE-AE09-7388DBA8D6CE@.microsoft.com...
> Hi all,
> Below are two similar SQL statements that give the same results:
> 1. SELECT * FROM InvoiceDtl WHERE IvoNum LIKE ('Ivo-0510-00001')
> 2. DECLARE @.IvoNum AS NVARCHAR (20)
> SET @.IvoNum = 'Ivo-0510-00001'
> SELECT * FROM InvoiceDtl WHERE IvoNum LIKE (@.IvoNum)
> InvoiceDtl is a big table with 2.3++ million rows. IvoNum is of type
> NVARCHAR (20) and has a non-clustered index.
> I run both statements seperately in Query Analyzer. Statement 1 takes 1-2
> seconds. But statement 2 takes 3-4 minutes (and makes my harddisk run
mad)!
> Cld anyone pls kindly advise why that is happening? TQ.