August 1, 2005 at 2:21 pm
Dear Noel,
It is indeed an msSQL linked server. I have tried the 4 part name syntax but it is much slower (much!). I could never work out why.
Rolf
August 1, 2005 at 3:59 pm
If you get a long execution time it could be because you may not be getting the remote execution that is needed. Assuming both SQL Server are 2000
Do this:
1. Make sure you have selected collation Compatible in the linked server properties
2. set lazy schema validation on that server to true using :
exec sp_serveroption 'yourservername', 'lazy schema validation', true
3. Run the estimated execution plan and check if you get the "remote query" operator
The other technique is to create a procedure on the remote server and call it from the local one
* Noel
August 2, 2005 at 3:14 am
Hi there,
Collation compatible is on and I have run the sp_serveroption to set it to lazy schema.
When I run a simple query still takes a long time and in the execution plan I still get the remote query operator which is 100% of the cost.
Is this right..? Incidentally using the OPENQUERY method the execution plan show a REMOTE SCAN operator rather than a REMOTE QUERY operator.
Rolf
August 2, 2005 at 9:57 am
If you still get long execution time apparently there is something not very efficiently done (M$ fault?). This is arguably one of the most "gray" areas in which you would like M$ to shed a bit more light( The so called push vs pull of the data). According to MS documentation Remote query should indicate that the remote server should perform the action and you would hope that it does efficiently. Somehow the processing time tend to be shorter on the server closer to the data (that's why I offer you the possibility of creating the stored procedure there)
About the OpenQuery REMOTE SCAN is just an indication that it will force the execution remotely without any further intervention of the local Server which is not the case when using 4 part names because it has to figure out that it can delegate the processing to the remote Sever before but that should not take that much of an effort (I would think!?).
I am assuming you have an index on the OrderID Column in the remote sever, right?
* Noel
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply