Skip to content

Instantly share code, notes, and snippets.

@jeffhandley
Created August 23, 2012 21:37
Show Gist options
  • Save jeffhandley/3442185 to your computer and use it in GitHub Desktop.
Save jeffhandley/3442185 to your computer and use it in GitHub Desktop.
OData / EF vs. Rewritten queries
SELECT TOP (30) 
[Project1].[PackageRegistrationKey] AS [PackageRegistrationKey], 
[Project1].[Id] AS [Id], 
[Project1].[Version] AS [Version], 
[Project1].[FlattenedAuthors] AS [FlattenedAuthors], 
[Project1].[Copyright] AS [Copyright], 
[Project1].[Created] AS [Created], 
[Project1].[FlattenedDependencies] AS [FlattenedDependencies], 
[Project1].[Description] AS [Description], 
[Project1].[DownloadCount1] AS [DownloadCount], 
[Project1].[ExternalPackageUrl] AS [ExternalPackageUrl], 
[Project1].[C1] AS [C1], 
[Project1].[IconUrl] AS [IconUrl], 
[Project1].[IsLatestStable] AS [IsLatestStable], 
[Project1].[Language] AS [Language], 
[Project1].[LastUpdated] AS [LastUpdated], 
[Project1].[LicenseUrl] AS [LicenseUrl], 
[Project1].[Hash] AS [Hash], 
[Project1].[HashAlgorithm] AS [HashAlgorithm], 
[Project1].[PackageFileSize] AS [PackageFileSize], 
[Project1].[ProjectUrl] AS [ProjectUrl], 
[Project1].[C2] AS [C2], 
[Project1].[ReleaseNotes] AS [ReleaseNotes], 
[Project1].[C3] AS [C3], 
[Project1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], 
[Project1].[Summary] AS [Summary], 
[Project1].[C4] AS [C4], 
[Project1].[C5] AS [C5], 
[Project1].[DownloadCount] AS [DownloadCount1], 
[Project1].[C6] AS [C6]
FROM ( SELECT [Project1].[PackageRegistrationKey] AS [PackageRegistrationKey], [Project1].[Copyright] AS [Copyright], [Project1].[Created] AS [Created], [Project1].[Description] AS [Description], [Project1].[ReleaseNotes] AS [ReleaseNotes], [Project1].[DownloadCount] AS [DownloadCount], [Project1].[ExternalPackageUrl] AS [ExternalPackageUrl], [Project1].[HashAlgorithm] AS [HashAlgorithm], [Project1].[Hash] AS [Hash], [Project1].[IconUrl] AS [IconUrl], [Project1].[IsLatestStable] AS [IsLatestStable], [Project1].[LastUpdated] AS [LastUpdated], [Project1].[LicenseUrl] AS [LicenseUrl], [Project1].[Language] AS [Language], [Project1].[PackageFileSize] AS [PackageFileSize], [Project1].[ProjectUrl] AS [ProjectUrl], [Project1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], [Project1].[Summary] AS [Summary], [Project1].[Version] AS [Version], [Project1].[FlattenedAuthors] AS [FlattenedAuthors], [Project1].[FlattenedDependencies] AS [FlattenedDependencies], [Project1].[Id] AS [Id], [Project1].[DownloadCount1] AS [DownloadCount1], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[C4] AS [C4], [Project1].[C5] AS [C5], [Project1].[C6] AS [C6], row_number() OVER (ORDER BY [Project1].[DownloadCount1] DESC, [Project1].[Id] ASC) AS [row_number]
  FROM ( SELECT 
    [Extent1].[PackageRegistrationKey] AS [PackageRegistrationKey], 
    [Extent1].[Copyright] AS [Copyright], 
    [Extent1].[Created] AS [Created], 
    [Extent1].[Description] AS [Description], 
    [Extent1].[ReleaseNotes] AS [ReleaseNotes], 
    [Extent1].[DownloadCount] AS [DownloadCount], 
    [Extent1].[ExternalPackageUrl] AS [ExternalPackageUrl], 
    [Extent1].[HashAlgorithm] AS [HashAlgorithm], 
    [Extent1].[Hash] AS [Hash], 
    [Extent1].[IconUrl] AS [IconUrl], 
    [Extent1].[IsLatestStable] AS [IsLatestStable], 
    [Extent1].[LastUpdated] AS [LastUpdated], 
    [Extent1].[LicenseUrl] AS [LicenseUrl], 
    [Extent1].[Language] AS [Language], 
    [Extent1].[PackageFileSize] AS [PackageFileSize], 
    [Extent1].[ProjectUrl] AS [ProjectUrl], 
    [Extent1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], 
    [Extent1].[Summary] AS [Summary], 
    [Extent1].[Version] AS [Version], 
    [Extent1].[FlattenedAuthors] AS [FlattenedAuthors], 
    [Extent1].[FlattenedDependencies] AS [FlattenedDependencies], 
    [Extent2].[Id] AS [Id], 
    [Extent3].[DownloadCount] AS [DownloadCount1], 
    N'packages/' + [Extent3].[Id] + N'/' + [Extent1].[Version] AS [C1], 
     CAST( CASE WHEN ([Extent1].[Listed] = 1) THEN [Extent1].[Published] ELSE NULL END AS datetime2) AS [C2], 
    N'package/ReportAbuse/' + [Extent3].[Id] + N'/' + [Extent1].[Version] AS [C3], 
    CASE WHEN ([Extent1].[Tags] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE N' ' + LTRIM(RTRIM([Extent1].[Tags])) + N' ' END AS [C4], 
    CASE WHEN ([Extent1].[Title] IS NULL) THEN [Extent3].[Id] ELSE [Extent1].[Title] END AS [C5], 
    cast(0 as float(53)) AS [C6]
    FROM [dbo].[Packages] AS [Extent1]
    INNER JOIN [dbo].[PackageRegistrations] AS [Extent2] ON [Extent1].[PackageRegistrationKey] = [Extent2].[Key]
    LEFT OUTER JOIN [dbo].[PackageRegistrations] AS [Extent3] ON [Extent1].[PackageRegistrationKey] = [Extent3].[Key]
    WHERE [Extent1].[IsPrerelease] <> cast(1 as bit)
  ) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 30
ORDER BY [Project1].[DownloadCount1] DESC, [Project1].[Id] ASC
SELECT TOP (30) 
[Project1].[PackageRegistrationKey] AS [PackageRegistrationKey], 
[Project1].[Id] AS [Id], 
[Project1].[Version] AS [Version], 
[Project1].[FlattenedAuthors] AS [FlattenedAuthors], 
[Project1].[Created] AS [Created], 
[Project1].[FlattenedDependencies] AS [FlattenedDependencies], 
[Project1].[Description] AS [Description], 
[Project1].[DownloadCount1] AS [DownloadCount], 
[Project1].[IconUrl] AS [IconUrl], 
[Project1].[IsLatestStable] AS [IsLatestStable], 
[Project1].[IsLatest] AS [IsLatest], 
[Project1].[IsPrerelease] AS [IsPrerelease], 
[Project1].[Published] AS [Published], 
[Project1].[LicenseUrl] AS [LicenseUrl], 
[Project1].[Language] AS [Language], 
[Project1].[Hash] AS [Hash], 
[Project1].[HashAlgorithm] AS [HashAlgorithm], 
[Project1].[PackageFileSize] AS [PackageFileSize], 
[Project1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], 
[Project1].[C1] AS [C1], 
[Project1].[Title] AS [Title], 
[Project1].[DownloadCount] AS [DownloadCount1]
FROM ( SELECT [Project1].[PackageRegistrationKey] AS [PackageRegistrationKey], [Project1].[Created] AS [Created], [Project1].[Description] AS [Description], 
[Project1].[DownloadCount] AS [DownloadCount], [Project1].[HashAlgorithm] AS [HashAlgorithm], [Project1].[Hash] AS [Hash], [Project1].[IconUrl] AS [IconUrl],
 [Project1].[IsLatest] AS [IsLatest], [Project1].[IsLatestStable] AS [IsLatestStable], [Project1].[LicenseUrl] AS [LicenseUrl],
  [Project1].[Language] AS [Language], [Project1].[Published] AS [Published], [Project1].[PackageFileSize] AS [PackageFileSize], 
  [Project1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], [Project1].[Title] AS [Title], [Project1].[Version] AS [Version], 
  [Project1].[IsPrerelease] AS [IsPrerelease], [Project1].[FlattenedAuthors] AS [FlattenedAuthors], [Project1].[FlattenedDependencies] AS [FlattenedDependencies], 
  [Project1].[Id] AS [Id], [Project1].[DownloadCount1] AS [DownloadCount1], [Project1].[C1] AS [C1], row_number() OVER (ORDER BY [Project1].[DownloadCount1] DESC, 
  [Project1].[Id] ASC, [Project1].[Version] ASC) AS [row_number]
        FROM ( SELECT 
                [Filter1].[PackageRegistrationKey] AS [PackageRegistrationKey], 
                [Filter1].[Created] AS [Created], 
                [Filter1].[Description] AS [Description], 
                [Filter1].[DownloadCount2] AS [DownloadCount], 
                [Filter1].[HashAlgorithm] AS [HashAlgorithm], 
                [Filter1].[Hash] AS [Hash], 
                [Filter1].[IconUrl] AS [IconUrl], 
                [Filter1].[IsLatest] AS [IsLatest], 
                [Filter1].[IsLatestStable] AS [IsLatestStable], 
                [Filter1].[LicenseUrl] AS [LicenseUrl], 
                [Filter1].[Language] AS [Language], 
                [Filter1].[Published] AS [Published], 
                [Filter1].[PackageFileSize] AS [PackageFileSize], 
                [Filter1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], 
                [Filter1].[Title] AS [Title], 
                [Filter1].[Version] AS [Version], 
                [Filter1].[IsPrerelease] AS [IsPrerelease], 
                [Filter1].[FlattenedAuthors] AS [FlattenedAuthors], 
                [Filter1].[FlattenedDependencies] AS [FlattenedDependencies], 
                [Filter1].[Id] AS [Id], 
                [Extent3].[DownloadCount] AS [DownloadCount1], 
                CASE WHEN ([Filter1].[Listed] = 1) THEN [Filter1].[Published] ELSE null END AS [C1]
                FROM (SELECT [Extent1].[PackageRegistrationKey] AS [PackageRegistrationKey], [Extent1].[Created] AS [Created], [Extent1].[Description] AS [Description], 
        [Extent1].[DownloadCount] AS [DownloadCount2], [Extent1].[HashAlgorithm] AS [HashAlgorithm], [Extent1].[Hash] AS [Hash], [Extent1].[IconUrl] AS [IconUrl], 
        [Extent1].[IsLatest] AS [IsLatest], [Extent1].[IsLatestStable] AS [IsLatestStable], [Extent1].[LicenseUrl] AS [LicenseUrl], [Extent1].[Language] AS [Language],
         [Extent1].[Published] AS [Published], [Extent1].[PackageFileSize] AS [PackageFileSize], [Extent1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance],
          [Extent1].[Title] AS [Title], [Extent1].[Version] AS [Version], [Extent1].[Listed] AS [Listed], [Extent1].[IsPrerelease] AS [IsPrerelease], 
          [Extent1].[FlattenedAuthors] AS [FlattenedAuthors], [Extent1].[FlattenedDependencies] AS [FlattenedDependencies], [Extent2].[Id] AS [Id]
                        FROM [dbo].[Packages] AS [Extent1]
                        INNER JOIN [dbo].[PackageRegistrations] AS [Extent2] ON [Extent1].[PackageRegistrationKey] = [Extent2].[Key]
                        WHERE ([Extent1].[Listed] = 1) AND ([Extent1].[IsPrerelease] <> cast(1 as bit)) AND ([Extent1].[IsLatestStable] = 1) ) AS [Filter1]
                LEFT OUTER JOIN [dbo].[PackageRegistrations] AS [Extent3] ON [Filter1].[PackageRegistrationKey] = [Extent3].[Key]
                WHERE ([Filter1].[Id] LIKE '%jquery%' ESCAPE N'~') OR ([Filter1].[Id] LIKE '%ui%' ESCAPE N'~') 
        OR ([Filter1].[Title] LIKE '%jquery%' ESCAPE N'~') OR ([Filter1].[Title] LIKE '%ui%' ESCAPE N'~')
        ) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 30
ORDER BY [Project1].[DownloadCount1] DESC, [Project1].[Id] ASC, [Project1].[Version] ASC 
SELECT TOP (30)
Paged.PackageRegistrationKey
, Paged.Id
, Paged.Version
, Packages.FlattenedAuthors
, Packages.Copyright
, Packages.Created
, Packages.FlattenedDependencies
, Packages.Description
, PackageRegistrations.DownloadCount
, Packages.ExternalPackageUrl
, N'packages/' + PackageRegistrations.Id + N'/' + Packages.Version AS C1
, Packages.IconUrl
, Packages.IsLatestStable
, Packages.Language
, Packages.LastUpdated
, Packages.LicenseUrl
, Packages.Hash
, Packages.HashAlgorithm
, Packages.PackageFileSize
, Packages.ProjectUrl
, CASE Packages.Listed WHEN 1 THEN Packages.Published ELSE NULL END AS C2
, Packages.ReleaseNotes
, N'package/ReportAbuse/' + PackageRegistrations.Id + N'/' + Packages.Version AS C3
, Packages.RequiresLicenseAcceptance
, Packages.Summary
, CASE WHEN Packages.Tags IS NULL THEN CAST(NULL as varchar(1)) ELSE N' ' + LTRIM(RTRIM(Packages.Tags)) + N' ' END AS C4
, ISNULL(Packages.Title, PackageRegistrations.Id) AS C5
, Packages.DownloadCount AS DownloadCount1
, cast(0 as float(53)) AS C6
FROM (
SELECT Filtered.Id
, Filtered.PackageRegistrationKey
, Filtered.Version
, Filtered.DownloadCount
, row_number() OVER (ORDER BY Filtered.DownloadCount DESC, Filtered.Id ASC) AS [row_number]
FROM (
SELECT PackageRegistrations.Id
, Packages.PackageRegistrationKey
, Packages.Version
, PackageRegistrations.DownloadCount
FROM Packages
INNER JOIN PackageRegistrations ON PackageRegistrations.[Key] = Packages.PackageRegistrationKey
WHERE Packages.IsPrerelease <> cast(1 as bit)
) Filtered
) Paged
INNER JOIN PackageRegistrations ON PackageRegistrations.[Key] = Paged.PackageRegistrationKey
INNER JOIN Packages ON Packages.PackageRegistrationKey = Paged.PackageRegistrationKey AND Packages.Version = Paged.Version
WHERE Paged.[row_number] > 30
ORDER BY PackageRegistrations.DownloadCount DESC
, Paged.Id
SELECT TOP (30)
Paged.PackageRegistrationKey
, Paged.Id
, Paged.Version
, Packages.FlattenedAuthors
, Packages.Copyright
, Packages.Created
, Packages.FlattenedDependencies
, Packages.Description
, PackageRegistrations.DownloadCount
, Packages.ExternalPackageUrl
, N'packages/' + PackageRegistrations.Id + N'/' + Packages.Version AS C1
, Packages.IconUrl
, Packages.IsLatestStable
, Packages.Language
, Packages.LastUpdated
, Packages.LicenseUrl
, Packages.Hash
, Packages.HashAlgorithm
, Packages.PackageFileSize
, Packages.ProjectUrl
, CASE Packages.Listed WHEN 1 THEN Packages.Published ELSE NULL END AS C2
, Packages.ReleaseNotes
, N'package/ReportAbuse/' + PackageRegistrations.Id + N'/' + Packages.Version AS C3
, Packages.RequiresLicenseAcceptance
, Packages.Summary
, CASE WHEN Packages.Tags IS NULL THEN CAST(NULL as varchar(1)) ELSE N' ' + LTRIM(RTRIM(Packages.Tags)) + N' ' END AS C4
, ISNULL(Packages.Title, PackageRegistrations.Id) AS C5
, Packages.DownloadCount AS DownloadCount1
, cast(0 as float(53)) AS C6
FROM (
SELECT Filtered.Id
, Filtered.PackageRegistrationKey
, Filtered.Version
, Filtered.DownloadCount
, row_number() OVER (ORDER BY Filtered.DownloadCount DESC, Filtered.Id ASC) AS [row_number]
FROM (
SELECT PackageRegistrations.Id
, Packages.PackageRegistrationKey
, Packages.Version
, PackageRegistrations.DownloadCount
FROM Packages
INNER JOIN PackageRegistrations ON PackageRegistrations.[Key] = Packages.PackageRegistrationKey
WHERE ((((Packages.IsPrerelease <> cast(1 as bit)))))
((((AND Packages.IsLatestStable = 1))))
((((AND Packages.IsLatest = 1))))
AND (
PackageRegistrations.Id LIKE '%jquery%' ESCAPE N'~'
OR PackageRegistrations.Id LIKE '%ui%' ESCAPE N'~'
OR Packages.Title LIKE '%jquery%' ESCAPE N'~'
OR Packages.Title LIKE '%ui%' ESCAPE N'~'
OR Packages.Tags LIKE '%jquery%' ESCAPE N'~'
OR Packages.Tags LIKE '%ui%' ESCAPE N'~'
)
) Filtered
) Paged
INNER JOIN PackageRegistrations ON PackageRegistrations.[Key] = Paged.PackageRegistrationKey
INNER JOIN Packages ON Packages.PackageRegistrationKey = Paged.PackageRegistrationKey AND Packages.Version = Paged.Version
WHERE Paged.[row_number] > 30
ORDER BY PackageRegistrations.DownloadCount DESC
, Paged.Id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment