Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
juicyapple
Posting Yak Master
176 Posts |
 Posted - 2009-08-13 : 23:42:48
|
| Hi,I am facing a problem when run select statement to query other database through linked server, it takes more than 1 hour. But if the query is executed at the original database, it only takes 15 seconds. Any one has this kind of experiece? is this due to the query through linked server?Thanks. |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2009-08-14 : 02:59:09
|
If you are accessing the object directly using linked server name then its possible you are pulling a large amount of data over the wire and then applying conditions after the fact. If this is the case then you are better off sending the query over the wire to be executed on the remote server and only retrieving the resultset over the wire (which hopefully is much smaller than entire table).For example:select * from YourServer.YourDB.dbo.YourTable where PK = 1-- vsselect *from openquery(YourServer, 'select * from YourDB.dbo.YourTable where PK = 1') or, exec a remote proc.Nathan Skerl |
 |
|
|
juicyapple
Posting Yak Master
176 Posts |
Posted - 2009-08-14 : 04:43:37
|
I tried to create view at the Server1.databaseA, then query the view from Server2.databaseB, manage to reduce the time to about 2 minutes.quote:
Originally posted by nathans
If you are accessing the object directly using linked server name then its possible you are pulling a large amount of data over the wire and then applying conditions after the fact. If this is the case then you are better off sending the query over the wire to be executed on the remote server and only retrieving the resultset over the wire (which hopefully is much smaller than entire table).For example:select * from YourServer.YourDB.dbo.YourTable where PK = 1-- vsselect *from openquery(YourServer, 'select * from YourDB.dbo.YourTable where PK = 1') or, exec a remote proc.Nathan Skerl
|
|
|
|
|
|
|
Slowness in remote query