I have a RegMarketPrice table with 4 billion records, and I want select from 312 to 351 record by DO. DO build next quiry:

SELECT  [a].[Id] ,
        [a].[Date] ,
…
        [f].[#e.Name]
FROM    ( SELECT    [g].[Id] ,
                    [g].[TypeId] ,
…
                    [g].[Price06155]
          FROM      ( SELECT TOP 351
                                [h].[Id] ,
                                171 AS [TypeId] ,
                                [h].[Date] ,
                                [h].[MarketPlace.Id] ,
                                [h].[FinTool.Id] ,
                                [h].[TransactionCount] ,
                                [h].[CountPerTransaction] ,
                                [h].[TradeVolume] ,
                                [h].[TradeVolumeCurrency.Id] ,
                                [h].[IsPercent] ,
                                [h].[FinToolCurrency.Id] ,
                                [h].[QuotationSheetLevel.Id] ,
                                [h].[AveragePrice] ,
                                [h].[Price06155] ,
                                ROW_NUMBER() OVER ( ORDER BY [h].[Id] ASC ) AS [RowNumber0]
                      FROM      [dbo].[RegMarketPrice] [h]
                      ORDER BY  [h].[Id] ASC
                    ) [g]
          WHERE     ( [g].[RowNumber0] > 312 )
        ) [a]
        LEFT OUTER JOIN ( SELECT    [i].[Id] AS [#a.Id] ,
                                    144 AS [#a.TypeId] ,
                                    [i].[IntId] AS [#a.IntId] ,
                                    [i].[Name] AS [#a.Name] ,
                                    [i].[IsFreeBroker] AS [#a.IsFreeBroker] ,
                                    [i].[Currency.Id] AS [#a.Currency.Id] ,
                                    [i].[Version] AS [#a.Version]
                          FROM      [dbo].[MarketPlace] [i]
                        ) [b] ON ( [a].[MarketPlace.Id] = [b].[#a.Id] )
        LEFT OUTER JOIN ( SELECT    [j].[Id] AS [#b.Id] ,
                                    133 AS [#b.TypeId] ,
                                    [j].[IntId] AS [#b.IntId] ,
                                    [j].[Emitter.Id] AS [#b.Emitter.Id] ,
                                    [j].[FinToolGroup.Id] AS [#b.FinToolGroup.Id] ,
                                    [j].[FinToolType.Id] AS [#b.FinToolType.Id] ,
                                    [j].[Name] AS [#b.Name] ,
                                    [j].[FullName] AS [#b.FullName] ,
                                    [j].[Currency.Id] AS [#b.Currency.Id] ,
                                    [j].[Nominal] AS [#b.Nominal] ,
                                    [j].[Version] AS [#b.Version]
                          FROM      [dbo].[FinTool] [j]
                        ) [c] ON ( [a].[FinTool.Id] = [c].[#b.Id] )
        LEFT OUTER JOIN ( SELECT    [k].[Id] AS [#c.Id] ,
                                    105 AS [#c.TypeId] ,
                                    [k].[SysName] AS [#c.SysName] ,
                                    [k].[RuName] AS [#c.RuName] ,
                                    [k].[EnName] AS [#c.EnName] ,
                                    [k].[Version] AS [#c.Version]
                          FROM      [dbo].[Currency] [k]
                        ) [d] ON ( [a].[TradeVolumeCurrency.Id] = [d].[#c.Id] )
        LEFT OUTER JOIN ( SELECT    [l].[Id] AS [#d.Id] ,
                                    105 AS [#d.TypeId] ,
                                    [l].[SysName] AS [#d.SysName] ,
                                    [l].[RuName] AS [#d.RuName] ,
                                    [l].[EnName] AS [#d.EnName] ,
                                    [l].[Version] AS [#d.Version]
                          FROM      [dbo].[Currency] [l]
                        ) [e] ON ( [a].[FinToolCurrency.Id] = [e].[#d.Id] )
        LEFT OUTER JOIN ( SELECT    [m].[Id] AS [#e.Id] ,
                                    168 AS [#e.TypeId] ,
                                    [m].[IntId] AS [#e.IntId] ,
                                    [m].[MarketPlace.Id] AS [#e.MarketPlace.Id] ,
                                    [m].[Name] AS [#e.Name] ,
                                    [m].[Version] AS [#e.Version]
                          FROM      [dbo].[QuotationSheet] [m]
                        ) [f] ON ( [a].[QuotationSheetLevel.Id] = [f].[#e.Id] )
ORDER BY [a].[Id] ASC ,
        [b].[#a.Id] ASC ,
        [c].[#b.Id] ASC ,
        [d].[#c.Id] ASC ,
        [e].[#d.Id] ASC ,
        [f].[#e.Id] ASC ;

Query contains paging:

SELECT TOP 351
…
ROW_NUMBER() OVER ( ORDER BY [h].[Id] ASC ) AS [RowNumber0]
…
WHERE     ( [g].[RowNumber0] > 312 )

I can improve executed query plan in 3 times by changing last string on

WHERE     ( [g].[RowNumber0]  between 312 and  351)

Can you explain or improve your paging query?


Updated at 14.07.2010 10:09:56

3 times = 3 times faster? If so, this is really attractive.

The actual execution time is not significantly changed, but the fact-queryplan-cost shows improvement in 3 times.

Perhaps the actual time does not change, because the request is well-paralling, and it just improve processor load.

In any case, I do not understand the fact-queryplan-cost, but it is so. Maybe you will be useful, this information.

This thread was imported from our support forum. The original discussion may contain more detailed answer. Original topic by joshua.

asked Jul 08 '10 at 10:35

Editor's gravatar image

Editor
46141156157


One Answer:

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.

answered Jul 08 '10 at 10:52

Editor's gravatar image

Editor
46141156157

Definitely useful. At least, we plan to test this (after fixing a set of ASAP issues ;) ).

(Jul 08 '10 at 10:52) Alex Yakunin Alex%20Yakunin's gravatar image

We'll definitely study this.

3 times = 3 times faster? If so, this is really attractive.

(Jul 08 '10 at 10:52) Alex Yakunin Alex%20Yakunin's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA