May 20, 2004 at 6:41 am
Hi,
I am working on a VB project that involves retrieval of data from two Sql Server databases. My app needs to search and update records that are contained on different servers. Currently, I am creating a temp table on one of the servers and importing data from the other through VB. This process is decreasing my performance and I am looking for a more efficient way. I am considering retrieving data using Linked servers, but I am not sure about performance issues. Any suggestions would be helpful.
May 21, 2004 at 11:32 am
Your performance would definitely increase using a linked server approach. That way the Database will be doing the processing rather than your VB Client side cursor. In fact you can just link the tables as if they were in the same database. Please be sure to reference the linked server table using the proper syntax:
SELECT
Fields
FROM
LocalTable A
INNER JOIN
LinkedServer.DatabaseName.dbo.TableName B ON A.CommonField = B.CommonField
Hope this helps.
May 24, 2004 at 2:37 am
Hi there ...
I also had that problem - I had to use 5 Tables strewn over 5 SQL Servers - some SQL 7.0 some SQL 2000.
Be sure to configure the linkserver properly regarding collation and so on. When using the same SQL Server version with the same default collation you can congifure the linkserver as collationcompatible and set useremotecollation to 1.
I found this boosted my perfomance for the use of linkserver drastically. When using different versions you have to find the right collation and sometimes have to convert collations (SQL 2000 - not SQL 7.0) during a join
hope this helps
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply