ASP.NET Output Caching vs ASP.NET Object Caching

Question

Can you please help me with deciding which one of the following scenarios will work the best.

I have 3 user controls. Each control needs to generate content based on the results of SQL Server query, for example user control #1 populates a gridview with the output of a query.

I want to make use of Caching in order to reduce round-trips to my SQL Server Database. I came up with the following two solutions, and would like to know which one will work the best.

Scenario #1:

Put all 3 user control's queries in a single SQL Server Stored procedure (Batching), execute the stored procedure outside of the User Controls inside the containing page, and then provide each user control with its query output. Round-trips to the database will be reduced by placing all 3 queries in a single stored procedure.
Caching in this scenario will happen by caching the output of the stored procedure. Therefore, the output of the stored procedure will be taken from memory each time to provide each user control with its output, after which each user control will generate its output. About 188 different versions of the query will be cached by ASP.NET object caching.

Scenario #2:

Query the SQL Server database individually inside of each User Control, and then generate each user control's output with the query results that each one obtained. This means that each user control will individually call the SQL Server Database. Caching in this scenario will happen by caching the output of each user control, thus the user controls won't have to generate its output every time, and can get its output directly from memory. The catch in this scenario is that I will have to cache about 188 different versions of one or more of the user controls.

Thanks in advance

Two quick questions: 1) How much overlap is there in the three DB calls? Do the controls share any common data?, 2) Is it possible for the controls to defer there calls back to the db, or are all three controls *always* loaded? — GrayWizardx, Jan 31 '12 at 23:21

score 1 · Accepted Answer · answered Feb 01 '12 at 00:00

Either way, you'll have to cache 188 versions of data. My vote would be: output caching - because it requires less processing to generate the HTML (that is to be included into the aspx page; which is, after all, the ultimate goal), compared to achieving almost exactly the same if in-memory caching is done (only with more processing time, as cached data would have to be converted into HTML every time a single user requests it, which would be far more than 188 times).

You obviously understand how output caching works and how it different versions of HTML can be cached depending on the request url (query).

score 0 · Answer 2 · answered Feb 01 '12 at 00:53

I dont see the two scenarios as necessarily mutually exclusive. There should be no reason that you cant extract the SQL query logic out and then use output caching with the controls if you so desire.

Again though its really going to vary a lot more based on how frequently the data changes and how expensive it is to get the data to begin with. There is for instance nothing in your question that says you couldnt make a single DB call to get all the data (for all versions) and keep it in App cache, then generate controls as needed.

ASP.NET Output Caching vs ASP.NET Object Caching

2 Answers2