linked table inside of procedure running slow

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)

linked table inside of procedure running slow

Author

Topic

kapinak
Starting Member

4 Posts

Posted - 2007-12-11 : 16:01:40

Hi,

Does anyone know why when I do a select to a link server through query analyzer it returns the rows in 3 seconds, but when i run just the select from within a procedure it takes like 20 seconds?

This is mind boggling. Please help anyone out there......

SELECT DISTINCT
ts.set_date ,
ts.port ,
ts.sipid ,
cs.description ,
ts.current
FROM LNKDB1A.lightdb.dbo.jimmy_trans ts LEFT OUTER JOIN
LNKDB1A.lightdb.dbo.jimmy_sec cs ON
ts.sipid = cs.sipid
WHERE set_date BETWEEN '10/10/2007' AND '10/10/2007'
AND port IN ( SELECT port FROM LNKDB1A.lightdb.dbo.jimmy_port)
AND ts.current IS NULL
AND trans IN ('BUY','BUY TBA','SELL', 'SELL TBA')

this query along runs within 3 seconds in query analyzer.

However, I put this in a stored procedure and run the same thing .. . take like 20 -25 seconds. ..

WHY? ?? help.

kapinak
Starting Member

4 Posts

Posted - 2007-12-11 : 16:03:14

by the way,

i did a execution plan on it and they show both exactly the same.. .

this is why it's so mind boggling.

i don't understand .

I'm running sql server 2000 sp4
both local and remote server are running the same os and same db version etc..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 16:08:57

Since you are doing a DISTINCT anyway...

SELECT DISTINCT	ts.set_date,
		ts.port,
		ts.sipid,
		cs.description,
		ts.current
FROM		LNKDB1A.lightdb.dbo.jimmy_trans AS ts
INNER JOIN	LNKDB1A.lightdb.dbo.jimmy_port AS p ON p.port
LEFT JOIN	LNKDB1A.lightdb.dbo.jimmy_sec AS cs ON cs.sipid = ts.sipid
WHERE		ts.set_date BETWEEN '10/10/2007' AND '10/10/2007'
		AND ts.current IS NULL 
		AND ts.trans IN ('BUY', 'BUY TBA', 'SELL', 'SELL TBA')

E 12°55'05.25"
N 56°04'39.16"

kapinak
Starting Member

4 Posts

Posted - 2007-12-11 : 16:25:41

hmmm.. ...

doesn't explain why it runs so fast using just simple query analyzer using declare ?

also, you changed my code when pasting it again.

?

is there a known bug with microsoft using parameter in stored procedures ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 16:41:51

You could be a victim of parameter sniffing.
Not that uncommon.

E 12°55'05.25"
N 56°04'39.16"

sqlsquirrel
Starting Member

21 Posts

Posted - 2007-12-14 : 12:45:30

That is exactly it! If you are passing parameters to the stored procedure and it takes that long to run as compared to running the SQL statement in Query Analyzer then I would say "parameter sniffing". IF you are a victim of "parameter sniffing" try this in your procedure. Declare local variables in your stored procedure and assign right away the parameters that are passed in to the local variables. Then throughout your code use the local variables not the parameters that were passed in. This will solve your issue IF it is in fact "parameter sniffing".

Brett Davis
Senior SQL Server DBA
For more helpful tips checkout my blog at: http://www.lockergnome.com/sqlsquirrel/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources