Variable in SELECT expression causes query degradation

[kuidokylm]

How to avoid this query performance degradation

this query is fast
SELECT ACCOUNT.ID FROM DBO.ACCOUNT WHERE NOT EXISTS
( SELECT fd.[kampus inv_no] FROM [dbo].[Cust_ Ledger Entry] fd WHERE
fd.[kampus inv_no] = 'C'+CAST(ACCOUNT.ID AS VARCHAR(30)) ) AND ACCOUNT.
WINTIME_SENT IS NOT NULL

but if i use variable 'C' -> @apref , query becames very slow

DECLARE @apref VARCHAR(1)
SET @apref='C'

SELECT ACCOUNT.ID FROM DBO.ACCOUNT WHERE NOT EXISTS
( SELECT fd.[kampus inv_no] FROM [dbo].[Cust_ Ledger Entry] fd WHERE
fd.[kampus inv_no] = @apref+CAST(ACCOUNT.ID AS VARCHAR(30)) ) AND ACCOUNT.
WINTIME_SENT IS NOT NULL

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-performance/200907/1

[kuidokylm via SQLMonster.com]

One possible solution is to use dynamic SQL

DECLARE @apref VARCHAR(1),@ap VARCHAR(1000)
SET @apref='C'
SET @ap='SELECT ACCOU.ID, ACCOU.contracts_ID, ACCOU.due_date, ACCOU.
account_date, ACCOU.wintime_ready ,[dbo].[CONTRACT_TENANT_KUIDO_S](ACCOU.
contracts_ID) AS TENANT_NAME ,ACCOU.reference_number, ACCOU.WINTIME_SENT ,
(SELECT SUM(ACCOUNT_DETAILS.TOTAL_SUMMA) FROM dbo.ACCOUNT_DETAILS WHERE
ACCOUNT_DETAILS.account_ID = ACCOU.ID) AS SUMMA
FROM (
SELECT ACCOUNT.ID, ACCOUNT.contracts_ID, ACCOUNT.due_date, ACCOUNT.
account_date, ACCOUNT.WINTIME_READY
,ACCOUNT.reference_number, ACCOUNT.WINTIME_SENT FROM DBO.ACCOUNT

WHERE NOT EXISTS
( SELECT fd.[kampus inv_no] FROM [dbo].[Cust_ Ledger Entry] fd WHERE

fd.[kampus inv_no] = '''+@apref+'''+CAST(ACCOUNT.ID AS VARCHAR(30)) ) AND
ACCOUNT.WINTIME_SENT IS NOT NULL AND ACCOUNT.due_date IS NOT NULL
) ACCOU ORDER BY ACCOU.ID'

EXECUTE sp_executesql @stmt = @ap

this also works fast

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

kuidokylm (u2904@uwe) writes:
> How to avoid this query performance degradation
>
> this query is fast
> SELECT ACCOUNT.ID FROM DBO.ACCOUNT WHERE NOT EXISTS
> ( SELECT fd.[kampus inv_no] FROM [dbo].[Cust_ Ledger Entry] fd WHERE
> fd.[kampus inv_no] = 'C'+CAST(ACCOUNT.ID AS VARCHAR(30)) ) AND ACCOUNT.
> WINTIME_SENT IS NOT NULL
>
>
> but if i use variable 'C' -> @apref , query becames very slow
>
> DECLARE @apref VARCHAR(1)
> SET @apref='C'
>
> SELECT ACCOUNT.ID FROM DBO.ACCOUNT WHERE NOT EXISTS
> ( SELECT fd.[kampus inv_no] FROM [dbo].[Cust_ Ledger Entry] fd WHERE
> fd.[kampus inv_no] = @apref+CAST(ACCOUNT.ID AS VARCHAR(30)) ) AND
> ACCOUNT.WINTIME_SENT IS NOT NULL

The optimizer optimizes an entire batch at a time, and does thus not know
the values of variables, but have to make a blind guess.

Rather than using dynamic SQL, try adding

OPTION(RECOMPILE) to the query to force a recompilation each time. In
this case the optimizer will look at the current value of the variable.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx