Views vs. Stored Procedures

Wow - I got to do these more often! I recently hired a new programmer, and we were having a discussion regarding the best way to handle complex queries involving lots of joins. Obviously, running one of those complex queries ad-hoc (even if it is somewhat optimized) will put a strain against your server. In certain situations, it definitely makes sense to use a 'pre-compiled' version of this query - but the question is, what kind?

You could create a stored proc that, when executed, returned the appropriate result set, or you could create a view that would allow you to query the joined tables as if they were actually one big happy table. While different schools of thought have evolved around this subject, I generally use the following rules of thumb to determine when it is appropriate to use either a Stored Proc or a View:

Use a stored proc if:

• You need to obscure specific details of the query from the querier
  
• You need to perform advanced logic or calculations against the data before returning it (beyond basic aggregation).
  
• You will only be using the data in one or two specific places and will not need to further filter, sort, or modify it.
  
  

Use a view if:

• You need the ability to further modify or manipulate the data - by specifying sorting or aggregates.
  
• You will be using this in enough places that maintaining a stored proc will be cumbersome to customize and maintain for each place.
  
• You want to select the specific output columns to be passed back to your application
  
• You need to join the results of the data to another table or tables.
  

.Net Tip - Caching and Data Updates

Wow, it's been a while! I promise to update more frequently. To make up for not posting for a while here is a very useful caching tip that I use on my own sites.

We all have user controls that appear on every page (a perfect example of this - a header control or a navigation control). User controls can very easily be cached using the OutputCache directive:

<%@ OutputCache duration="3600"%>


By embedding the above tag into a user control, it will be cached for an hour. But what if the data it displays changes during the course of the hour? How do I ensure that the data will be updated in a timely manner? The simple solution is in the VaryByCustom attribute of the tag.

VaryByCustom calls a special function called GetVaryByCustomString and passes it the value you specify for the VaryByCustom attribute in the OutputCache tag. Essentially you write the code to determine what value gets returned. With each subsequent request for the cached object, the ASP.Net engine first checks the result GetVaryByCustomString function. If the result has changed - regardless of the duration on the Cache, the system will bypass the cache and execute the code in the control.

Therefore, it is very easy to set up, say, an application variable that gets changed only when the data gets changed, then use the GetVaryByCustomString function to check for updates.

So your OutputCache line will now look like this:


<%@ OutputCache duration="3600" VaryByCustom="updateCheck"%>


Then make the following changes to your Global.asax:

1. Add the following line to your Application_Start method:
   
Application["lastUpate"]=DateTime.Now.ToString();



2. Add the following code as the function GetVaryByCustomString:
   
public override string GetVaryByCustomString(HttpContext context,string arg)
{
if(arg=="serial")
{
return Application["serial"].ToString();
}
//We need to return an empty string so that this compiles
return "";
}

   

Now you are almost done. All you still need to do to ensure that your cached pages are updated when data changes is to make the following call:

Application["lastUpate"]=DateTime.Now.ToString();


This will update the application variable, and change the result of the GetVaryByCustomString function, which will force the Cache to update.