troubleshooting Question

Understanding SQL Overhead

Avatar of propharma
propharma asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
9 Comments2 Solutions492 ViewsLast Modified:
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.
"Batchelor", Developer and EE Topic Advisor
Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros