February 17, 2011 at 7:56 am
Hi,
I execute a query using Linked server which is slow but it is faster
when i execute it directly on the server . The linked server is SQL Server 2008 database and I am executing it from SQL 2008 Database
How linked server works?
My assumption it brings the data(all the columns of the tables mentioned in the query) from the remote machine and caches it in the local tempdb space from where I am executing the Linked server and then filters the data based on the query. Is that right? I assume that is the reason for performance problem. If i execute the query using linked server i get "Query timeout expired" message
February 17, 2011 at 8:18 am
More likely, it's an issue with distributed transactions. Those can be very, very slow.
I'd have to see the query to give advice on it, but take a look at "Distributed Transaction Coordinator" online and see if you can work on the query to optimize for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 17, 2011 at 8:28 am
Hi,
Have a look on "Distributed Queries,Accessing External Data,Optimizing Distributed Queries,Guidelines for Using Distributed Queries" in the Help File you will get some idea about it
Thanks
Parthi
February 17, 2011 at 8:57 am
the query is using linked server. but if i execute without the linked server directly on the server it works fine.
select orderguid from [cc-datamart-pro].scmprod.dbo.cv3orderuserdata
where touchedwhen >'2011-02-14 23:57:21.873'
and UserDataCode = 'PHM_Strength'
following is the error message
"[b]Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "cc-datamart-pro" reported an error. Execution terminated by the provider because a resource limit was reached.
Msg 7421, Level 16, State 2, Line 1
Cannot fetch the rowset from OLE DB provider "SQLNCLI10" for linked server "cc-datamart-pro". ."[/b]
February 17, 2011 at 9:03 am
if that's a huge table and you would keep using it constantly, i would suggest you set up a trans/snapshot replication between the two databases. so you would be able to run the query against the table from the local server instead of using a linked server.
February 17, 2011 at 9:06 am
Hi
Try this
In the linked Server properties, Server Options page, Connection Timeout (set to 0) and Querry Timeout (set to 3600);
The remote server properties, Connections page, Remote Qquery timeout (set to 3600)
Thanks
Parthi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply