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?
4
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.
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.
3