Performance of inter-database query (between linked servers)
- by Swoosh
I have an import between 2 linked servers. I basically got to get the data from a multiple join into a table on my side.
The current query is something like this:
select a.*
from db1.dbo.tbl1 a
inner join db1.dbo.tbl2 on ...
inner join db1.dbo.tbl3 on ...
inner join db1.dbo.tbl4 on ...
inner join db2.dbo.myside on ...
db1 = linked server
db2 = my own database
After this one, I am using an insert into + select to add this data in my table which is located in db2. (usually few hundred records - this import running once a minute)
My question is related to performance. The tables on the linked server (tbl1, tbl2, tbl3, tbl4) are huge tables, with millions of records, and it is slowing down the import process.
I was told that, if I do the join on the "other" side (db1 - linked server) for example in a stored procedure, than, even if the query looks the same, it would run faster. Is that right? This is kinda hard to test. Note that the join contains a table from my database too.
Also. are there other "tricks" I could use in order to make this run faster? Thanks
