November 10, 2006 at 4:26 am
I'm running a stored procedure on 2000 sql server that calls a stored procedure on a 2005 x32 sql server.
The process works for most queries. Basically, the initial SP is a dynamic string builder that calls the final SP is counting rows that match criteria passed as SP Params. The process uses the select * from openquery() method. (see below)
IF the number of rows to be counted is in the thousands the whole process completes in under 2secs and everyone is jolly. If the numbers of rows is 100k then it returns in 20secs, which is acceptable. However, if the rows counted is in the 1000k region then the process fails with a timeout message at 600secs.
lets assume that the dynamic query results in
"select * from openquery(beastRL01,'exec linked_sp_test ''UK'',''all_codes'',''All'',''london''')" which is a large count that fails
If I run the query in SSMS against the 2000 server it will still fail at 600secs.
If I extract the final SP command:
"exec linked_sp_test 'UK','all_codes','All','london'"
and run against the 2005 server it will complete in 26secs.
I'm confused as to why the running this command over the linkedServer results in the timeOut.
Is it that the the final query is not using the best index in the query plan? How could I tell this?
Is it that the calling server is getting the rows and doing the counting? If so, can I issue an instruction for the counting to be done at that reciveing server?
Is it something else? If anyone has any clues where to look I would be most grateful.
Thanks
Garry
November 13, 2006 at 8:00 am
This was removed by the editor as SPAM
November 13, 2006 at 10:32 am
Try to go to Server Properties -> Connections and check the Remote Query timeout parameter - change to 0 for unlimited and it should not time out anymore
November 13, 2006 at 10:37 am
Thanks G. Nocholls
That may stop it timing out. I'll take a look.
However, I'm more interested in solving the riddle of why it takes so long over the serverLink.
I don't understand why the SP on the 2005 server runs at 26secs, but calling this SP over the serverLink runns at 600secs +_
Garry
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply