A workaround for a very annoying SQL Server 2005/8 parameter sniffing issue

If you do a search for “SQL Parameter Sniffing sp_executesql” one will find a very large collection of confused posts. The most common problems is that a query that runs fine in the query analyzer takes many orders of magnitude longer in code. Those who have dug deeper will find that if they rewrite the query to define variables and set them prior executing, the problem vanishes in code.

The reason for this problem goes to the heart of what a database does. Modern database systems based on SQL do not have the programmer specify the strategy for retrieving the data. Instead, the programmer simply specifies a query and the engine uses the statistics and indexes it has on hand to make a good guess as to the best approach. This analysis takes a bit of time (investigating the tables and the parameters to see if scanning a table may make more sense than an index scan, for example). Because of this, Microsoft SQL Server 2005 and 2008 can use a “cached query plan” instead of redoing this work again. In high transaction environments this makes a lot of sense as the overhead can impact the throughput of the entire system.

However, this same benefit can become a curse in some very complex edge cases. The situation I find it in is when there are complex views that use other views and do sub-queries as well. Combine this with extremely complex filter criteria and you have a recipe for a confused database plan. In our specific example, the plan took 90-120 seconds to process in code, while it only took 0-5 seconds in the query analyzer.

As we use LLBLGen, the solution was to create our own DataBaseAdapter for use in the query situations that caused this slowdown. The code is very much a hack, but a very useful hack as it restored the performance of the database to “good as new”. Here are the details (cross posted from my original post in the LLBLGen forums):

Step 1: Create your own subclass of the DataAccessAdapter:

Code:

public class YourDataAccessAdapter : DataAccessAdapter
{
}

Step 2: Create the utility functions RewriteQuery and ReplaceVars:

Code:

    private string RewriteQuery(IRetrievalQuery selectQuery)
    {
        StringBuilder declare = new StringBuilder();
        StringBuilder set = new StringBuilder();
        foreach (SqlParameter param in selectQuery.Parameters)
        {
            declare.AppendLine(String.Format("declare {0}_param {1}", param.ParameterName, param.SqlDbType));
            set.AppendLine(String.Format("set {0}_param = {0}", param.ParameterName));
        }
        return String.Format("{0}\r\n{1}\r\n{2}", declare, set, ReplaceVars(selectQuery.Command.CommandText));
    }
    private string ReplaceVars(string source)
    {
        Regex reg = new Regex(@"(@[\w|_]+)");
        return reg.Replace(source, "$1_param");
    }


Step 3: Implement overrides

Override the following events: OnFetchEntity, OnFetchEntityCollection, OnFetchTypedList, OnFetchTypedView, OnGetScalar. In each case they will look like:

Code:

    protected override void OnFetchEntityCollection(IRetrievalQuery selectQuery, IEntityCollection2 entityCollectionToFetch)
    {
        selectQuery.Command.CommandText = RewriteQuery(selectQuery);
        base.OnFetchEntityCollection(selectQuery, entityCollectionToFetch);
    }


There is an exception on OnGetScalar: selectQuery is called scalarQuery.
Once this is done, you can build your query as usual, just create your derived adapter where you have a problem query (I did not replace all queries, just problem ones in this way).

Code:

            using (YourDataAccessAdapter adapter = new YourDataAccessAdapter() )
            {
                adapter.FetchTypedView(...);
            }