Showing posts from March, 2012

SQL Parameter Sniffing and Performance Issues

Recently a client had a problem with a a stored procedure taking over 1 and a half minutes to execute. This is usually the case when a stored procedure is written that works perfectly well until massive amounts of data are stored by the application and required to be processed by the stored procedure.

On examining the stored procedure in question however, it had been very well written and there was nothing obvious about the code that would cause a problem. To test this, I extracted out the query from the stored procedure and ran it in a new query window in SQL Management Studio. The query ran in 3 seconds! I ran the stored procedure manually again, and it took over a minute and a half again....

The problem it turns out is Parameter Sniffing.

For those of you who are unfamiliar with parameter sniffing, the definition from Microsoft is:

“Parameter sniffing” refers to a process whereby SQL Server’s execution environment “sniffs” the current parameter values during compilation or recompil…

MVC3 View Models

I keep seeing a familiar question on message boards - How do I return multiple models to a view? I can see why this question is asked; traditionally with ASP.NET Web Forms applications, you may have a page which has multiple gridviews displaying data from different sources in your application. Part of the learning curve for MVC3 for Web Forms developers is understanding the relationship between the model and view, and how to still use one model for displaying data from different sources. This is where view models come into play.

For example, you may have a default page in your MVC3 application that needs to display a list of products and latest news. For this simple and common example, you will need:

two model entities to represent a news article and a producttwo repositories in your data access layer a view model to use with your controller
Your news article entity may be something like:

namespace Domain.Entities
    using System;
    using System.Collections.Generic;
    using Sys…

Ad Hoc Query Paging in SQL Server 2012

Implementing true paging in ASP.NET has been awkward for years, as by default all records are returned and paging is implemented by the control displaying the data. This means your database call could return hundreds or thousands of records that take an age to return, causing page loading times be drastically reduced. Worst still, you may end up having to change query timeout values just to cope with the returned results.
A solution has been available with SQL Server 2005 upwards using a combination of the RANK and OVER keywords:
SELECT rank() OVER (ORDER BY a.au_lname, a.au_fname) AS rank, a.au_lname, a.au_fname FROM authors a ORDER BY rank
With SQL Server 2012, a much better solution appears to have been implemented with ad-hoc query paging. 

By using a combination of OFFSET and FETCH, along with ORDER BY, you have much greater control of paging through a result set, improving database performance and addressing the issues above.

This sample code runs against the Person table in the Adv…