Forums
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!