Pages

Wednesday, 28 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 recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word “current” refers to the parameter values present in the statement call that caused a compilation or a recompilation.

The query contained in the stored procedure required just one input parameter, however the parameter was used multiple times. The results from the stored procedure could also vary wildly depending on the parameter value being passed in each time the stored procedure is called.

The solution

The solution was actually fairly straightforward: disable parameter sniffing for that stored procedure.

To do this, you need to create a local variable in your stored procedure, and set the value of that variable to the value of your input parameter.

For example, your original stored procedure may look like:


 CREATE PROCEDURE GetEvents
     @EventId int
 AS
 BEGIN
     SELECT Title 
     FROM dbo.Events
     WHERE Id = @EventId
 END


To disable parameter sniffing, you would change the above example to:


 CREATE PROCEDURE GetEvents
     @EventId int
 AS
 BEGIN
    DECLARE @LocalEventID int
    SET @LocalEventID = @EventId
    SELECT Title 
    FROM dbo.Events
    WHERE Id = @LocalEventID
 END


Tuesday, 27 March 2012

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 product
  • two 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 System.Linq;
    using System.Text;

 
    public class News
    {
        public int ID { get; set; }

        public string Headline { get; set; }

        public string Article { get; set; }

        public DateTime Date { get; set; }

    }
}

Your product entity may be something like:

namespace Domain.Entities
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;


    public class Products
    {
        public int ID { get; set; }

        public string Name { get; set; }

    }
}

In your data access layer, you should have a repository for news articles and products. Each of these repositories have methods to return data from your data store. In both the repository examples below, generic lists are returned from a method in each repository call "GetAll".

namespace Domain.Repositories
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Domain.Entities;
    using System.Data;

public class NewsRepository
    {

        public List<News> GetAll()
        {
            List<News> articles = new List<News>();
            // Call to database here to return data and add news entities to the articles generic list
            return articles;

        }

    }
}

namespace Domain.Repositories
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Domain.Entities;
    using System.Data;

public class ProductsRepository
    {

        public List<Product> GetAll()
        {
            List<Product> products = new List<Product>();
            // Call to database here to return data and add product entities to the products generic list
            return products;

        }

    }
}

You will then need a view model to combine the list of products and news articles you want to display on your default page:

namespace BusinessLogic.ViewModels
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Domain.Entities;
    using Domain.Repositories;
    using Business_Logic.ViewModels;

public class Home
    {

        private List<News> articles
        private List<Product> products;

        public List<News> Articles
        {
            get
            {
                if (articles != null)
                {
                    return articles;
                }
                else
                {
                    return null;
                };
            }
        }
        public List<Product> Products
        {
            get
            {
                if (products != null)
                {
                    return products;
                }
                else { return null; };
            }
        }


        public Home()
        {
            var articleDetails = new NewsRepository();
            articleDetails = articleDetails.GetAll();
            articleDetails = null;

            var productsList = new ProductsRepository();
            products = productsList.GetAll();
            productsList = null;
        }
    }
}


The above view model is just a regular class, with nothing special about it. It has two public properties with backing fields that are populated when the class is instantiated. One property exposes a list of news articles, the other a list of products. This view model can then be passed by your controller to your view.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using BusinessLogic.ViewModels;

namespace WebUI.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/

        public ActionResult Index()
        {

            var viewModel = new Home();
            // The instantiation populates the backing fields in the Home view model and these are now available via the public properties
            return View(viewModel);
        }

    }
}


In your view, you can now reference the generic list of products and the generic list of news articles to display in your view:

@model BusinessLogic.ViewModels.Home
@{
    ViewBag.Title = "Index";
}
<h2>Index</h2>
@foreach (var p in Model.Articles)
{
    @p.Headline
}


@foreach (var p in Model.Products)
{
    @p.Name
}

Having been through the curve with learning MVC3 from Web Forms, I hope this helps someone. The examples above are quite simple, and I've avoided using dependency injection for simplification and hopeful ease of understanding view models.

Thursday, 22 March 2012

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 AdventureWorks sample database. In the query, SQL Server 2012 will return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time.



SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;