Unanswered: slow query response from a linked server
I have 2 servers (say MAINSRV e SECSRV) running SQL2000 Standard SP3 on Windows 2000 Advanced within a NT (!) domain and each server is linked to the other.
My problem is that if I run a query returning few dozens of rows like:
SELECT * FROM MAINSRV.DbName.dbo.TblName TBLA
WHERE Fieldx = 'anyval'
from a client connected to the SECSRV server, it takes something like 35 minutes to complete, while the same query completes in no time when run on clients connected to MAINSRV.
Even the simplest SELECT Count(*) FROM... takes more than one minute from SECSRV while completing in a fraction of second from MAINSRV.
I tried to change the linked server security options (SQL/Windows), but the remote query remains slow.
There are no locks active on the table, both the servers have almost no load (CPU less than 10%, when tested) and the query returns just a few KBytes, so communication overhead will not be the problem.
Any suggestions will be very appreciated, thank you!!!
If this is regardless of client then you are probably best posting in the SQL Server forum. The long and the short is - linked server queries like this are best executed on the host server not a server that is linked to the host server - get the client to choose the appropriate server.
Security options won't make a difference. Ensure that you are subimitting exactly the same query to both servers. You have too many variables (network connections between servers, whether or not the query can be compiled by the executiing server etc).
In any event - my experience is that you won't get the same response from linked servers especially if you are linking local and remote tables.
Just an FYI - select * is a poor chooice of predicate - selecting only the columns you need is good practice and may be faster.