Slow running parameterized query against linkedsvr

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

General SQL Server Forums

New to SQL Server Programming

Slow running parameterized query against linkedsvr

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2009-03-11 : 19:28:55

I am getting some strange problem on my sql server linked server. We have linked server SQLIrvine47. When I hard coded the parameter it gives the result very fast less than a second.

.........................................................
select Count(*) From [SQLIrvine47].RepMaster.dbo.[Loan]
Where Tx_Num = 'DF0001' And Sgt_Num = 1 And Tgt_Num = 'YI'
..........................................................
If I declare the parameters it gives the result after more than 10 seconds. I need to do this kind of result for more than 30,000 records and it is so time consuming.If someone has similar problem in past plz help me.
..............................................

declare @Tx_Num varchar(8)
declare @Sgt_num int
declare @Tgt_Num varchar(2)

select @Tx_Num = 'DF0001'
select @Sgt_Num = 1
select @Tgt_Num 'YI'

select Count(*) From [SQLIrvine47].RepMaster.dbo.[Loan]
Where Tx_Num = @Tx_Num And Sgt_Num = @Sgt_Num And Tgt_Num = @Tgt_Num

Thanks
Sanjeev

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-11 : 21:41:52

I faced a similar issue sometime back. Check the difference in Execution plans...Check this thread. Might help.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119740

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-11 : 21:50:54

quote:
Originally posted by vijayisonly

I faced a similar issue sometime back. Check the difference in Execution plans...Check this thread. Might help.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119740

Infact index hints can't be used with linked server. Also Indexes are of no use in linked server as it does REMOTE SCAN.

sanjnep
Posting Yak Master

191 Posts

Posted - 2009-03-13 : 12:53:04

Still I am not able to find the solution. Is there any other way?
Thanks
SS

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 13:06:44

Check out .. http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html?

Maybe issue with Parameter Sniffing...

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources