[Last Call] Learn how to a build a cloud-first strategyRegister Now


Where is the bottleneck?

Posted on 2012-08-28
Medium Priority
Last Modified: 2012-08-28
Hi, I hope I can explain my issue and get some ideas:

I basically load QueryResults into a dataset and loop through those results and perform calculations on the results.

This is all happening fine when my QueryResults are, lets say > 500 records.  Once the QueryResults become a set of 1500 rows, for example, my server load on the DB goes way high and the operations take a minute instead of 5 seconds or less.

Where do I need to split the operations.  Is 1500 rows just too big a result to process?  

Question by:kapes13
  • 2
LVL 12

Accepted Solution

Tony303 earned 2000 total points
ID: 38342844
Have a look at the estimates execution plan perhaps. The items with the highest percent are the problem areas. Is there a table scan in the plan? that would be traditionally the biggest bottleneck.

Author Comment

ID: 38342901
Yup I am looking over those times right now, it seems the first band of processing can handle substantially more objects than the second batch (where records go from the 400 to the 1500, of course), but I am still trying to see where the lapse would be since they run the same logic, but you are on the right path for sure, and I know I am too, it's just at which point I want to draw the final line.

Author Closing Comment

ID: 38343012
Hopefully the CPU times are accurate and I did not miss some memory hole somewhere that is not allowing the record set to really be processed in the proper area, so we are swapping out some processing routines and letting the smaller record sets do the bulk of the processing and see how that pans out, thanks all.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question