I am trying to understand what is causing a tremendous increase in run time for a series of queries.
I have run them individually and found the actual time to be 3-5 times faster than when run in series.
I tested this on a small set (just over 500 records), but this also runs on data sets in the 10-20k rows.
Query 1 -- 500 rows, .020 min <-- This is a select into Query with joined tables on 2 different servers.
Query 2 -- 475 rows, 2.55 min <-- This is an update Query with joined tables on 2 different servers.
Query 3 -- 25 rows, 0.15 min <-- Also and update Query with joined tables on 2 different servers, and a statement to select only rows not affected in Query 2.
Query 4A-D -- Upto 500 rows each, 0.02 min in total. <-- simple update queries to format fields collected above.
Total run time when executed step by step - Just under 3 min. When I select all steps and run in a batch this takes over 8 min.
Can anyone explain why this would be, and how to minimize this? Bringing the tables to the same server is not an option. I am primarilly interested in why the whole process runs quicker when done step by step.