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.

E.G.

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.
ASKER CERTIFIED SOLUTION
Qlemo
"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