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?