May 8, 2006 at 4:04 pm
My remote production server is running SQL Server 2000 and I have it set up as a linked server to a local machine running SQL Server 2005.
If I fire up Management Studio and run the query on the remote machine it returns results in about 2 minutes. If I run the query on the local machine and reference the remote production machine as a linked server it takes upwards of 1 hour , it hasn't finished yet.
Is there some "Don't run like a POS" checkbox somewhere I need to check?
The result set is about 20k records.
May 9, 2006 at 9:44 am
I've identified the mechanism that is causing the extrememly slow query times. It turns out that doing a join between a table on a linked server and local table causes a great deal of overhead.
The general form of the queries I've tried are:
select ext.x, ext.y, ext.z
from [linkedServer].[db].[dbo].
as ext
left outer join [localTable] as loc
where (loc.SomeID is not null)
and
select ext.x, ext.y, ext.z
from [linkedServer].[db].[dbo].
as ext
where ext.SomeID in (select SomeID from [localTable] as loc)
I'm sure I can come up with a strategy that will avoid the need for a remote/local table join, but does anyone have any insight on how to make such a join more efficient?
May 9, 2006 at 12:01 pm
If the linked table that your referencing is not huge in size then you could possibly select that table (use a select into clause) into your local machine as a temp table before you run your join statment and then join it locally.
May 9, 2006 at 4:25 pm
I've run into another interesting problem when running this query in our production environment.
We have 2 servers S1 and S2. S1 is our production OLTP server, S2 is our production OLAP server.
When I run a query of the form above I get the following error:
OLE DB provider "SQLNCLI" for linked server "S1" returned message "Cannot create new connection because in manual or distributed transaction mode.".
Msg 7320, Level 16, State 2, Procedure StoredProc_p, Line 10
Cannot execute the query "SELECT "Tbl1006"."SomeID" "Col1210","Tbl1006"."SomeNo" "Col1211","Tbl1006"."SomeDate" FROM "DB"."dbo"."TBL" "Tbl1006" WHERE "Tbl1006"."SomeID"=?" against OLE DB provider "SQLNCLI" for linked server "S1".
So it looks to me like the OLTP server (S1) gets the results of the joined query and then the OLAP server queries the OLTP server a second time to get the cached results? Does anyone have any experience with this? I may post this problem in a new thread if I don't get any attention here.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply