reduce the number of round trip

Question

I have 2 database in different servers: server 1 has the list of all the change list and server 2 has the list of all the files modified in the change list.

I need to combine the data from both the tables.

My first query is

Select changelist from database1;

This gives me the list of change list.

Now, for every changelist, i need to fire a query using

foreach change in changelist
select * from database2 where changelist= change;

Is there any way to send all the change list to the database at once and fetch the data in one single round trip?

Are the servers on the same network or available via say VPN? If so you can link them directly within SQL Server to allow for example `select * from server2.database2.tablename where x in (select x from server1.database1 ...)` Failing that you could bulk insert `changelist` into `database2.sometemptable` & select across a join. — Alex K., Sep 03 '13 at 16:38
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Sep 03 '13 at 16:42
[Selecting data from two different servers in SQL Server](http://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server) — huMpty duMpty, Sep 03 '13 at 16:58

Hanlet Escaño · Answer 1 · 2013-09-03T16:51:51.060

1

You can do a JOIN statement and add a Linked Server, and after you add one of your servers as linked server, you can do something like this:

SELECT * FROM ChangeList, [Database2].[dbo].[Change]

edited Sep 03 '13 at 16:51

answered Sep 03 '13 at 16:41

Hanlet Escaño

17,114
8
52
75

score 1 · Answer 2 · answered Sep 03 '13 at 16:43

If a significant percentage of the items in that other table are going to be used, and you have sufficient resources (namely memory) to be able to, one option is to just pull down the entire change list table from your second source and then do the work from there in your C# program. If all/most of the data is needed, this is not wasteful at all. If you only need a small portion of the data, but this still works, it may be the easiest option, and so may be worth doing anyway.

Another option is to try to build a query like:

select * from database2 where changelist in (change1, change2, change3, ...);

This is an option as long as the number of changes is sufficiently small. You'll generally have a hard limit based on your database of a thousand or two, although this option can get a bit messy before you get to that point.

score 0 · Answer 3 · answered Sep 03 '13 at 19:28

0

Either of these should do what you need, but I'm not a huge fan of cursors.

Select

SELECT *
FROM database2
WHERE changelist IN (Select Changelist from database1)

Cursor

DECLARE Change CURSOR FOR
SELECT Changelist from Database1

OPEN Change;
FETCH NEXT FROM Change;
WHILE @@FETCH_STATUS = 0
   BEGIN
      select * from database2 where changelist= Change;
   END;

answered Sep 03 '13 at 19:28

Volearix

1,573
3
23
49

the second is still resulting in N queries against the second database. It *might* be a bit better coming from the second DB (or worse, potentially) but in the end it's still going over the wire to perform those N queries. If you've already taken the time to link the two databases then logically what's being done here is a `JOIN`. – Servy Sep 03 '13 at 20:18

reduce the number of round trip

3 Answers3