Оптимизация left join запроса (mssql) для больших таблиц

Question

Как можно оптимизировать ниже приведенный запрос? Кол-во записей в таблице > 14 миллионов.

Пример исходных данных для 'XVG/BTC', 'XRP/BTC', 'XMR/BTC' за период с 1 по 3 июля доступны тут

CREATE VIEW [dbo].[v_agg_day_trades] WITH SCHEMABINDING
AS
SELECT 
        LEFT(a.pair, CHARINDEX('/', a.pair)-1) Base,
        SUBSTRING(a.pair, CHARINDEX('/', a.pair)+1, 20) Quote,      
        a.pair as Pair,
        CAST(a.dt AS DATE) [date], 
        ROUND(AVG(100 * (CASE WHEN a.price > b.price THEN a.price / b.price - 1 WHEN a.price < b.price THEN b.price / a.price - 1 ELSE null END )), 8) PriceStep, 
        ROUND(MIN(a.price), 8) MinPrice,
        ROUND(MAX(a.price), 8) MaxPrice,
        ROUND(AVG(a.price), 8) AvgPrice,
        SUM(a.amount) BaseDayVolume,
        AVG(a.amount) BaseAvgVolume,
        MIN(a.amount) BaseMinVolume,
        MAX(a.amount) BaseMaxVolume,
        SUM(a.cost) QuoteDayVolume,
        AVG(a.cost) QuoteAvgVolume,
        MIN(a.cost) QuoteMinVolume,
        MAX(a.cost) QuoteMaxVolume,
        COUNT(*) Transactions
FROM dbo.trades a WITH(NOLOCK, NOWAIT) LEFT HASH JOIN dbo.trades b WITH(NOLOCK, NOWAIT) ON a.pair = b.pair AND a.tid + 1 = b.tid  AND a.side != b.side
GROUP BY 
        LEFT(a.pair, CHARINDEX('/', a.pair)-1),
        SUBSTRING(a.pair, CHARINDEX('/', a.pair)+1, 20),        
        a.pair, 
        cast(a.dt as date)

GO

CREATE TABLE [dbo].[trades](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [pair] [nvarchar](20) NOT NULL,
    [dt] [datetime2](7) NOT NULL,
    [ts] [bigint] NOT NULL,
    [tid] [int] NOT NULL,
    [side] [nvarchar](20) NOT NULL,
    [price] [float] NULL,
    [amount] [float] NULL,
    [cost] [float] NULL,
    [fee] [float] NULL
) ON [PRIMARY]
GO


USE [tb5]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [ClusteredIndex-20200729-004955]    Script Date: 8/1/2020 09:54:42 ******/
CREATE CLUSTERED INDEX [ClusteredIndex-20200729-004955] ON [dbo].[trades]
(
    [pair] ASC,
    [ts] ASC,
    [tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Answer 1

Обновил код с учетом рекомендаций @Mike.

CREATE VIEW [dbo].[v_agg_day_trades] WITH SCHEMABINDING
AS
WITH q  AS (

    SELECT LEFT(pair, CHARINDEX('/', pair)-1) Base, SUBSTRING(pair, CHARINDEX('/', pair)+1, 20) Quote, pair, CAST(dt AS DATE) [date], 
        price,  
        LEAD(price,1) OVER(PARTITION BY pair ORDER BY tid) next_price, 
        side,
        LEAD(side,1) OVER(PARTITION BY pair ORDER BY tid) next_side, amount, cost
    FROM dbo.trades WITH(NOLOCK, NOWAIT)
) 
SELECT  Base, Quote, pair, [date],
        ROUND(MIN(q.price), 8) MinPrice,
        ROUND(MAX(q.price), 8) MaxPrice,
        ROUND(AVG(q.price), 8) AvgPrice,
        ROUND(AVG(CASE WHEN NOT (side = next_side OR price = next_price) THEN ROUND(100*ABS(price/next_price - 1),4) ELSE NULL END), 4) priceStep, 
        SUM(q.amount) BaseDayVolume,
        AVG(q.amount) BaseAvgVolume,
        MIN(q.amount) BaseMinVolume,
        MAX(q.amount) BaseMaxVolume,
        SUM(q.cost) QuoteDayVolume,
        AVG(q.cost) QuoteAvgVolume,
        MIN(q.cost) QuoteMinVolume,
        MAX(q.cost) QuoteMaxVolume
FROM q
GROUP BY Base, Quote, pair, [date]

/****** Object:  Index [NonClusteredIndex-20200801-213918]    Script Date: 8/1/2020 21:56:38 ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20200801-213918] ON [dbo].[trades]
(
    [pair] ASC,
    [tid] ASC
)
INCLUDE([side],[price],[dt],[ts],[amount],[cost],[fee]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

/****** Object:  Index [ClusteredIndex-20200729-004955]    Script Date: 8/1/2020 21:57:28 ******/
CREATE CLUSTERED INDEX [ClusteredIndex-20200729-004955] ON [dbo].[trades]
(
    [pair] ASC,
    [ts] ASC,
    [tid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Ситуация намного улучшилась, но для больших данных (145 890 265 строк), запрос медленный.

БЛОГ НА HUSL

Оптимизация left join запроса (mssql) для больших таблиц

Ответы (1 шт):