Sitecore

Translate Sitecore fast queries to SQL

Tobias Studer

Tobias Studer

— 2 min read

Last week I tried to figure out how the SQL queries look like that get generated from your Sitecore fast queries. Either I need to improve my Google skills or there is just not a lot of information out there. So I decided to takle the problem myself.

A german version of this blog post can be found here.

As it turns out, the fast queries get translated within the main data provider. So I created a new data provider which inherits from the main data provider. With a little help of my favorite decompiler, I've overwritten the QueryFast(...) method without changing the default behavior, but with some additional log output.

Disclaimer: I'm not affiliated with JetBrains, just really loving their work.

protected override IDList QueryFast(string query, CallContext context)
{
    var baseIdList = this.SelectIDs(query, context);
    if (baseIdList != null && baseIdList.Count > 0) return baseIdList;

    var parameters = new ParametersList();
    var sql = this.Translator.TranslateQuery(query, context, parameters);

    Log.Debug(string.Format("FastQuery: {0}", query), this);
    Log.Debug(string.Format("SQL Query: {0}", sql), this);

    if (sql == null) return null;

    var stopwatch = Stopwatch.StartNew();
    using (var reader = this.Api.CreateReader(sql, parameters.ToArray()))
    {
        var idList = new IDList();
        while (reader.Read())
        {
            idList.Add(this.Api.GetId(0, reader));
        }

        context.CurrentResult = idList;
    }

    Log.Debug(string.Format("Query Time: {0}ms", stopwatch.ElapsedMilliseconds), this);
    return null;
}

This is already nice, but the SQL query still had all its parameters instead of the actual values in it. As I wanted to copy and paste the query and run it directly against the database without replacing the parameters with the values manually, I had to write an additional helper method.

private string FormatSqlQuery(string sql, ParametersList parameterList)
{
    var parameters = parameterList.ToArray();
    for (var i = 0; i < parameters.Length; i = i + 2)
    {
        var parameterName = string.Format("@{0}", parameters[i]);
        var parameterValue = string.Format("'{0}'", parameters[i + 1]);

        sql = sql.Replace(parameterName, parameterValue);
    }

    return sql;
}

At this point I realized, that we use a lot of fast queries in some places and it was really hard to find the one that I'm interested in. So I added a list of keyword, that must be in the original fast query and only output the addional information when the criteria is met.

private bool IsTraceEnabled(string query)
{
    return this.Filters.Count == 0 || this.Filters.Any(query.Contains);
}

The configuration of the data provider is done via an include-file and patches the main data provider. Also the possiblity to define filters can be done through the configuration file.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" >
    <sitecore>
    <dataProviders>
        <main  type="Loquacious.DataProvider.LoquaciousDataProvider, Loquacious.DataProvider" patch:instead="main">
        <param connectionStringName="$(1)" />
        <Name>$(1)</Name>
        <!--
        <filters hint="list">
            <filter>{E248FD78-B482-49FD-9151-F0A926448576}</filter>
        </filters>
        -->
        </main>
    </dataProviders>
    </sitecore>
</configuration>

I tried to leverage the Sitecore functionality by adding the filters with the hint="list" attribute. A filter can be any string that should be in the fast query, even a Sitecore ID. Be aware to either add a list with at least one entry or don't add the list at all. Otherwise, Sitecore will throw an exception.

To make it really easy to get started with this very loquacious data provider, I created a NuGet package. Just install the package and you're ready to go. In the package is a version for .NET 4.0 compiled against Sitecore 6 and a version for .NET 4.5 compiled against Sitecore 7.

The full source code and more documentation can be found on GitHub.

Any suggestions, improvements or questions are very welcome.

Read this blog post by @retohugi about a very nice addition to see real-time log output http://sitecore.unic.com/de/2013/12/11/Log2Console (only available in german)

Read more