Which Way is More Efficient - Declare Variable or Use Single Quote

21 Dec 2005 10:16 PM

RitaG

Hello.

Could somebody please tell me which is more efficient:

1) To declare a variable and set it to a value and then use the variable
thoughout
    the SQL code.
    E.g. DECLARE @PL Char(2)
          Set @PL = 'PL'
          When Left(MyColumn,2) = @PL Then
          When MyColumn2 = @PL Then

2) To just use the value of the variable within single quotes all the time.
    E.g. When Left(MyColumn,2) = 'PL' Then
           When MyColumn2 = 'PL' Then

TIA,
Rita

21 Dec 2005 11:10 PM

ML

IMHO efficiency is at least in part based on scalability and manageability,
so my vote goes to option 1.

ML

---
http://milambda.blogspot.com/

21 Dec 2005 11:19 PM

RitaG

Thanks. I also prefer option 1 but I was talking to somebody who said it's
not more efficient than option 2 since Stored Procedures are compiled at run
time.

Show quote

"ML" wrote:

> IMHO efficiency is at least in part based on scalability and manageability,
> so my vote goes to option 1.
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

21 Dec 2005 11:25 PM

ML

That special someone forgot to mention the fact that once compiled, the
procedures stay in the system cache until either the schema or the data
changes so much, that they need to be recompiled.

ML

---
http://milambda.blogspot.com/

21 Dec 2005 11:53 PM

--CELKO--

Where is the rest of the CASE expression?

CASE 'PL'
WHEN LEFT(my_column, 2) THEN 42
WHEN mycolumn2 THEN 43
ELSE NULL END

Version 2. What is the rule from your Intro Software Engineering
course? Never create extra variables in a program. This is more
fundamental than just SQL.

22 Dec 2005 12:22 AM

Andrew J. Kelly

Option 2 would generally be more preferable from an optimization stand
point. The optimizer does not know what the value of @PL is when it
generates the query plan. But it does know what a constant is. It can use
that information in determining how best to generate the query plan. That
would not be true for a parameter in a stored procedure though as it uses
the values passed in the first time the sp is run and compiled.

--
Andrew J. Kelly SQL MVP

Show quote

"RitaG" <Ri***@discussions.microsoft.com> wrote in message
news:AD082CD5-0580-44F4-A1A5-8D4712F6FABE@microsoft.com...
> Hello.
>
> Could somebody please tell me which is more efficient:
>
> 1) To declare a variable and set it to a value and then use the variable
> thoughout
>    the SQL code.
>    E.g. DECLARE @PL Char(2)
>          Set @PL = 'PL'
>          When Left(MyColumn,2) = @PL Then
>          When MyColumn2 = @PL Then
>
> 2) To just use the value of the variable within single quotes all the
> time.
>    E.g. When Left(MyColumn,2) = 'PL' Then
>           When MyColumn2 = 'PL' Then
>
> TIA,
> Rita