April 22, 2003 at 12:04 pm
I have two SQL Server instances on the same box (INSTANCE1 AND INSTANCE1\NAMED). I have database TESTA on INSTANCE1 and database TESTB on INSTANCE1\NAMED. I have created a linked server on INSTANCE1 to have visibility to INSTANCE1\NAMED database TESTB:
sp_addlinkedserver @server = 'LINKB', @srvproduct = '', @provider = 'SQLOLEDB',
@datasrc = 'INSTANCE1\NAMED', @catalog = 'TESTB'
I added the appropriate linked server logins, and then created views on all the tables that reside in TESTB. I am now performing updates on these views while in TESTA on INSTANCE1. Performance is NOT good: it takes approximately 20 minutes to update 1000 rows on one of the 'remote' tables.
One thing that needs to be mentioned is that we are connected to our TESTA database via an ODBC data source (driver SQL Server) created on my workstation with the default database as TESTA. We then use a fourth-generation tool that performs the various SQL commands (including setting bind variables, etc) via the ODBC data source.
I am new to SQL Server, and would greatly appreciate in helpful advice on improving performance. Thanks!
April 22, 2003 at 1:23 pm
Do you have performance issue in select statement too? Can you post your update statement? Have indexes be defined properly on the table? What is "OLE DB Provider Options" setting in linked server
April 23, 2003 at 1:18 am
If you are doing an UPDATE tablename WHERE col=x ..., what may be happening is that SQL Server is reading all of the rows in the remote table over the network, in order to determine which ones to update.
You may be better off writing a stored procedure on the remote server that does the updates, and call the remote stored procedure instead of doing an update statement.
April 23, 2003 at 10:52 am
Thank you for the two responses. It looks like we have a problem with our fourth-generation tool, and that team is going to investigate. I was able to run SQL Profiler traces on the two instances and found there to be cursor type processing on the remote table (which is causing our performance problem). Thanks again for your responses.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply