Indra PR wrote:
I've read something about this before, it is said that the performance will be better if we create a temporary table from the table in other database first.
That can be part of the solution but not the solution itself. The main point is how much data you tranfer through the link. Fore example if you have a query like
select ...
from linkedservertable
join localtable
where ...
SQL Server may have transfer the whole linked server table to this server before the join or row elimination can be done. If the same query can be written to format
select ...
from localtable
(possibly join to linkedservertavle)
where joiningcolumn in (select keycolumns
from linkedservertable
where restrictive conditions...)
the performance may be very different since the elimination may be done at the linked server. This is a very simple example, but hopefully points out the idea. Temporary tables can be used for that exact purpose if the operation cannot be re-written otherwise.
Indra PR wrote:
then it is right for me to use linked-server, and I don't have any other choice of this
Based on your description I would say that linked server is a good way to handle your situation. One more thing. If you insert/update/delete your data on both servers in a same transaction you are forced to use distributed transactions. MS DTC takes care of this, but it's good to know that this will add extra overhead to transaction handling, which then again makes the operation a bit slower. However if you want the transaction to be ACID, that's the correct way to do it.
The need to optimize rises from a bad design.My articles[^]
