Querying between databases (between linked servers)

Question

I have an import between two linked servers. I basically got to get data from multiple joins to a table on my side.

The current query looks 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 that, I use the insert in + select to add this data to my table, which is in db2. (usually several hundred records - this import works once a minute)

My question is about performance. The tables on the linked server (tbl1, tbl2, tbl3, tbl4) are huge tables with millions of records and slow down the import process. I was told that if I join the "other" side (db1 is a connected server), for example, in a stored procedure, than even if the request looks the same, it will work faster. It is right? This is pretty hard to verify. Please note that the connection also contains a table from my database.

Moreover. Are there any other “tricks” that I could use to speed up this launch? Thanks

Answer 1

Correctly place the stored procedure on db1 to increase performance, so less data should pass through the channel, since most of it is filtered in the connections.

If the data on the other hand is static, why not post it in a materialized way? Thus, you only need to update the data once a day, and not every time the request is executed.

Answer 2

It really depends on what your request is doing. You can use the “remote” prompt for connections to make the connection happen on the linked server. i.e:.

 select a.* from db1.dbo.tbl1 a inner remote join db1.dbo.tbl2 on ... inner remote join db1.dbo.tbl3 on ... inner remote join db1.dbo.tbl4 on ... inner join db2.dbo.myside on ... 

(I assume that you left the server out of the above and that all links "db1." Are really "linkedserver.db1".)

If you can do most of the work only with information about the linked server, you can use OPENQUERY to speed things up. i.e:.

 select a.* from OPENQUERY(db1, '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 ... ') a inner join db2.dbo.myside on ... 

But the best way to speed it up might be to have a checklist on the linked server to control the return, but again it depends on your actual request, what it does and what permissions you have on the linked server.

Answer 3

Stored procedures are cached, so the first time the stored procedure is started, it will take some time. All further calls to this stored procedure will be much faster. You can see the impact of performance by including performance statistics in SSMS.

To improve connection performance, make sure you have indexes.

Note that cross-server inserts are dangerous because you rely on the network. I'm also not sure if you can use transactions in this scenario. If not, then this is another problem.

I saw a scenario where the temp database could not handle such an insert, and the fix was to use a cursor. It was much slower, but more reliable for this scenario.