Linked-Server SQL Server 2000
-
Dear all, I have a linked-server on my database. My senior in my company make it like that, because in the future, we will have some servers to handle some applications in the company. But after we make it so and running it, the transfer is so damn slow. Is it because the linked-server? Are there any better ways to access database from multi-server in one application. Using mirror or something? If you have any reference for this, please help me. Thanks.
- No Signature Available -
-
Dear all, I have a linked-server on my database. My senior in my company make it like that, because in the future, we will have some servers to handle some applications in the company. But after we make it so and running it, the transfer is so damn slow. Is it because the linked-server? Are there any better ways to access database from multi-server in one application. Using mirror or something? If you have any reference for this, please help me. Thanks.
- No Signature Available -
Indra PR wrote:
the transfer is so damn slow
Operations done over linked server may be performed very differently than those executed locally. You can use execution plan to investigate what's being done and why it's slow. So you should investigate each problem statement (especially multi database joins are often problematic). The idea in optimizing multi server statements is that you transfer minimal amount of data using the linked server so in many cases the SQL may have to be rewritten.
Indra PR wrote:
Are there any better ways to access database from multi-server in one application
Linked server is intended for this. Of course there are other techniques such as replication etc, but the data is not typically up to date in those scenarios or you cannot update the data on the other end (stand-by solution etc)
The need to optimize rises from a bad design.My articles[^]
-
Indra PR wrote:
the transfer is so damn slow
Operations done over linked server may be performed very differently than those executed locally. You can use execution plan to investigate what's being done and why it's slow. So you should investigate each problem statement (especially multi database joins are often problematic). The idea in optimizing multi server statements is that you transfer minimal amount of data using the linked server so in many cases the SQL may have to be rewritten.
Indra PR wrote:
Are there any better ways to access database from multi-server in one application
Linked server is intended for this. Of course there are other techniques such as replication etc, but the data is not typically up to date in those scenarios or you cannot update the data on the other end (stand-by solution etc)
The need to optimize rises from a bad design.My articles[^]
Mika Wendelius wrote:
especially multi database joins are often problematic
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. Then we join the temporary table with the 2nd table. Is that right?
Mika Wendelius wrote:
Linked server is intended for this. Of course there are other techniques such as replication etc, but the data is not typically up to date in those scenarios or you cannot update the data on the other end (stand-by solution etc)
If it so, then it is right for me to use linked-server, and I don't have any other choice of this, the only way is to optimize the query. Isn't it? Correct me if I'm wrong. ;)
- No Signature Available -
-
Mika Wendelius wrote:
especially multi database joins are often problematic
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. Then we join the temporary table with the 2nd table. Is that right?
Mika Wendelius wrote:
Linked server is intended for this. Of course there are other techniques such as replication etc, but the data is not typically up to date in those scenarios or you cannot update the data on the other end (stand-by solution etc)
If it so, then it is right for me to use linked-server, and I don't have any other choice of this, the only way is to optimize the query. Isn't it? Correct me if I'm wrong. ;)
- No Signature Available -
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[^]