Linked server query delaying for 10 minutes

Question

Linked server query delaying for 10 minutes

k50

SSC Enthusiast

Points: 114
More actions
September 8, 2020 at 8:31 pm

#3787123

have a linked server, when i run
select * from mylink.db.dbo.mytable
it takes exactly 10 minutes to run. returns about 700 rows. if i change to select count(*) from mylink.db.dbo.mytable, still 10 minutes.
if i use openquery, it returns instantly.
select * from openquery(mylink, 'select * from db.dbo.mytable');
so its using the same linked server, what is causing the delay??
any help would be greatly appreciated!!
thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18765 More actions · Answer 1

This post does not make much sense so I presume your query actually has a WHERE clause.

If the linked server has a different collation etc then the four part naming convention will bring over all the rows in mytable and then apply the filter(s) in the WHERE clause. (eg If mytable has ten million rows it will bring over all ten million rows and then apply the filter(s) to return the 700 rows.)

When using OPENQUERY the filters are always applied on the remote server so only 700 rows will be brought over.

k50 SSC Enthusiast Points: 114 More actions · Answer 2

The table has exactly 733 rows. Query is to bring all the rows so there is no where clause. There is a timeout somewhere that is causing the query to delay exactly 600 seconds. I have tried: select *, select col1 (just a single column), select count(*), select * where col1 = xxx. In every case, the query returns exactly 10 minutes later. so there is a delay somewhere of 600 seconds but not sure why or where.

thanks!

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

The key difference between openquery and a linked server query is where the query processing occurs. In a linked query, all the processing occurs locally. In an openquery, the processing occurs on the remote server, and only the data is moved.

Now, in your case, a SELECT * query without a WHERE clause, there really isn't any query processing as such. However, I suspect that's still the issue. Is the query on the remote server against a view or something like that which would entail moving a lot of data locally in order to put the data together? Guessing here honestly.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

k50 SSC Enthusiast Points: 114 More actions · Answer 4

thanks for the thoughts, very small table. and one piece of information that i should have shared right away. this is our QA setup. 2 servers one local one remote. now we have the same setup in production, and in that env, both queries return right away. the linked query returns right away, the openquery returns right away. somehow in our QA env, the query is being held, or the results are being held for exactly 10 minutes.