Increasing performance with Dapper

I recently wrote an application where users had a need for some very specific searches. We decided to implement these searches as a query to the database. As we were using Entity Framework we first wrote our search implementation something like this:

    repository.Stuff.Where(c => c.SomeStuff.ToLower().Contains(query)) || 
    c.SomeOtherStuff != null && c.SomeOtherStuff.Things.ToLower().Contains(query))

We had about 20ish different properties that we needed to null check and make a case insensitive search for. A couple of these properties belonged to entities in different tables than our imaginary Stuff table above and had "one to one" or "many to many" relationships.

The generated sql looked like something out of Edgar Allan Poes worst nightmares:

SELECT
    [UnionAll1].[Id] AS [C1],
    [UnionAll1].[Id1] AS [C2],
    [UnionAll1].[Id2] AS [C3],
    [UnionAll1].[Stuff] AS [C4],
    [UnionAll1].[Stuff2] AS [C5],
    [UnionAll1].[Stuff3] AS [C6],
    [UnionAll1].[Some_Id] AS [C7],
    [UnionAll1].[SomeOtherStuff] AS [C8],
    [UnionAll1].[Stuff666] AS [C9],
    [UnionAll1].[MoreStuff] AS [C10],
    [UnionAll1].[Things] AS [C11],
    [UnionAll1].[OtherThing] AS [C12],
    [UnionAll1].[Something] AS [C13],
    [UnionAll1].[PhoneNumberOrSomething] AS [C14],
    [UnionAll1].[AnotherThing] AS [C15],
    [UnionAll1].[MoreStuff] AS [C16],
    [UnionAll1].[AndMore] AS [C17],
    [UnionAll1].[MoreThings] AS [C18],
    [UnionAll1].[AndMoreMore] AS [C19],
    [UnionAll1].[Stuff4] AS [C20],
    [UnionAll1].[AndEvenMoreStuff] AS [C21],
    [UnionAll1].[CoolStuff] AS [C22],
    [UnionAll1].[SomeDate] AS [C23],
    [UnionAll1].[Stuff5] AS [C24],
    [UnionAll1].[Stuff6] AS [C25],
    [UnionAll1].[Stuff7] AS [C26],
    [UnionAll1].[AndSomeUserName] AS [C27],
    [UnionAll1].[BoolStuff] AS [C28],
    [UnionAll1].[BitStuff] AS [C29],
    [UnionAll1].[MoreThings] AS [C30],
    [UnionAll1].[Id3] AS [C31],
    [UnionAll1].[EvenMoreStuff] AS [C32],
    [UnionAll1].[AndMoreStuff] AS [C33],
    [UnionAll1].[Stuff8] AS [C34],
    [UnionAll1].[Stuff9] AS [C35],
    [UnionAll1].[IActuallyCantMakeUpMoreColumnNames] AS [C36],
    [UnionAll1].[Please] AS [C37],
    [UnionAll1].[No] AS [C38],
    [UnionAll1].[Moooore] AS [C39],
    [UnionAll1].[C1] AS [C40],
    [UnionAll1].[Id4] AS [C41],
    [UnionAll1].[ThisIsInsane] AS [C42],
    [UnionAll1].[StuffAgainAndAgain] AS [C43],
    [UnionAll1].[ICantTakeIt] AS [C44],
    [UnionAll1].[MakeItStop] AS [C45],
    [UnionAll1].[Almost] AS [C46],
    [UnionAll1].[JustAFewMore] AS [C47],
    [UnionAll1].[ComeOn] AS [C48],
    [UnionAll1].[AlmostThere] AS [C49],
    [UnionAll1].[Finally] AS [C50],
    [UnionAll1].[C2] AS [C51],
    [UnionAll1].[C3] AS [C52],
    [UnionAll1].[C4] AS [C53],
    [UnionAll1].[C5] AS [C54],
    [UnionAll1].[C6] AS [C55],
    [UnionAll1].[C7] AS [C56]
    FROM  (SELECT
        CASE WHEN ([Join2].[Some_Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
        [Extent2].[Id] AS [Id],
        [Extent1].[Id] AS [Id1],
        [Extent1].[Id] AS [Id2],
        [Extent1].[Stuff] AS [Stuff],
        [Extent1].[Stuff2] AS [Stuff2],
        [Extent1].[Stuff3] AS [Stuff3],
        [Extent1].[Person_Id] AS [Person_Id],
        [Extent1].[SomeOtherStuff] AS [SomeOtherStuff],
        [Extent1].[Stuff666] AS [Stuff666],
        [Extent1].[MoreStuff] AS [MoreStuff],
        [Extent1].[AndMore] AS [AndMore],
        [Extent1].[MoreThings] AS [MoreThings],
        [Extent1].[AndMoreMore] AS [AndMoreMore],
        [Extent1].[PhoneNumberOrSomething] AS [PhoneNumberOrSomething],
        [Extent1].[AnotherThing] AS [AnotherThing],
        [Extent1].[Things] AS [Things],
        [Extent1].[OtherThing] AS [OtherThing],
        [Extent1].[Stuff4] AS [Stuff4],
        [Extent1].[Stuff5] AS [Stuff5],
        [Extent1].[AndEvenMoreStuff] AS [AndEvenMoreStuff],
        [Extent1].[CoolStuff] AS [CoolStuff],
        [Extent1].[SomeDate] AS [SomeDate],
        [Extent1].[Stuff6] AS [Stuff6],
        [Extent1].[Stuff7] AS [Stuff7],
        [Extent1].[AndSomeUserName] AS [AndSomeUserName],
        [Extent1].[BitStuff] AS [BitStuff],
        [Extent1].[BoolStuff] AS [BoolStuff],
        [Extent1].[MoreThings] AS [MoreThings],
        [Extent1].[EvenMoreStuff] AS [EvenMoreStuff],
        [Extent1].[AndEvenMoreStuff] AS [AndEvenMoreStuff],
        [Extent2].[Id] AS [Id3],
        [Extent2].[MoreMoreMore] AS [MoreMoreMore],
        [Extent2].[Stuff8] AS [Stuff8],
        [Extent2].[Stuff9] AS [Stuff9],
        [Extent2].[IActuallyCantMakeUpMoreColumnNames] AS [IActuallyCantMakeUpMoreColumnNames],
        [Extent2].[Please] AS [Please],
        [Extent2].[No] AS [No],
        [Extent2].[Moooore] AS [Moooore],
        [Extent2].[ThisIsInsane] AS [ThisIsInsane],
        [Join2].[Id] AS [Id4],
        [Join2].[StuffAgainAndAgain] AS [StuffAgainAndAgain],
        [Join2].[ICantTakeIt] AS [ICantTakeIt],
        [Join2].[MakeItStop] AS [MakeItStop],
        [Join2].[Almost] AS [Almost],
        [Join2].[JustAFewMore] AS [JustAFewMore],
        [Join2].[ComeOn] AS [ComeOn],
        [Join2].[AlmostThere] AS [AlmostThere],
        [Join2].[Almoooost] AS [Almooost],
        [Join2].[Finally] AS [Finally],
        CAST(NULL AS int) AS [C2],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS int) AS [C4],
        CAST(NULL AS varchar(1)) AS [C5],
        CAST(NULL AS datetime2) AS [C6],
        CAST(NULL AS int) AS [C7]
        FROM   [dbo].[StuffTable] AS [Extent1]
        INNER JOIN [dbo].[TableOfOtherStuff] AS [Extent2] ON [Extent1].[Some_Id] = [Extent2].[Id]
        LEFT OUTER JOIN  (SELECT [Extent3].[Other_Id] AS [Other_Id], [Extent3].[Stuff_Id] AS [Stuff_Id], [Extent4].[Id] AS [Id], [Extent4].[Stuff] AS [Stuff], [Extent4].[Morestuff] AS [MoreStuff], [Extent4].[AndMore] AS [AndMore], [Extent4].[EvenMore] AS [EvenMore], [Extent4].[MoreMoreMore] AS [MoreMoreMore], [Extent4].[MoreMoreMore] AS [MoreMoreMore], [Extent4].[ItNeverEnds] AS [ItNeverEnds], [Extent4].[ComeOnPlease] AS [ComeOnPlease], [Extent4].[Argh] AS [Argh]
            FROM  [dbo].[SomeOtherTable] AS [Extent3]
            INNER JOIN [dbo].[AndAnotherTableOfStuff] AS [Extent4] ON [Extent3].[Some_Id] = [Extent4].[Id] ) AS [Join2] ON [Extent1].[Id] = [Join2].[Some_Id]
        WHERE (([Extent1].[Stuff] IS NOT NULL) AND (LOWER([Extent1].[Stuff]) LIKE N'%rh%')) OR (([Extent1].[Stuff2] IS NOT NULL) AND (LOWER([Extent1].[Stuff2]) LIKE N'%rh%')) OR (([Extent1].[MoreStuff] IS NOT NULL) AND (LOWER([Extent1].[MoreStuff]) LIKE N'%rh%')) OR (([Extent1].[EvenMoreStuff] IS NOT NULL) AND ([Extent1].[EvenMoreStuff] LIKE N'%rh%')) OR (([Extent2].[Please] IS NOT NULL) AND (LOWER([Extent2].[Please]) LIKE N'%rh%')) OR (([Extent2].[MakeItStop] IS NOT NULL) AND (LOWER([Extent2].[MakeItStop]) LIKE N'%rh%')) OR (([Extent2].[Finally] IS NOT NULL) AND (LOWER([Extent2].[Finally]) LIKE N'%rh%'))
    UNION ALL
        SELECT
        2 AS [C1],
        [Extent6].[Id] AS [Id],
        [Extent5].[Id] AS [Id1],
        [Extent5].[Id] AS [Id2],
        [Extent5].[Stuff] AS [Stuff],
        [Extent5].[MoreStuff] AS [MoreStuff],
        [Extent5].[Stuff2] AS [Stuff2],
        [Extent5].[Some_Id] AS [Some_Id],
        [Extent5].[Stuff3] AS [Stuff3],
        [Extent5].[EvenMoreStuff] AS [EvenMoreStuff],
        [Extent5].[SomeOtherStuff] AS [SomeOtherStuff],
        [Extent5].[Stuff666] AS [Stuff666],
        [Extent5].[AndMore] AS [AndMore],
        [Extent5].[MoreThings] AS [MoreThings],
        [Extent5].[Thingy] AS [Thingy],
        [Extent5].[OtherThing] AS [OtherThing],
        [Extent5].[AndSomeUserName] AS [AndSomeUserName],
        [Extent5].[PhoneNumberOrSomething] AS [PhoneNumberOrSomething],
        [Extent5].[ItNeverEnds] AS [ItNeverEnds],
        [Extent5].[ItGoesOnAndOn] AS [ItGoesOnAndOn],
        [Extent5].[AndOnAndOn] AS [AndOnAndOn],
        [Extent5].[Please] AS [Please],
        [Extent5].[AnotherColumn] AS [AnotherColumn],
        [Extent5].[IActuallyCantMakeUpMoreColumnNames] AS [IActuallyCantMakeUpMoreColumnNames],
        [Extent5].[Stuff4] AS [Stuff4],
        [Extent5].[Stuff5] AS [Stuff5],
        [Extent5].[BitStuff] AS [BitStuff],
        [Extent5].[BoolStuff] AS [BoolStuff],
        [Extent5].[CoolStuff] AS [CoolStuff],
        [Extent5].[SomeDate] AS [SomeDate],
        [Extent5].[StuffAgainAndAgain] AS [StuffAgainAndAgain],
        [Extent6].[Id] AS [Id3],
        [Extent6].[ICantTakeIt] AS [ICantTakeIt],
        [Extent6].[MakeItStop] AS [MakeItStop],
        [Extent6].[AlmostThere] AS [AlmostThere],
        [Extent6].[JustAFewMore] AS [JustAFewMore],
        [Extent6].[ComeOn] AS [ComeOn],
        [Extent6].[Almost] AS [Almost],
        [Extent6].[Almoooost] AS [Almoooost],
        [Extent6].[Finally] AS [Finally],
        CAST(NULL AS int) AS [C2],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        CAST(NULL AS varchar(1)) AS [C5],
        CAST(NULL AS varchar(1)) AS [C6],
        CAST(NULL AS varchar(1)) AS [C7],
        CAST(NULL AS varchar(1)) AS [C8],
        CAST(NULL AS varchar(1)) AS [C9],
        CAST(NULL AS bit) AS [C10],
        CAST(NULL AS bit) AS [C11],
        [Extent7].[Id] AS [Id4],
        [Extent7].[Url] AS [Url],
        [Extent7].[SomeThing] AS [SomeThing],
        [Extent7].[MoreStuff] AS [MoreStuff],
        [Extent7].[Crazy] AS [Crazy],
        [Extent7].[StuffId] AS [StuffId]
        FROM   [dbo].[Thingies] AS [Extent5]
        INNER JOIN [dbo].[SomeOtherTable] AS [Extent6] ON [Extent5].[Some_Id] = [Extent6].[Id]
        INNER JOIN [dbo].[AndAnotherTable] AS [Extent7] ON [Extent5].[Id] = [Extent7].[StuffId]
        WHERE (([Extent5].[Stuff] IS NOT NULL) AND (LOWER([Extent5].[Stuff]) LIKE N'%rh%')) OR (([Extent5].[Stuff2] IS NOT NULL) AND (LOWER([Extent5].[Stuff2]) LIKE N'%rh%')) OR (([Extent5].[SomeOtherStuff] IS NOT NULL) AND (LOWER([Extent5].[SomeOtherStuff]) LIKE N'%rh%')) OR (([Extent5].[EvenMoreStuff] IS NOT NULL) AND ([Extent5].[EvenMoreStuff] LIKE N'%rh%')) OR (([Extent6].[Please] IS NOT NULL) AND (LOWER([Extent6].[Please]) LIKE N'%rh%')) OR (([Extent6].[NoMore] IS NOT NULL) AND (LOWER([Extent6].[NoMore]) LIKE N'%rh%')) OR (([Extent6].[Finally] IS NOT NULL) AND (LOWER([Extent6].[Finally]) LIKE N'%rh%'))) AS [UnionAll1]
    ORDER BY [UnionAll1].[Id] ASC, [UnionAll1].[Id2] ASC, [UnionAll1].[Id3] ASC, [UnionAll1].[C1] ASC

This literally hurts to look at... and needless to say resulted in abysmal performance.

The query in itself took about 10-15 seconds to execute over a dataset of about 15 000 records... It was time to look at the alternatives.

We immediately gave up on Entity Framework for these queries. Now, don't get me wrong, I really like Entity Framework, the problem here isn't the framework in itself it's the ungrateful queries we give it to process. As Entity Framework is a "one shoe fits all"-solution that is a great tool in a wide spectrum of use cases it simply does not perform well with specialized queries such as these. You could of course execute plain sql queries through the DbContext, but this still comes with considerable overhead and the mapping to objects is not as streamlined as one could wish.

Enter dapper

Dapper is a "simple object mapper for .Net" that simply extends the IDbConnection interface and provides helper methods to execute a query or command and map the results to simple POCO .Net objects. It is extremely performant and light weight.

Our implementation with dapper took our initial query and made it look something like this:

SELECT TOP 10
StuffTable.Id,
Stuff collate sql_latin1_general_cp1_ci_as AS Stuff,
StuffTable.StuffNumber collate sql_latin1_general_cp1_ci_as AS StuffNumber,
MoreStuff collate sql_latin1_general_cp1_ci_as AS MoreStuff,
Stuff666 collate sql_latin1_general_cp1_ci_as AS Stuff666,
ThingsTable.ThingsNumber collate sql_latin1_general_cp1_ci_as AS ThingsNumber,
ThingsTable.Thingy collate sql_latin1_general_cp1_ci_as AS Thingy,
ThingsTable.OtherThing collate sql_latin1_general_cp1_ci_as AS OtherThing,
OtherTable.OtherStuff collate sql_latin1_general_cp1_ci_as AS OtherStuff,
OtherTable.EvenMoreStuff collate sql_latin1_general_cp1_ci_as  AS EvenMoreStuff,
LEFT(Thingy, 1) + ' ' + LEFT(OtherThing, 1) AS WeirdStuff
FROM dbo.StuffTable
LEFT JOIN dbo.ThingsTable ON ThingsTable.Id = Cases.Thing_Id
LEFT JOIN dbo.StuffTableToOtherTable ON StuffTableToOtherTable.Stuff_Id = StuffTable.Id
LEFT JOIN dbo.OtherTable ON OtherTable.Id = StuffTableToOtherTable.Other_Id
 
WHERE
LEFT(Thingy, 1) + ' ' + LEFT(OtherThing, 1) = 'r l' OR
Stuff LIKE '%r l%' OR
StuffNumber LIKE '%r l%' OR
MoreStuff LIKE 'r l%' OR
Stuff666 LIKE 'r l%' OR
ThingsNumber LIKE '%r l%' OR
Thingy LIKE 'r l%' OR
OtherThing LIKE 'r l%' OR
Thingy + ' ' + OtherThing LIKE 'r l%' OR
OtherStuff LIKE 'r l%' OR
EvenMoreStuff LIKE '%r l%'

Yeah, it still isn't very pretty, but lets face it; A complicated SQL query will never be more than half readable...

To execute the query with dapper we simply do something like this:

 var query = //our crazy query from above, omitted for brevity
 var dbConnection = //our database connection of type IDbConnection
 
 var res = dbConnection.Query<AwesomeResult>(query);

We execute the result on our database connection and dapper maps it automatically to our AwesomeResult class.

With this approach our once monstrously slow query took about 14ms to execute.