query running slow for @append variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query running slow for @append variable

Author

Topic

Rg
Starting Member

10 Posts

Posted - 2011-12-13 : 02:04:13

in the below query if i pass
directly Id+'d ' the query bring result 4 sec
otherwise it takes 15 min for append variable

can anyone help me for this ?

declare @type varchar(4)
declare @append char

if @type='MMM'

begin
set
set @append='D'
end

if @type='VBN'

begin
set
set @append=''
end

select @type='MMM'
select
select TId,Tdate from A1
inner join A2 on a1.id+@append=a2.Id
where TYPE=@type

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 02:10:29

it has to do concatenation and then do join on the fly after retrieving value for variable at runtime. why not create a derived table with this concatenation and then do join?

ie like


select TId,Tdate from (select other columns...,id + @append as id from A1)a1 
inner join A2 on a1.id=a2.Id
where TYPE=@type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources