xumix wrote:
This can be made even faster on MSSQL if use CTE like
WITH OrderQuery AS (
SELECT
[h].[Id] ,
171 AS [TypeId] ,
.....
ROW_NUMBER() OVER ( ORDER BY [h].[Id] ASC ) AS [RowNumber0]
FROM [dbo].[RegMarketPrice] [h]
)
FROM ( SELECT [g].[Id] ,
[g].[TypeId] ,
.......
FROM ( SELECT *
FROM OrderQuery AS h
) [g]
WHERE ( [g].[RowNumber0] BETWEEN 312 AND 351 )
) [a]
Alex (Xtensive) wrote:
I'm curious, why plan with CTE must be better? I.e. are there any real reasons? If there is an article explaining this, it would be great to get some links here.
As far as I can judge, all 3 cases (or at least first two - i.e. except a case with CTE) must lead to nearly the same plan. So I;d like to know if this is just SQL Server specific "feature" (or may be just a particular case specific behavior), or there is something more important behind this case?
xumix wrote:
Here is the code:
SELECT [a].[Id] ,
[a].[Date] ,
[a].[MarketPlace_Id] ,
[a].[FinTool_Id] ,
[a].[TransactionCount] ,
[a].[CountPerTransaction] ,
[a].[TradeVolume] ,
[a].[TradeVolumeCurrency_Id] ,
[a].[IsPercent] ,
[a].[FinToolCurrency_Id] ,
[a].[QuotationSheetLevel_Id] ,
[a].[AveragePrice] ,
[a].[Price06155] ,
[a].[#a.Name] ,
[a].[#b.Name] ,
[a].[#c.SysName] ,
[a].[#d.SysName] ,
[b].[#e.Name]
FROM ( SELECT [c].[Id] ,
[c].[TypeId] ,
[c].[Date] ,
[c].[MarketPlace_Id] ,
[c].[FinTool_Id] ,
[c].[TransactionCount] ,
[c].[CountPerTransaction] ,
[c].[TradeVolume] ,
[c].[TradeVolumeCurrency_Id] ,
[c].[IsPercent] ,
[c].[FinToolCurrency_Id] ,
[c].[QuotationSheetLevel_Id] ,
[c].[AveragePrice] ,
[c].[Price06155] ,
[c].[#a.Id] ,
[c].[#a.TypeId] ,
[c].[#a.IntId] ,
[c].[#a.Emitter_Id] ,
[c].[#a.FinToolGroup_Id] ,
[c].[#a.FinToolType_Id] ,
[c].[#a.Name] ,
[c].[#a.FullName] ,
[c].[#a.Currency_Id] ,
[c].[#a.Nominal] ,
[c].[#a.Version] ,
[d].[#b.Id] ,
[d].[#b.TypeId] ,
[d].[#b.IntId] ,
[d].[#b.Name] ,
[d].[#b.IsFreeBroker] ,
[d].[#b.Currency_Id] ,
[d].[#b.Version] ,
[e].[#c.Id] ,
[e].[#c.TypeId] ,
[e].[#c.SysName] ,
[e].[#c.RuName] ,
[e].[#c.EnName] ,
[e].[#c.Version] ,
[f].[#d.Id] ,
[f].[#d.TypeId] ,
[f].[#d.SysName] ,
[f].[#d.RuName] ,
[f].[#d.EnName] ,
[f].[#d.Version]
FROM ( SELECT TOP 6355
[g].[Id] ,
[g].[TypeId] ,
[g].[Date] ,
[g].[MarketPlace_Id] ,
[g].[FinTool_Id] ,
[g].[TransactionCount] ,
[g].[CountPerTransaction] ,
[g].[TradeVolume] ,
[g].[TradeVolumeCurrency_Id] ,
[g].[IsPercent] ,
[g].[FinToolCurrency_Id] ,
[g].[QuotationSheetLevel_Id] ,
[g].[AveragePrice] ,
[g].[Price06155] ,
[h].[#a.Id] ,
[h].[#a.TypeId] ,
[h].[#a.IntId] ,
[h].[#a.Emitter_Id] ,
[h].[#a.FinToolGroup_Id] ,
[h].[#a.FinToolType_Id] ,
[h].[#a.Name] ,
[h].[#a.FullName] ,
[h].[#a.Currency_Id] ,
[h].[#a.Nominal] ,
[h].[#a.Version] ,
ROW_NUMBER() OVER ( ORDER BY [h].[#a.Name] ASC ) AS [RowNumber0]
FROM ( SELECT [i].[Id] ,
181 AS [TypeId] ,
[i].[Date] ,
[i].[MarketPlace_Id] ,
[i].[FinTool_Id] ,
[i].[TransactionCount] ,
[i].[CountPerTransaction] ,
[i].[TradeVolume] ,
[i].[TradeVolumeCurrency_Id] ,
[i].[IsPercent] ,
[i].[FinToolCurrency_Id] ,
[i].[QuotationSheetLevel_Id] ,
[i].[AveragePrice] ,
[i].[Price06155]
FROM [dbo].[RegMarketPrice] [i]
) [g]
INNER JOIN ( SELECT [j].[Id] AS [#a.Id] ,
146 AS [#a.TypeId] ,
[j].[IntId] AS [#a.IntId] ,
[j].[Emitter_Id] AS [#a.Emitter_Id] ,
[j].[FinToolGroup_Id] AS [#a.FinToolGroup_Id] ,
[j].[FinToolType_Id] AS [#a.FinToolType_Id] ,
[j].[Name] AS [#a.Name] ,
[j].[FullName] AS [#a.FullName] ,
[j].[Currency_Id] AS [#a.Currency_Id] ,
[j].[Nominal] AS [#a.Nominal] ,
[j].[Version] AS [#a.Version]
FROM [dbo].[FinTool] [j]
) [h] ON ( [g].[FinTool_Id] = [h].[#a.Id] )
ORDER BY [h].[#a.Name] ASC
) [c]
INNER JOIN ( SELECT [k].[Id] AS [#b.Id] ,
161 AS [#b.TypeId] ,
[k].[IntId] AS [#b.IntId] ,
[k].[Name] AS [#b.Name] ,
[k].[IsFreeBroker] AS [#b.IsFreeBroker] ,
[k].[Currency_Id] AS [#b.Currency_Id] ,
[k].[Version] AS [#b.Version]
FROM [dbo].[MarketPlace] [k]
) [d] ON ( [c].[MarketPlace_Id] = [d].[#b.Id] )
INNER JOIN ( SELECT [l].[Id] AS [#c.Id] ,
109 AS [#c.TypeId] ,
[l].[SysName] AS [#c.SysName] ,
[l].[RuName] AS [#c.RuName] ,
[l].[EnName] AS [#c.EnName] ,
[l].[Version] AS [#c.Version]
FROM [dbo].[Currency] [l]
) [e] ON ( [c].[TradeVolumeCurrency_Id] = [e].[#c.Id] )
INNER JOIN ( SELECT [m].[Id] AS [#d.Id] ,
109 AS [#d.TypeId] ,
[m].[SysName] AS [#d.SysName] ,
[m].[RuName] AS [#d.RuName] ,
[m].[EnName] AS [#d.EnName] ,
[m].[Version] AS [#d.Version]
FROM [dbo].[Currency] [m]
) [f] ON ( [c].[FinToolCurrency_Id] = [f].[#d.Id] )
WHERE ( [c].[RowNumber0] > 6314 )
) [a]
LEFT OUTER JOIN ( SELECT [n].[Id] AS [#e.Id] ,
178 AS [#e.TypeId] ,
[n].[IntId] AS [#e.IntId] ,
[n].[MarketPlace_Id] AS [#e.MarketPlace_Id] ,
[n].[Name] AS [#e.Name] ,
[n].[Version] AS [#e.Version]
FROM [dbo].[QuotationSheet] [n]
) [b] ON ( [a].[QuotationSheetLevel_Id] = [b].[#e.Id] )
ORDER BY [a].[#a.Name] ASC;
SELECT [a].[Id] ,
[a].[Date] ,
[a].[MarketPlace_Id] ,
[a].[FinTool_Id] ,
[a].[TransactionCount] ,
[a].[CountPerTransaction] ,
[a].[TradeVolume] ,
[a].[TradeVolumeCurrency_Id] ,
[a].[IsPercent] ,
[a].[FinToolCurrency_Id] ,
[a].[QuotationSheetLevel_Id] ,
[a].[AveragePrice] ,
[a].[Price06155] ,
[a].[#a.Name] ,
[a].[#b.Name] ,
[a].[#c.SysName] ,
[a].[#d.SysName] ,
[b].[#e.Name]
FROM ( SELECT [c].[Id] ,
[c].[TypeId] ,
[c].[Date] ,
[c].[MarketPlace_Id] ,
[c].[FinTool_Id] ,
[c].[TransactionCount] ,
[c].[CountPerTransaction] ,
[c].[TradeVolume] ,
[c].[TradeVolumeCurrency_Id] ,
[c].[IsPercent] ,
[c].[FinToolCurrency_Id] ,
[c].[QuotationSheetLevel_Id] ,
[c].[AveragePrice] ,
[c].[Price06155] ,
[c].[#a.Id] ,
[c].[#a.TypeId] ,
[c].[#a.IntId] ,
[c].[#a.Emitter_Id] ,
[c].[#a.FinToolGroup_Id] ,
[c].[#a.FinToolType_Id] ,
[c].[#a.Name] ,
[c].[#a.FullName] ,
[c].[#a.Currency_Id] ,
[c].[#a.Nominal] ,
[c].[#a.Version] ,
[d].[#b.Id] ,
[d].[#b.TypeId] ,
[d].[#b.IntId] ,
[d].[#b.Name] ,
[d].[#b.IsFreeBroker] ,
[d].[#b.Currency_Id] ,
[d].[#b.Version] ,
[e].[#c.Id] ,
[e].[#c.TypeId] ,
[e].[#c.SysName] ,
[e].[#c.RuName] ,
[e].[#c.EnName] ,
[e].[#c.Version] ,
[f].[#d.Id] ,
[f].[#d.TypeId] ,
[f].[#d.SysName] ,
[f].[#d.RuName] ,
[f].[#d.EnName] ,
[f].[#d.Version]
FROM ( SELECT TOP 6355
[g].[Id] ,
[g].[TypeId] ,
[g].[Date] ,
[g].[MarketPlace_Id] ,
[g].[FinTool_Id] ,
[g].[TransactionCount] ,
[g].[CountPerTransaction] ,
[g].[TradeVolume] ,
[g].[TradeVolumeCurrency_Id] ,
[g].[IsPercent] ,
[g].[FinToolCurrency_Id] ,
[g].[QuotationSheetLevel_Id] ,
[g].[AveragePrice] ,
[g].[Price06155] ,
[h].[#a.Id] ,
[h].[#a.TypeId] ,
[h].[#a.IntId] ,
[h].[#a.Emitter_Id] ,
[h].[#a.FinToolGroup_Id] ,
[h].[#a.FinToolType_Id] ,
[h].[#a.Name] ,
[h].[#a.FullName] ,
[h].[#a.Currency_Id] ,
[h].[#a.Nominal] ,
[h].[#a.Version] ,
ROW_NUMBER() OVER ( ORDER BY [h].[#a.Name] ASC ) AS [RowNumber0]
FROM ( SELECT [i].[Id] ,
181 AS [TypeId] ,
[i].[Date] ,
[i].[MarketPlace_Id] ,
[i].[FinTool_Id] ,
[i].[TransactionCount] ,
[i].[CountPerTransaction] ,
[i].[TradeVolume] ,
[i].[TradeVolumeCurrency_Id] ,
[i].[IsPercent] ,
[i].[FinToolCurrency_Id] ,
[i].[QuotationSheetLevel_Id] ,
[i].[AveragePrice] ,
[i].[Price06155]
FROM [dbo].[RegMarketPrice] [i]
) [g]
INNER JOIN ( SELECT [j].[Id] AS [#a.Id] ,
146 AS [#a.TypeId] ,
[j].[IntId] AS [#a.IntId] ,
[j].[Emitter_Id] AS [#a.Emitter_Id] ,
[j].[FinToolGroup_Id] AS [#a.FinToolGroup_Id] ,
[j].[FinToolType_Id] AS [#a.FinToolType_Id] ,
[j].[Name] AS [#a.Name] ,
[j].[FullName] AS [#a.FullName] ,
[j].[Currency_Id] AS [#a.Currency_Id] ,
[j].[Nominal] AS [#a.Nominal] ,
[j].[Version] AS [#a.Version]
FROM [dbo].[FinTool] [j]
) [h] ON ( [g].[FinTool_Id] = [h].[#a.Id] )
ORDER BY [h].[#a.Name] ASC
) [c]
INNER JOIN ( SELECT [k].[Id] AS [#b.Id] ,
161 AS [#b.TypeId] ,
[k].[IntId] AS [#b.IntId] ,
[k].[Name] AS [#b.Name] ,
[k].[IsFreeBroker] AS [#b.IsFreeBroker] ,
[k].[Currency_Id] AS [#b.Currency_Id] ,
[k].[Version] AS [#b.Version]
FROM [dbo].[MarketPlace] [k]
) [d] ON ( [c].[MarketPlace_Id] = [d].[#b.Id] )
INNER JOIN ( SELECT [l].[Id] AS [#c.Id] ,
109 AS [#c.TypeId] ,
[l].[SysName] AS [#c.SysName] ,
[l].[RuName] AS [#c.RuName] ,
[l].[EnName] AS [#c.EnName] ,
[l].[Version] AS [#c.Version]
FROM [dbo].[Currency] [l]
) [e] ON ( [c].[TradeVolumeCurrency_Id] = [e].[#c.Id] )
INNER JOIN ( SELECT [m].[Id] AS [#d.Id] ,
109 AS [#d.TypeId] ,
[m].[SysName] AS [#d.SysName] ,
[m].[RuName] AS [#d.RuName] ,
[m].[EnName] AS [#d.EnName] ,
[m].[Version] AS [#d.Version]
FROM [dbo].[Currency] [m]
) [f] ON ( [c].[FinToolCurrency_Id] = [f].[#d.Id] )
WHERE ( [c].[RowNumber0] BETWEEN 6315 AND 6355 )
) [a]
LEFT OUTER JOIN ( SELECT [n].[Id] AS [#e.Id] ,
178 AS [#e.TypeId] ,
[n].[IntId] AS [#e.IntId] ,
[n].[MarketPlace_Id] AS [#e.MarketPlace_Id] ,
[n].[Name] AS [#e.Name] ,
[n].[Version] AS [#e.Version]
FROM [dbo].[QuotationSheet] [n]
) [b] ON ( [a].[QuotationSheetLevel_Id] = [b].[#e.Id] )
ORDER BY [a].[#a.Name] ASC;
WITH OrderQuery AS ( SELECT
[g].[Id] ,
[g].[TypeId] ,
[g].[Date] ,
[g].[MarketPlace_Id] ,
[g].[FinTool_Id] ,
[g].[TransactionCount] ,
[g].[CountPerTransaction] ,
[g].[TradeVolume] ,
[g].[TradeVolumeCurrency_Id] ,
[g].[IsPercent] ,
[g].[FinToolCurrency_Id] ,
[g].[QuotationSheetLevel_Id] ,
[g].[AveragePrice] ,
[g].[Price06155] ,
[h].[#a.Id] ,
[h].[#a.TypeId] ,
[h].[#a.IntId] ,
[h].[#a.Emitter_Id] ,
[h].[#a.FinToolGroup_Id] ,
[h].[#a.FinToolType_Id] ,
[h].[#a.Name] ,
[h].[#a.FullName] ,
[h].[#a.Currency_Id] ,
[h].[#a.Nominal] ,
[h].[#a.Version] ,
ROW_NUMBER() OVER ( ORDER BY [h].[#a.Name] ASC ) AS [RowNumber0]
FROM ( SELECT [i].[Id] ,
181 AS [TypeId] ,
[i].[Date] ,
[i].[MarketPlace_Id] ,
[i].[FinTool_Id] ,
[i].[TransactionCount] ,
[i].[CountPerTransaction] ,
[i].[TradeVolume] ,
[i].[TradeVolumeCurrency_Id] ,
[i].[IsPercent] ,
[i].[FinToolCurrency_Id] ,
[i].[QuotationSheetLevel_Id] ,
[i].[AveragePrice] ,
[i].[Price06155]
FROM [dbo].[RegMarketPrice] [i]
) [g]
INNER JOIN ( SELECT [j].[Id] AS [#a.Id] ,
146 AS [#a.TypeId] ,
[j].[IntId] AS [#a.IntId] ,
[j].[Emitter_Id] AS [#a.Emitter_Id] ,
[j].[FinToolGroup_Id] AS [#a.FinToolGroup_Id] ,
[j].[FinToolType_Id] AS [#a.FinToolType_Id] ,
[j].[Name] AS [#a.Name] ,
[j].[FullName] AS [#a.FullName] ,
[j].[Currency_Id] AS [#a.Currency_Id] ,
[j].[Nominal] AS [#a.Nominal] ,
[j].[Version] AS [#a.Version]
FROM [dbo].[FinTool] [j]
) [h] ON ( [g].[FinTool_Id] = [h].[#a.Id] )
)
SELECT [a].[Id] ,
[a].[Date] ,
[a].[MarketPlace_Id] ,
[a].[FinTool_Id] ,
[a].[TransactionCount] ,
[a].[CountPerTransaction] ,
[a].[TradeVolume] ,
[a].[TradeVolumeCurrency_Id] ,
[a].[IsPercent] ,
[a].[FinToolCurrency_Id] ,
[a].[QuotationSheetLevel_Id] ,
[a].[AveragePrice] ,
[a].[Price06155] ,
[a].[#a.Name] ,
[a].[#b.Name] ,
[a].[#c.SysName] ,
[a].[#d.SysName] ,
[b].[#e.Name],
a.[RowNumber0]
FROM ( SELECT [c].[Id] ,
[c].[TypeId] ,
[c].[Date] ,
[c].[MarketPlace_Id] ,
[c].[FinTool_Id] ,
[c].[TransactionCount] ,
[c].[CountPerTransaction] ,
[c].[TradeVolume] ,
[c].[TradeVolumeCurrency_Id] ,
[c].[IsPercent] ,
[c].[FinToolCurrency_Id] ,
[c].[QuotationSheetLevel_Id] ,
[c].[AveragePrice] ,
[c].[Price06155] ,
[c].[#a.Id] ,
[c].[#a.TypeId] ,
[c].[#a.IntId] ,
[c].[#a.Emitter_Id] ,
[c].[#a.FinToolGroup_Id] ,
[c].[#a.FinToolType_Id] ,
[c].[#a.Name] ,
[c].[#a.FullName] ,
[c].[#a.Currency_Id] ,
[c].[#a.Nominal] ,
[c].[#a.Version] ,
[d].[#b.Id] ,
[d].[#b.TypeId] ,
[d].[#b.IntId] ,
[d].[#b.Name] ,
[d].[#b.IsFreeBroker] ,
[d].[#b.Currency_Id] ,
[d].[#b.Version] ,
[e].[#c.Id] ,
[e].[#c.TypeId] ,
[e].[#c.SysName] ,
[e].[#c.RuName] ,
[e].[#c.EnName] ,
[e].[#c.Version] ,
[f].[#d.Id] ,
[f].[#d.TypeId] ,
[f].[#d.SysName] ,
[f].[#d.RuName] ,
[f].[#d.EnName] ,
[f].[#d.Version],
c.[RowNumber0]
FROM ( SELECT * FROM OrderQuery
) [c]
INNER JOIN ( SELECT [k].[Id] AS [#b.Id] ,
161 AS [#b.TypeId] ,
[k].[IntId] AS [#b.IntId] ,
[k].[Name] AS [#b.Name] ,
[k].[IsFreeBroker] AS [#b.IsFreeBroker] ,
[k].[Currency_Id] AS [#b.Currency_Id] ,
[k].[Version] AS [#b.Version]
FROM [dbo].[MarketPlace] [k]
) [d] ON ( [c].[MarketPlace_Id] = [d].[#b.Id] )
INNER JOIN ( SELECT [l].[Id] AS [#c.Id] ,
109 AS [#c.TypeId] ,
[l].[SysName] AS [#c.SysName] ,
[l].[RuName] AS [#c.RuName] ,
[l].[EnName] AS [#c.EnName] ,
[l].[Version] AS [#c.Version]
FROM [dbo].[Currency] [l]
) [e] ON ( [c].[TradeVolumeCurrency_Id] = [e].[#c.Id] )
INNER JOIN ( SELECT [m].[Id] AS [#d.Id] ,
109 AS [#d.TypeId] ,
[m].[SysName] AS [#d.SysName] ,
[m].[RuName] AS [#d.RuName] ,
[m].[EnName] AS [#d.EnName] ,
[m].[Version] AS [#d.Version]
FROM [dbo].[Currency] [m]
) [f] ON ( [c].[FinToolCurrency_Id] = [f].[#d.Id] )
WHERE ( [c].[RowNumber0] BETWEEN 6315 AND 6355 )
) [a]
LEFT OUTER JOIN ( SELECT [n].[Id] AS [#e.Id] ,
178 AS [#e.TypeId] ,
[n].[IntId] AS [#e.IntId] ,
[n].[MarketPlace_Id] AS [#e.MarketPlace_Id] ,
[n].[Name] AS [#e.Name] ,
[n].[Version] AS [#e.Version]
FROM [dbo].[QuotationSheet] [n]
) [b] ON ( [a].[QuotationSheetLevel_Id] = [b].[#e.Id] )
As you can see, if we use ... row_number() over ( order by ... ) then we dont need to sort the resultset any more. We just use the [c].[RowNumber0] BETWEEN 6315 AND 6355 - and it is already sorted earlier when numbered
Execution plan is attached
Alex (Xtensive) wrote:
LOL (about SQL Server "cleverness"):
1) SQL standard implies result is either explicitly sorted, or order isn't predictable. That's why we explicitly apply it for the outermost SELECT (in fact, we don't eliminate ORDER BY there).
2) Why it sorts already sorted result at all? It's absolutely clear there is the same order.
So I suspect this is purely SQL Server specific case. I'll notify you here when this is investigated.
Alex (Xtensive) wrote:
Just looked up the plan - btw, it's good and correct:
-
Additional Sorts there are used on latest stages after hash\merge joins. Obviously, this is necessary in this case. This operation is performed on relatively small result set (after .Skip(...).Take(...)), so it is cheap, and its nearly zero cost proves this.
-
As far as I can judge, Filter and Top operations are located in correct places, their cost is 0% as well.
-
The most costly operation is Key Lookup (cost = 87%) appeared as result of joining PK to the result (= get the whole row of data by its PK extracted from secondary index), which must be correct as well - at least, this is very frequent case.
So it's interesting to see the similar plan for case with CTE - IMHO, the plan is nearly ideal, if tables are large.
