Having this LINQ query:
Query.All().Where(sml => sml.IsAvailable).Where(sml =>
(sml.StorageUnit.Enterprise == @.entity)).Select(p => new RowTuple() {
V0 = ((Object)p.ID),
V1 = ((Object)p.TypeId),
V2 = ((Object)p.CreationTime),
V3 = ((Object)new Tuple<String,Double,Double>(
p.RemainedQuantity.MeasureType,
p.RemainedQuantity.NormalizedValue,
p.RemainedQuantity.LastUsedScale
)),
V4 = ((Object)p.ResolvedLocation.FullAddress),
V5 = ((Object)Query.All().Where(suv => ((((((Int32)suv.VersionState) == 1) && (suv.ActiveFrom <= @.now)) && (suv.ActiveTo >= @.now)) && (suv.Owner == p.StorageUnit))).FirstOrDefault().StorageZone.Name),
V6 = ((Object)p.StorageUnit.Name),
V7 = ((Object)p.StorageLocation.Address),
V8 = ((Object)p.StoredContainer.Container.UniqueIdentifier),
V9 = ((Object)p.BlockReason.Description),
V10 = ((Object)p.MaterialLot.UniqueIdentifier),
V11 = ((Object)p.MaterialLot.ProductVersion.Owner.Name),
V12 = ((Object)p.MaterialLot.ProductVersion.Owner.Code),
V13 = ((Object)(((((((((((Object)p.MaterialLot.ProductVersion.VersionNumber) + "-") + p.MaterialLot.ProductVersion.VersionDescription) + " (") + ((Object)p.MaterialLot.ProductVersion.VersionState)) + " from ") + ((Object)p.MaterialLot.ProductVersion.ActiveFrom)) + " to ") + ((Object)p.MaterialLot.ProductVersion.ActiveTo)) + ")")),
V14 = ((Object)p.MaterialLot.ProductVersion.Owner.ExternalType.Name),
V15 = ((Object)Query.All().Where(tf => ((tf.Owner == p.MaterialLot) && (tf.Name == @.tfName))).FirstOrDefault().Value),
V16 = ((Object)Query.All().Where(tf => ((tf.Owner == p.MaterialLot) && (tf.Name == @.tfName))).FirstOrDefault().Value),
V17 = ((Object)Query.All().Where(tf => ((tf.Owner == p.MaterialLot) && (tf.Name == @.tfName))).FirstOrDefault().Value),
V18 = ((Object)Query.All().Where(tf => ((tf.Owner == p.MaterialLot) && (tf.Name == @.tfName))).FirstOrDefault().Value)
}).OrderBy(t => t.V2).Skip(0).Take(50)
results in the following SQL one:
[17:09:39] Белов Алексей: SELECT [a].[ID], [a].[TypeId],
[a].[CreationTime], [a].[RemainedQuantity.MeasureType],
[a].[RemainedQuantity.NormalizedValue], [a].[RemainedQuantity.LastUsedScale],
[a].[#a.Name], [a].[#b.FullAddress], [a].[#d.Name], [a].[#e.Address],
[a].[#g.UniqueIdentifier], [a].[#h.Description], [a].[#i.UniqueIdentifier],
[a].[#j.VersionState], [a].[#j.ActiveFrom], [a].[#j.ActiveTo],
[a].[#j.VersionDescription], [a].[#j.VersionNumber], [a].[#k.Code],
[a].[#k.Name], [a].[#l.Name], [a].[#m.Value], [a].[#n.Value], [a].[#o.Value],
[a].[#p.Value]
FROM (SELECT TOP 50 [b].[ID], [b].[TypeId], [b].[StorageUnit.ID],
[b].[StorageLocation.ID], [b].[ResolvedLocation.ID], [b].[StoredContainer.ID],
[b].[CreationTime], [b].[Quantity.MeasureType], [b].[Quantity.NormalizedValue],
[b].[Quantity.LastUsedScale], [b].[UsedQuantity.MeasureType],
[b].[UsedQuantity.NormalizedValue], [b].[UsedQuantity.LastUsedScale],
[b].[RemainedQuantity.MeasureType], [b].[RemainedQuantity.NormalizedValue],
[b].[RemainedQuantity.LastUsedScale], [b].[BlockReason.ID], [b].[IsAvailable],
[b].[Creator.ID], [b].[MaterialLot.ID], [b].[#a.ID], [b].[#a.TypeId],
[b].[#a.Enterprise.ID], [b].[#a.Site.ID], [b].[#a.Area.ID], [b].[#a.Name],
[b].[#a.Description], [b].[#a.MarkedAsFull], [b].[#a.LockedForInput],
[b].[#a.LockedForOutput], [b].[#a.TopViewDesignerData],
[b].[#a.NextVersionNumber], [c].[ID] AS [#b.ID], [c].[TypeId] AS [#b.TypeId],
[c].[Address] AS [#b.Address], [c].[FullAddress] AS [#b.FullAddress],
[d].[#c.StorageZone.ID], [e].[ID] AS [#d.ID], [e].[TypeId] AS [#d.TypeId],
[e].[Enterprise.ID] AS [#d.Enterprise.ID], [e].[Site.ID] AS [#d.Site.ID],
[e].[Area.ID] AS [#d.Area.ID], [e].[Name] AS [#d.Name], [e].[Description] AS
[#d.Description], [e].[ConnectionDesignerData] AS [#d.ConnectionDesignerData],
[e].[Address] AS [#d.Address], [e].[FullAddress] AS [#d.FullAddress],
[e].[TopViewDesignerData] AS [#d.TopViewDesignerData], [e].[NextVersionNumber]
AS [#d.NextVersionNumber], [f].[ID] AS [#e.ID], [f].[TypeId] AS [#e.TypeId],
[f].[Enterprise.ID] AS [#e.Enterprise.ID], [f].[Site.ID] AS [#e.Site.ID],
[f].[Area.ID] AS [#e.Area.ID], [f].[Owner.ID] AS [#e.Owner.ID], [f].[Address]
AS [#e.Address], [f].[AddressSeparator] AS [#e.AddressSeparator],
[f].[FullAddress] AS [#e.FullAddress], [f].[IsCapacityValid] AS
[#e.IsCapacityValid], [f].[Capacity.MeasureType] AS [#e.Capacity.MeasureType],
[f].[Capacity.NormalizedValue] AS [#e.Capacity.NormalizedValue],
[f].[Capacity.LastUsedScale] AS [#e.Capacity.LastUsedScale],
[f].[IsUsedCapacityValid] AS [#e.IsUsedCapacityValid],
[f].[UsedCapacity.MeasureType] AS [#e.UsedCapacity.MeasureType],
[f].[UsedCapacity.NormalizedValue] AS [#e.UsedCapacity.NormalizedValue],
[f].[UsedCapacity.LastUsedScale] AS [#e.UsedCapacity.LastUsedScale],
[f].[LockedForInput] AS [#e.LockedForInput], [f].[LockedForOutput] AS
[#e.LockedForOutput], [f].[TopViewDesignerData] AS [#e.TopViewDesignerData],
[g].[ID] AS [#f.ID], [g].[TypeId] AS [#f.TypeId], [g].[StorageUnit.ID] AS
[#f.StorageUnit.ID], [g].[StorageLocation.ID] AS [#f.StorageLocation.ID],
[g].[ResolvedLocation.ID] AS [#f.ResolvedLocation.ID], [g].[StoredContainer.ID]
AS [#f.StoredContainer.ID], [g].[CreationTime] AS [#f.CreationTime],
[g].[Quantity.MeasureType] AS [#f.Quantity.MeasureType],
[g].[Quantity.NormalizedValue] AS [#f.Quantity.NormalizedValue],
[g].[Quantity.LastUsedScale] AS [#f.Quantity.LastUsedScale],
[g].[UsedQuantity.MeasureType] AS [#f.UsedQuantity.MeasureType],
[g].[UsedQuantity.NormalizedValue] AS [#f.UsedQuantity.NormalizedValue],
[g].[UsedQuantity.LastUsedScale] AS [#f.UsedQuantity.LastUsedScale],
[g].[RemainedQuantity.MeasureType] AS [#f.RemainedQuantity.MeasureType],
[g].[RemainedQuantity.NormalizedValue] AS
[#f.RemainedQuantity.NormalizedValue], [g].[RemainedQuantity.LastUsedScale] AS
[#f.RemainedQuantity.LastUsedScale], [g].[BlockReason.ID] AS
[#f.BlockReason.ID], [g].[IsAvailable] AS [#f.IsAvailable], [h].[Creator.ID] AS
[#f.Creator.ID], [h].[Container.ID] AS [#f.Container.ID], [i].[ID] AS [#g.ID],
[i].[TypeId] AS [#g.TypeId], [i].[UniqueIdentifier] AS [#g.UniqueIdentifier],
[j].[#h.ID], [j].[#h.TypeId], [j].[#h.Code], [j].[#h.Description],
[j].[#h.Category], [k].[#i.ID], [k].[#i.TypeId], [k].[#i.Creator.ID],
[k].[#i.ProductVersion.ID], [k].[#i.CreateTime], [k].[#i.UniqueIdentifier],
[l].[#j.ID], [l].[#j.TypeId], [l].[#j.Enterprise.ID], [l].[#j.Site.ID],
[l].[#j.SyncKey.ID], [l].[#j.SyncKey.VersionID], [l].[#j.Owner.ID],
[l].[#j.VersionState], [l].[#j.ActiveFrom], [l].[#j.ActiveTo],
[l].[#j.Priority], [l].[#j.VersionDescription], [l].[#j.VersionNumber],
[m].[#k.ID], [m].[#k.TypeId], [m].[#k.Enterprise.ID], [m].[#k.Code],
[m].[#k.Name], [m].[#k.Description], [m].[#k.Category],
[m].[#k.ExternalType.ID], [m].[#k.MeasureType], [m].[#k.Density.MeasureType],
[m].[#k.Density.NormalizedValue], [m].[#k.Density.LastUsedScale],
[m].[#k.SyncKey.ID], [m].[#k.SyncKey.VersionID], [m].[#k.NextVersionNumber],
[n].[#l.ID], [n].[#l.TypeId], [n].[#l.ExternalTypeKey.TypeID], [n].[#l.Name],
[n].[#l.LocalTypeID], [n].[#l.BaseType.ID], [o].[#m.Value], [p].[#n.Value],
[q].[#o.Value], [r].[#p.Value], ROW_NUMBER() OVER(ORDER BY [b].[CreationTime]
ASC) AS [RowNumber0]
FROM (SELECT [s].[ID], [s].[TypeId], [s].[StorageUnit.ID],
[s].[StorageLocation.ID], [s].[ResolvedLocation.ID], [s].[StoredContainer.ID],
[s].[CreationTime], [s].[Quantity.MeasureType], [s].[Quantity.NormalizedValue],
[s].[Quantity.LastUsedScale], [s].[UsedQuantity.MeasureType],
[s].[UsedQuantity.NormalizedValue], [s].[UsedQuantity.LastUsedScale],
[s].[RemainedQuantity.MeasureType], [s].[RemainedQuantity.NormalizedValue],
[s].[RemainedQuantity.LastUsedScale], [s].[BlockReason.ID], [s].[IsAvailable],
[s].[Creator.ID], [s].[MaterialLot.ID], [t].[ID] AS [#a.ID], [t].[TypeId] AS
[#a.TypeId], [t].[Enterprise.ID] AS [#a.Enterprise.ID], [t].[Site.ID] AS
[#a.Site.ID], [t].[Area.ID] AS [#a.Area.ID], [t].[Name] AS [#a.Name],
[t].[Description] AS [#a.Description], [t].[MarkedAsFull] AS [#a.MarkedAsFull],
[t].[LockedForInput] AS [#a.LockedForInput], [t].[LockedForOutput] AS
[#a.LockedForOutput], [t].[TopViewDesignerData] AS [#a.TopViewDesignerData],
[t].[NextVersionNumber] AS [#a.NextVersionNumber] FROM (SELECT [u].[ID],
[u].[TypeId], [u].[StorageUnit.ID], [u].[StorageLocation.ID],
[u].[ResolvedLocation.ID], [u].[StoredContainer.ID], [u].[CreationTime],
[u].[Quantity.MeasureType], [u].[Quantity.NormalizedValue],
[u].[Quantity.LastUsedScale], [u].[UsedQuantity.MeasureType],
[u].[UsedQuantity.NormalizedValue], [u].[UsedQuantity.LastUsedScale],
[u].[RemainedQuantity.MeasureType], [u].[RemainedQuantity.NormalizedValue],
[u].[RemainedQuantity.LastUsedScale], [u].[BlockReason.ID], [u].[IsAvailable],
[v].[Creator.ID], [v].[MaterialLot.ID]
FROM (SELECT [w].[ID], [w].[TypeId], [w].[Creator.ID], [w].[MaterialLot.ID]
FROM [dbo].[StoredMaterialLot] [w]) [v]
INNER JOIN (SELECT [x].[ID], [x].[TypeId], [x].[StorageUnit.ID],
[x].[StorageLocation.ID], [x].[ResolvedLocation.ID], [x].[StoredContainer.ID],
[x].[CreationTime], [x].[Quantity.MeasureType], [x].[Quantity.NormalizedValue],
[x].[Quantity.LastUsedScale], [x].[UsedQuantity.MeasureType],
[x].[UsedQuantity.NormalizedValue], [x].[UsedQuantity.LastUsedScale],
[x].[RemainedQuantity.MeasureType], [x].[RemainedQuantity.NormalizedValue],
[x].[RemainedQuantity.LastUsedScale], [x].[BlockReason.ID], [x].[IsAvailable]
FROM [dbo].[StoredObject] [x]) [u] ON ([u].[ID] = [v].[ID])
WHERE ([u].[IsAvailable] <> 0)) [s]
LEFT OUTER JOIN [dbo].[StorageUnit] [t] ON ([s].[StorageUnit.ID] = [t].[ID])
WHERE ([t].[Enterprise.ID] = @p0_0)) [b]
LEFT OUTER JOIN (((SELECT [y].[ID], [y].[TypeId], [y].[Address],
[y].[FullAddress]
FROM (SELECT [z].[ID], [z].[TypeId], [z].[Address], [z].[FullAddress]
FROM [dbo].[StorageUnitVersion] [z]
WHERE ([z].[TypeId] IN (105, 125, 144, 166, 149, 140, 131, 121, 113))) [y])
UNION (SELECT [a1].[ID], [a1].[TypeId], [a1].[Address], [a1].[FullAddress]
FROM (SELECT [b1].[ID], [b1].[TypeId], [b1].[Address], [b1].[FullAddress]
FROM [dbo].[StorageZone] [b1]
WHERE ([b1].[TypeId] IN (167, 145, 132, 122, 126, 114, 106, 141, 150))) [a1]))
UNION (SELECT [c1].[ID], [c1].[TypeId], [c1].[Address], [c1].[FullAddress]
FROM (SELECT [d1].[ID], [d1].[TypeId], [d1].[Address], [d1].[FullAddress]
FROM [dbo].[StorageLocation] [d1]
WHERE ([d1].[TypeId] IN (111, 129, 147, 119))) [c1])) [c] ON
([b].[ResolvedLocation.ID] = [c].[ID])
OUTER APPLY (SELECT TOP 1 [e1].[StorageZone.ID] AS [#c.StorageZone.ID]
FROM [dbo].[StorageUnitVersion] [e1]
WHERE ((((CAST(CAST([e1].[VersionState] AS integer) AS integer) = 1) AND
([e1].[ActiveFrom] <= @p0_1)) AND ([e1].[ActiveTo] >= @p0_2)) AND
([e1].[Owner.ID] = [b].[StorageUnit.ID]))
ORDER BY [e1].[ID] ASC) [d]
LEFT OUTER JOIN [dbo].[StorageZone] [e] ON ([d].[#c.StorageZone.ID] = [e].[ID])
LEFT OUTER JOIN [dbo].[StorageLocation] [f] ON ([b].[StorageLocation.ID] =
[f].[ID])
LEFT OUTER JOIN (SELECT [f1].[ID], [f1].[TypeId], [f1].[Creator.ID],
[f1].[Container.ID] FROM [dbo].[StoredContainer] [f1]) [h]
INNER JOIN (SELECT [g1].[ID], [g1].[TypeId], [g1].[StorageUnit.ID],
[g1].[StorageLocation.ID], [g1].[ResolvedLocation.ID],
[g1].[StoredContainer.ID], [g1].[CreationTime], [g1].[Quantity.MeasureType],
[g1].[Quantity.NormalizedValue], [g1].[Quantity.LastUsedScale],
[g1].[UsedQuantity.MeasureType], [g1].[UsedQuantity.NormalizedValue],
[g1].[UsedQuantity.LastUsedScale], [g1].[RemainedQuantity.MeasureType],
[g1].[RemainedQuantity.NormalizedValue], [g1].[RemainedQuantity.LastUsedScale],
[g1].[BlockReason.ID], [g1].[IsAvailable]
FROM [dbo].[StoredObject] [g1]) [g] ON ([g].[ID] = [h].[ID]) ON
([b].[StoredContainer.ID] = [g].[ID]) LEFT OUTER JOIN [dbo].[Container] [i] ON
([h].[Container.ID] = [i].[ID])
LEFT OUTER JOIN (SELECT [h1].[ID] AS [#h.ID], 103 AS [#h.TypeId], [h1].[Code]
AS [#h.Code], [h1].[Description] AS [#h.Description], [h1].[Category] AS
[#h.Category]
FROM [dbo].[BlockReason] [h1]) [j] ON ([b].[BlockReason.ID] = [j].[#h.ID])
LEFT OUTER JOIN (SELECT [i1].[ID] AS [#i.ID], 128 AS [#i.TypeId],
[i1].[Creator.ID] AS [#i.Creator.ID], [i1].[ProductVersion.ID] AS
[#i.ProductVersion.ID], [i1].[CreateTime] AS [#i.CreateTime],
[i1].[UniqueIdentifier] AS [#i.UniqueIdentifier]
FROM [dbo].[MaterialLot] [i1]) [k] ON ([b].[MaterialLot.ID] = [k].[#i.ID])
LEFT OUTER JOIN (SELECT [j1].[ID] AS [#j.ID], 138 AS [#j.TypeId],
[j1].[Enterprise.ID] AS [#j.Enterprise.ID], [j1].[Site.ID] AS [#j.Site.ID],
[j1].[SyncKey.ID] AS [#j.SyncKey.ID], [j1].[SyncKey.VersionID] AS
[#j.SyncKey.VersionID], [j1].[Owner.ID] AS [#j.Owner.ID], [j1].[VersionState]
AS [#j.VersionState], [j1].[ActiveFrom] AS [#j.ActiveFrom], [j1].[ActiveTo] AS
[#j.ActiveTo], [j1].[Priority] AS [#j.Priority], [j1].[VersionDescription] AS
[#j.VersionDescription], [j1].[VersionNumber] AS [#j.VersionNumber]
FROM [dbo].[ProductVersion] [j1]) [l] ON ([k].[#i.ProductVersion.ID] =
[l].[#j.ID])
LEFT OUTER JOIN (SELECT [k1].[ID] AS [#k.ID], 136 AS [#k.TypeId],
[k1].[Enterprise.ID] AS [#k.Enterprise.ID], [k1].[Code] AS [#k.Code],
[k1].[Name] AS [#k.Name], [k1].[Description] AS [#k.Description],
[k1].[Category] AS [#k.Category], [k1].[ExternalType.ID] AS
[#k.ExternalType.ID], [k1].[MeasureType] AS [#k.MeasureType],
[k1].[Density.MeasureType] AS [#k.Density.MeasureType],
[k1].[Density.NormalizedValue] AS [#k.Density.NormalizedValue],
[k1].[Density.LastUsedScale] AS [#k.Density.LastUsedScale], [k1].[SyncKey.ID]
AS [#k.SyncKey.ID], [k1].[SyncKey.VersionID] AS [#k.SyncKey.VersionID],
[k1].[NextVersionNumber] AS [#k.NextVersionNumber]
FROM [dbo].[Product] [k1]) [m] ON ([l].[#j.Owner.ID] = [m].[#k.ID])
LEFT OUTER JOIN (SELECT [l1].[ID] AS [#l.ID], 118 AS [#l.TypeId],
[l1].[ExternalTypeKey.TypeID] AS [#l.ExternalTypeKey.TypeID], [l1].[Name] AS
[#l.Name], [l1].[LocalTypeID] AS [#l.LocalTypeID], [l1].[BaseType.ID] AS
[#l.BaseType.ID] FROM [dbo].[ExternalType] [l1]) [n] ON
([m].[#k.ExternalType.ID] = [n].[#l.ID])
OUTER APPLY (SELECT TOP 1 [m1].[Value] AS [#m.Value] FROM [dbo].[TraceField]
[m1]
WHERE (([m1].[Owner.ID] = [b].[MaterialLot.ID]) AND ([m1].[Name] = @p0_3))
ORDER BY [m1].[ID] ASC) [o]
OUTER APPLY (SELECT TOP 1 [n1].[Value] AS [#n.Value] FROM [dbo].[TraceField]
[n1]
WHERE (([n1].[Owner.ID] = [b].[MaterialLot.ID]) AND ([n1].[Name] = @p0_4))
ORDER BY [n1].[ID] ASC) [p]
OUTER APPLY (SELECT TOP 1 [o1].[Value] AS [#o.Value] FROM [dbo].[TraceField]
[o1]
WHERE (([o1].[Owner.ID] = [b].[MaterialLot.ID]) AND ([o1].[Name] = @p0_5))
ORDER BY [o1].[ID] ASC) [q]
OUTER APPLY (SELECT TOP 1 [p1].[Value] AS [#p.Value] FROM [dbo].[TraceField]
[p1]
WHERE (([p1].[Owner.ID] = [b].[MaterialLot.ID]) AND ([p1].[Name] = @p0_6))
ORDER BY [p1].[ID] ASC) [r]
ORDER BY [b].[CreationTime] ASC) [a]
WHERE ([a].[RowNumber0] > 0)
ORDER BY [a].[CreationTime] ASC;
The question is whether it is possible to automatically optimize the resulting
SQL query or not.
Original issue reported on code.google.com by Dmitri.Maximov on 1 Dec 2010 at 12:42
Original issue reported on code.google.com by
Dmitri.Maximovon 1 Dec 2010 at 12:42