Correct now, optimize later. is one of the most important developer mantras and Scott K. followed it to a fault. He was on a team of programmers debugging a C# package management application, which used Microsoft SQL for revision tracking. Make sure it works right the first time; you can always tease out more performance after launch.

But if your program takes ten minutes to extract a C# package, as Scott discovered, you might want to optimize sooner rather than later.

Scott went looking for the usual performance bottlenecks and typical slip-ups -- a WHERE clause instead of an ON, the wrong kind of table UNION. Instead, he found this generated SQL monstrosity:

SELECT 
    [Extent5].[Name] AS [Name]
    FROM   (SELECT [Extent1].[Id] AS [Id1], [Extent1].[LineId] AS [LineId], [Extent2].[Id] AS [Id2], [Extent2].[PackageId] AS [PackageId], [Extent2].[FileId] AS [FileId], [Extent3].[Id] AS [Id3], [Extent3].[Path] AS [Path], [Extent3].[Hash] AS [Hash], [Extent4].[Id] AS [Id4], [Extent4].[VersionString] AS [VersionString], [Extent4].[UserInstalled] AS [UserInstalled], [Extent4].[InstallTime] AS [InstallTime], [Extent4].[ManifestId] AS [ManifestId]
        FROM    [Redacted1] AS [Extent1]
        INNER JOIN [Redacted2] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
        INNER JOIN [Redacted3] AS [Extent3] ON [Extent2].[FileId] = [Extent3].[Id]
        INNER JOIN [Redacted4] AS [Extent4] ON [Extent2].[PackageId] = [Extent4].[Id]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM  (SELECT 
                [UnionAll54].[C1] AS [C1]
                FROM  (SELECT 
                    [UnionAll53].[C1] AS [C1]
                    FROM  (SELECT 
                        [UnionAll52].[C1] AS [C1]
                        FROM  (SELECT 
                            [UnionAll51].[C1] AS [C1]
                            FROM  (SELECT 
                                [UnionAll50].[C1] AS [C1]
                                FROM  (SELECT 
                                    [UnionAll49].[C1] AS [C1]
                                    FROM  (SELECT 
                                        [UnionAll48].[C1] AS [C1]
                                        FROM  (SELECT 
                                            [UnionAll47].[C1] AS [C1]
                                            FROM  (SELECT 
                                                [UnionAll46].[C1] AS [C1]
                                                FROM  (SELECT 
                                                    [UnionAll45].[C1] AS [C1]
                                                    FROM  (SELECT 
                                                        [UnionAll44].[C1] AS [C1]
                                                        FROM  (SELECT 
                                                            [UnionAll43].[C1] AS [C1]
                                                            FROM  (SELECT 
                                                                [UnionAll42].[C1] AS [C1]
                                                                FROM  (SELECT 
                                                                    [UnionAll41].[C1] AS [C1]
                                                                    FROM  (SELECT 
                                                                        [UnionAll40].[C1] AS [C1]
                                                                        FROM  (SELECT 
                                                                            [UnionAll39].[C1] AS [C1]
                                                                            FROM  (SELECT 
                                                                                [UnionAll38].[C1] AS [C1]
                                                                                FROM  (SELECT 
                                                                                    [UnionAll37].[C1] AS [C1]
                                                                                    FROM  (SELECT 
                                                                                        [UnionAll36].[C1] AS [C1]
                                                                                        FROM  (SELECT 
                                                                                            [UnionAll35].[C1] AS [C1]
                                                                                            FROM  (SELECT 
                                                                                                [UnionAll34].[C1] AS [C1]
                                                                                                FROM  (SELECT 
                                                                                                    [UnionAll33].[C1] AS [C1]
                                                                                                    FROM  (SELECT 
                                                                                                        [UnionAll32].[C1] AS [C1]
                                                                                                        FROM  (SELECT 
                                                                                                            [UnionAll31].[C1] AS [C1]
                                                                                                            FROM  (SELECT 
                                                                                                                [UnionAll30].[C1] AS [C1]
                                                                                                                FROM  (SELECT 
                                                                                                                    [UnionAll29].[C1] AS [C1]
                                                                                                                    FROM  (SELECT 
                                                                                                                        [UnionAll28].[C1] AS [C1]
                                                                                                                        FROM  (SELECT 
                                                                                                                            [UnionAll27].[C1] AS [C1]
                                                                                                                            FROM  (SELECT 
                                                                                                                                [UnionAll26].[C1] AS [C1]
                                                                                                                                FROM  (SELECT 
                                                                                                                                    [UnionAll25].[C1] AS [C1]
                                                                                                                                    FROM  (SELECT 
                                                                                                                                        [UnionAll24].[C1] AS [C1]
                                                                                                                                        FROM  (SELECT 
                                                                                                                                            [UnionAll23].[C1] AS [C1]
                                                                                                                                            FROM  (SELECT 
                                                                                                                                                [UnionAll22].[C1] AS [C1]
                                                                                                                                                FROM  (SELECT 
                                                                                                                                                    [UnionAll21].[C1] AS [C1]
                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                        [UnionAll20].[C1] AS [C1]
                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                            [UnionAll19].[C1] AS [C1]
                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                [UnionAll18].[C1] AS [C1]
                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                    [UnionAll17].[C1] AS [C1]
                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                        [UnionAll16].[C1] AS [C1]
                                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                                            [UnionAll15].[C1] AS [C1]
                                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                                [UnionAll14].[C1] AS [C1]
                                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                                    [UnionAll13].[C1] AS [C1]
                                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                                        [UnionAll12].[C1] AS [C1]
                                                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                                                            [UnionAll11].[C1] AS [C1]
                                                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                                                [UnionAll10].[C1] AS [C1]
                                                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                                                    [UnionAll9].[C1] AS [C1]
                                                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                                                        [UnionAll8].[C1] AS [C1]
                                                                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                                                                            [UnionAll7].[C1] AS [C1]
                                                                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                                                                [UnionAll6].[C1] AS [C1]
                                                                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                                                                    [UnionAll5].[C1] AS [C1]
                                                                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                                                                        [UnionAll4].[C1] AS [C1]
                                                                                                                                                                                                                        FROM  (SELECT 
                                                                                                                                                                                                                            [UnionAll3].[C1] AS [C1]
                                                                                                                                                                                                                            FROM  (SELECT 
                                                                                                                                                                                                                                [UnionAll2].[C1] AS [C1]
                                                                                                                                                                                                                                FROM  (SELECT 
                                                                                                                                                                                                                                    [UnionAll1].[C1] AS [C1]
                                                                                                                                                                                                                                    FROM  (SELECT 
                                                                                                                                                                                                                                        N'22176' AS [C1]
                                                                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                                                                                                                                                                                                                                    UNION ALL
                                                                                                                                                                                                                                        SELECT 
                                                                                                                                                                                                                                        N'22177' AS [C1]
                                                                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
                                                                                                                                                                                                                                UNION ALL
                                                                                                                                                                                                                                    SELECT 
                                                                                                                                                                                                                                    N'22178' AS [C1]
                                                                                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
                                                                                                                                                                                                                            UNION ALL
                                                                                                                                                                                                                                SELECT 
                                                                                                                                                                                                                                N'22179' AS [C1]
                                                                                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
                                                                                                                                                                                                                        UNION ALL
                                                                                                                                                                                                                            SELECT 
                                                                                                                                                                                                                            N'22180' AS [C1]
                                                                                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll4]
                                                                                                                                                                                                                    UNION ALL
                                                                                                                                                                                                                        SELECT 
                                                                                                                                                                                                                        N'22181' AS [C1]
                                                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable6]) AS [UnionAll5]
                                                                                                                                                                                                                UNION ALL
                                                                                                                                                                                                                    SELECT 
                                                                                                                                                                                                                    N'22182' AS [C1]
                                                                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable7]) AS [UnionAll6]
                                                                                                                                                                                                            UNION ALL
                                                                                                                                                                                                                SELECT 
                                                                                                                                                                                                                N'22183' AS [C1]
                                                                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable8]) AS [UnionAll7]
                                                                                                                                                                                                        UNION ALL
                                                                                                                                                                                                            SELECT 
                                                                                                                                                                                                            N'22185' AS [C1]
                                                                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable9]) AS [UnionAll8]
                                                                                                                                                                                                    UNION ALL
                                                                                                                                                                                                        SELECT 
                                                                                                                                                                                                        N'22186' AS [C1]
                                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable10]) AS [UnionAll9]
                                                                                                                                                                                                UNION ALL
                                                                                                                                                                                                    SELECT 
                                                                                                                                                                                                    N'22187' AS [C1]
                                                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable11]) AS [UnionAll10]
                                                                                                                                                                                            UNION ALL
                                                                                                                                                                                                SELECT 
                                                                                                                                                                                                N'22188' AS [C1]
                                                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable12]) AS [UnionAll11]
                                                                                                                                                                                        UNION ALL
                                                                                                                                                                                            SELECT 
                                                                                                                                                                                            N'22189' AS [C1]
                                                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable13]) AS [UnionAll12]
                                                                                                                                                                                    UNION ALL
                                                                                                                                                                                        SELECT 
                                                                                                                                                                                        N'22191' AS [C1]
                                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable14]) AS [UnionAll13]
                                                                                                                                                                                UNION ALL
                                                                                                                                                                                    SELECT 
                                                                                                                                                                                    N'22192' AS [C1]
                                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable15]) AS [UnionAll14]
                                                                                                                                                                            UNION ALL
                                                                                                                                                                                SELECT 
                                                                                                                                                                                N'22193' AS [C1]
                                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable16]) AS [UnionAll15]
                                                                                                                                                                        UNION ALL
                                                                                                                                                                            SELECT 
                                                                                                                                                                            N'22195' AS [C1]
                                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable17]) AS [UnionAll16]
                                                                                                                                                                    UNION ALL
                                                                                                                                                                        SELECT 
                                                                                                                                                                        N'22196' AS [C1]
                                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable18]) AS [UnionAll17]
                                                                                                                                                                UNION ALL
                                                                                                                                                                    SELECT 
                                                                                                                                                                    N'22198' AS [C1]
                                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable19]) AS [UnionAll18]
                                                                                                                                                            UNION ALL
                                                                                                                                                                SELECT 
                                                                                                                                                                N'22199' AS [C1]
                                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable20]) AS [UnionAll19]
                                                                                                                                                        UNION ALL
                                                                                                                                                            SELECT 
                                                                                                                                                            N'22200' AS [C1]
                                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable21]) AS [UnionAll20]
                                                                                                                                                    UNION ALL
                                                                                                                                                        SELECT 
                                                                                                                                                        N'22201' AS [C1]
                                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable22]) AS [UnionAll21]
                                                                                                                                                UNION ALL
                                                                                                                                                    SELECT 
                                                                                                                                                    N'22202' AS [C1]
                                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable23]) AS [UnionAll22]
                                                                                                                                            UNION ALL
                                                                                                                                                SELECT 
                                                                                                                                                N'22204' AS [C1]
                                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable24]) AS [UnionAll23]
                                                                                                                                        UNION ALL
                                                                                                                                            SELECT 
                                                                                                                                            N'22205' AS [C1]
                                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable25]) AS [UnionAll24]
                                                                                                                                    UNION ALL
                                                                                                                                        SELECT 
                                                                                                                                        N'22207' AS [C1]
                                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable26]) AS [UnionAll25]
                                                                                                                                UNION ALL
                                                                                                                                    SELECT 
                                                                                                                                    N'22208' AS [C1]
                                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable27]) AS [UnionAll26]
                                                                                                                            UNION ALL
                                                                                                                                SELECT 
                                                                                                                                N'22209' AS [C1]
                                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable28]) AS [UnionAll27]
                                                                                                                        UNION ALL
                                                                                                                            SELECT 
                                                                                                                            N'22210' AS [C1]
                                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable29]) AS [UnionAll28]
                                                                                                                    UNION ALL
                                                                                                                        SELECT 
                                                                                                                        N'22211' AS [C1]
                                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable30]) AS [UnionAll29]
                                                                                                                UNION ALL
                                                                                                                    SELECT 
                                                                                                                    N'22212' AS [C1]
                                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable31]) AS [UnionAll30]
                                                                                                            UNION ALL
                                                                                                                SELECT 
                                                                                                                N'22213' AS [C1]
                                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable32]) AS [UnionAll31]
                                                                                                        UNION ALL
                                                                                                            SELECT 
                                                                                                            N'22214' AS [C1]
                                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable33]) AS [UnionAll32]
                                                                                                    UNION ALL
                                                                                                        SELECT 
                                                                                                        N'22215' AS [C1]
                                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable34]) AS [UnionAll33]
                                                                                                UNION ALL
                                                                                                    SELECT 
                                                                                                    N'22217' AS [C1]
                                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable35]) AS [UnionAll34]
                                                                                            UNION ALL
                                                                                                SELECT 
                                                                                                N'22219' AS [C1]
                                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable36]) AS [UnionAll35]
                                                                                        UNION ALL
                                                                                            SELECT 
                                                                                            N'22220' AS [C1]
                                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable37]) AS [UnionAll36]
                                                                                    UNION ALL
                                                                                        SELECT 
                                                                                        N'22221' AS [C1]
                                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable38]) AS [UnionAll37]
                                                                                UNION ALL
                                                                                    SELECT 
                                                                                    N'22222' AS [C1]
                                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable39]) AS [UnionAll38]
                                                                            UNION ALL
                                                                                SELECT 
                                                                                N'22226' AS [C1]
                                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable40]) AS [UnionAll39]
                                                                        UNION ALL
                                                                            SELECT 
                                                                            N'22227' AS [C1]
                                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable41]) AS [UnionAll40]
                                                                    UNION ALL
                                                                        SELECT 
                                                                        N'22228' AS [C1]
                                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable42]) AS [UnionAll41]
                                                                UNION ALL
                                                                    SELECT 
                                                                    N'22229' AS [C1]
                                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable43]) AS [UnionAll42]
                                                            UNION ALL
                                                                SELECT 
                                                                N'22230' AS [C1]
                                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable44]) AS [UnionAll43]
                                                        UNION ALL
                                                            SELECT 
                                                            N'22231' AS [C1]
                                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable45]) AS [UnionAll44]
                                                    UNION ALL
                                                        SELECT 
                                                        N'22232' AS [C1]
                                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable46]) AS [UnionAll45]
                                                UNION ALL
                                                    SELECT 
                                                    N'22233' AS [C1]
                                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable47]) AS [UnionAll46]
                                            UNION ALL
                                                SELECT 
                                                N'22234' AS [C1]
                                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable48]) AS [UnionAll47]
                                        UNION ALL
                                            SELECT 
                                            N'22235' AS [C1]
                                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable49]) AS [UnionAll48]
                                    UNION ALL
                                        SELECT 
                                        N'22236' AS [C1]
                                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable50]) AS [UnionAll49]
                                UNION ALL
                                    SELECT 
                                    N'22237' AS [C1]
                                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable51]) AS [UnionAll50]
                            UNION ALL
                                SELECT 
                                N'22238' AS [C1]
                                FROM  ( SELECT 1 AS X ) AS [SingleRowTable52]) AS [UnionAll51]
                        UNION ALL
                            SELECT 
                            N'22239' AS [C1]
                            FROM  ( SELECT 1 AS X ) AS [SingleRowTable53]) AS [UnionAll52]
                    UNION ALL
                        SELECT 
                        N'22240' AS [C1]
                        FROM  ( SELECT 1 AS X ) AS [SingleRowTable54]) AS [UnionAll53]
                UNION ALL
                    SELECT 
                    N'22244' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable55]) AS [UnionAll54]
            UNION ALL
                SELECT 
                N'22245' AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable56]) AS [UnionAll55]
            WHERE [UnionAll55].[C1] = [Extent1].[LineId]
        ) ) AS [Filter2]
    LEFT OUTER JOIN [Redacted5] AS [Extent5] ON [Filter2].[ManifestId] = [Extent5].[Id]
    WHERE [Filter2].[Path] = @p__linq__0

What could possibly have generated that? he wondered. A bit of hunting brought him to this innocuous C# method:

  public IList<string> CheckForConflicts(Context ctx)
  {
   var conflictsWith = Actions.Select(a => a.LineId);

   return ctx.DbContext.Redacted1
    .Where(a => a.File.Path == Filename && conflictsWith.Any(b => b == a.LineId))
    .Select(a => a.Package.Manifest.Name)
    .ToList();
  }

That .Any() call shouldn't have caused all that, he thought. But that poor method seemed a bit out of place. He changed conflictsWith.Any(b => b == a.LineId) to conflictsWith.Contains(a.LineId) and checked the generated SQL.

SELECT 
    [Extent5].[Name] AS [Name]
    FROM   (SELECT [Extent1].[Id] AS [Id1], [Extent1].[LineId] AS [LineId], [Extent2].[Id] AS [Id2], [Extent2].[PackageId] AS [PackageId], [Extent2].[FileId] AS [FileId], [Extent3].[Id] AS [Id3], [Extent3].[Path] AS [Path], [Extent3].[Hash] AS [Hash], [Extent4].[Id] AS [Id4], [Extent4].[VersionString] AS [VersionString], [Extent4].[UserInstalled] AS [UserInstalled], [Extent4].[InstallTime] AS [InstallTime], [Extent4].[ManifestId] AS [ManifestId]
        FROM    [Redacted1] AS [Extent1]
        INNER JOIN [Redacted2] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
        INNER JOIN [Redacted3] AS [Extent3] ON [Extent2].[FileId] = [Extent3].[Id]
        INNER JOIN [Redacted4] AS [Extent4] ON [Extent2].[PackageId] = [Extent4].[Id]
        WHERE [Extent1].[LineId] IN (N'22176', N'22177', N'22178', N'22179', N'22180', N'22181', N'22182', N'22183', N'22185', N'22186', N'22187', N'22188', N'22189', N'22191', N'22192', N'22193', N'22195', N'22196', N'22198', N'22199', N'22200', N'22201', N'22202', N'22204', N'22205', N'22207', N'22208', N'22209', N'22210', N'22211', N'22212', N'22213', N'22214', N'22215', N'22217', N'22219', N'22220', N'22221', N'22222', N'22226', N'22227', N'22228', N'22229', N'22230', N'22231', N'22232', N'22233', N'22234', N'22235', N'22236', N'22237', N'22238', N'22239', N'22240', N'22244', N'22245') ) AS [Filter1]
    LEFT OUTER JOIN [Redacted5] AS [Extent5] ON [Filter1].[ManifestId] = [Extent5].[Id]
    WHERE [Filter1].[Path] = @p__linq__0

Still not pretty, but the execution time shrank from 10 minutes to 10 seconds.

Among the other mantras Scott followed, he added one more. Never confuse CONTAINS with ANY.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!