June 11, 2003 at 5:35 am
I have 2 linked server
I am logging in to the first serve and preform a select statment against table T1 in DB1 that is located on the second server
'Select f1,f2 srv2.db1.dbo.t1 where f1=v1'
That works fine .
When I am trying to Update data on the second server it takes a lot of time
some time i am getting a time out.
The second server is not bussy with any activity and no locking is in place
'Update srv2.db1.dbo.t1 set f1=v1,f2=v2 where f1=oldV1'
Is there any thing I sould do to improve performance.
When I am running the same update query directly on the second server it works with a blink of an eye.
connectivty library configuration is in place and so is the linked server configuration
Servers are connected through 100 Mbit Lan
June 11, 2003 at 6:22 am
Supplamental
Insert with the same prefrences works fast
Delete Works Very Slow
June 11, 2003 at 12:23 pm
Supplamental
I have checked the query plan and found out that when I am updateing or deleteing a remote table scan is preformed
I have tiried to use thw "with (index(0))"
but the analyzer wont allow me!
I am using sql server 7 sp3
June 11, 2003 at 4:16 pm
I don't know how much of the problem is solved in the enterprise version. We didn't have that option and finally resorted to using a middle tier to address each machine directly and separately. The response was 100 times faster.
Also - If you are not using dynamic SQL, you can perform the updates through stored procedures which maintains the speed.
I'm sure there are other ways - I am anxious to see other responses because my new project requires extremely fast response when searching multiple machines.
Guarddata-
June 11, 2003 at 10:20 pm
Tanks but a middle tier is out of the question due to security risk
I am using the link server method to by path decleration on the company fierwall
if i open a one way sql comunication port between 2 servers on the fire wall I can relay all DML through the server which is bheing the fire wall and is accessible to company user to the server that is in the DMS and is not accessible to company users directly
June 15, 2003 at 12:58 am
OK Problem Solved
If the SQL Server Is performing a Remote table Scan than "I will make the table local"
By writing a stored procedure that gets some paramters with the new values and the condition .
Now I have to call it
exec RemoteServer.DB.dbo.sp @pr1= ,,, @prn=
and it works just fine
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply