Get variables used in parameterized query

1

I’m using this code to print the sql timings in a console window:

    private static Dictionary<string, List<CustomTiming>> GetSqlTimings(MiniProfiler mp)
    {
        var dictionary = new Dictionary<string, List<CustomTiming>>();
        foreach (var timing in mp.GetTimingHierarchy())
        {
            if (timing.HasCustomTimings == false) continue;

            foreach (var customTiming in timing.CustomTimings["sql"])
            {
                List<CustomTiming> list;
                if (dictionary.TryGetValue(customTiming.CommandString, out list) == false)
                {
                    list = new List<CustomTiming>();
                    dictionary.Add(customTiming.CommandString, list);
                }

                list.Add(customTiming);
            }
        }

        return dictionary;
    }

and

        var sqlTimings = GetSqlTimings(MiniProfiler.Current);
        if (sqlTimings.Any(x => x.Value.Count > 1))
        {
            foreach (var commandString in sqlTimings.Keys.OrderBy(x => sqlTimings[x].Sum(t => t.DurationMilliseconds)))
            {
                var count = sqlTimings[commandString].Count;
                if (count > 1)
                {
                    Console.WriteLine("DUPLICATES");
                    var sum = sqlTimings[commandString].Sum(t => t.DurationMilliseconds);
                    Console.WriteLine(new string('*', 40));
                    Console.WriteLine("{0}ms for {1} query duplicates\n{2}", sum, count, commandString);
                }
            }
        }

This works fine, I’m able to see which queries are duplicated. However, I can’t seem to find the varying query parameters. Is there a way to access those parameters? It would be helpful to be able to see how they vary in order to understand which part of the linq statements that are turned into duplicates.

Thanks!

2

I figured out a solution with some trickery:

    MiniProfiler.Settings.SqlFormatter = new VerboseSqlServerFormatter();

    private static Dictionary<string, List<CustomTiming>> GetSqlTimings(MiniProfiler mp)
    {
        var dictionary = new Dictionary<string, List<CustomTiming>>();
        foreach (var timing in mp.GetTimingHierarchy())
        {
            if (timing.HasCustomTimings == false) continue;

            foreach (var customTiming in timing.CustomTimings["sql"])
            {
                var canonicalCommandString = Regex.Replace(
                    customTiming.CommandString,
                    "^DECLARE.*$",
                    string.Empty,
                    RegexOptions.Multiline);
                List<CustomTiming> list;
                if (dictionary.TryGetValue(canonicalCommandString, out list) == false)
                {
                    list = new List<CustomTiming>();
                    dictionary.Add(canonicalCommandString, list);
                }

                list.Add(customTiming);
            }
        }

        return dictionary;
    }

and then

        var timings = GetSqlTimings(MiniProfiler.Current);
        foreach (var commandString in timings.Keys.OrderBy(x => timings[x].Sum(t => t.DurationMilliseconds)))
        {
            var customTimings = timings[commandString];
            var count = customTimings.Count;
            if (count > 1)
            {
                var totalMilliseconds = timings[commandString].Sum(t => t.DurationMilliseconds);
                Console.WriteLine("DUPLICATES: {0} in {1}ms", count, totalMilliseconds);
                foreach (var customTiming in customTimings)
                {
                    Console.WriteLine(new string('*', 40));
                    Console.WriteLine(customTiming.CommandString);
                }
            }
            else
            {
                Console.WriteLine("NO DUPLICATES");
                var sum = timings[commandString].Sum(t => t.DurationMilliseconds);
                Console.WriteLine(new string('*', 40));
                Console.WriteLine("{0}ms\n{1}", sum, commandString);
            }
        }