2

I am redeveloping an old ecommerce website which is currently written in C# ASP.NET 2.0.

Because this is a reasonably high volume website and has suffered performance issues I want to develop in the most efficient manner possible.

To minimise database trips (using SQL Server 2012), as database performance is the usual bottleneck in the current system, I am looking at creating stored procedures that return everything required for the current page in one query as multiple recordsets.

For example the home page query might return the promotional banner image filenames and text, the new release products, the top selling products, etc. The product page might return the product details, related products, etc.

I can't remember seeing this technique used often if at all. Is returning all data in a single database request more efficient than retrieving the same data in several database requests? Are there any flaws in doing this?

Improve this question
4
  • Noted. Question edited to be more question-like. – johna Feb 20 '14 at 10:15
  • The first rule of optimization is to measure performance - know exactly where the bottlenecks are and address each in turn. How will your approach accomplish that? See also Things you should never do (a very old article, but still true) – Dan Pichelman Feb 21 '14 at 16:50
  • 2
    You are likely to find that tuning your SQL queries will give you much more of a performance boost than returning multiple datasets at a time. – Adam Zuckerman Feb 27 '14 at 0:59
  • Yeah, what @AdamZuckerman said. SELECT * FROM is far more evil than SELECT XXX FROM, SELECT YYY FROM, ... At least for moderately small values of "...". – Ross Patterson Mar 14 '14 at 10:57
1

Yes if:

  • there are lots of queries per page (latency will be reduced)
  • your web site and database are on different machines (network latency will be reduced)

No (not much) if:

  • there are not many queries per page
  • the web site and database are on the same server

So basically it will make it more efficient only if latency is significant.

Improve this answer
0

Your description of the home page seems like a dashboard page. The best practice for powering a dashboard page is to allow the data to be populated with queries that are executed in parallel. This allows any bad performing queries to be tuned independently.

This also makes it easier to add a new data view if needed when the home page evolves.

If all the views are going to be powered by a single source, then the display of the results are constrained to when the single source starts to return data. This time will be at least the time it takes to execute the slowest query.

Improve this answer
3
  • You make some good points. How would you typically execute queries in parallel - via individual AJAX requests? Incidently, I did do some comparison of multiple (sequential) queries versus single combined query with multiple recordsets and the page did execute significantly faster (blog.johnavis.com/blog/750/…). – johna May 21 '15 at 2:49
  • This can be due to a few reasons: 1. Each of the stored procedure invoked separately has an overhead that adds up to be greater than calling them all locally from another stored procedure. 2. The server architecture might not be optimized in processing parallel requests. For e.g., does the browser send the multiple requests on a single HTTP connection? 3. If the data being loaded has a lot of overlap, then it makes sense to load them all together From a scalability perspective, as each stored procedures grows to be complex, it makes sense to fetch them separately. – codedabbler May 21 '15 at 14:22
  • Yes AJAX is fine – codedabbler May 21 '15 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.