主页 / 问题 / 1160837
Accepted
Yuriy Tigiev
Asked:2020-08-01 13:57:47 +0000 UTC2020-08-01 13:57:47 +0000 UTC

大表的左连接查询优化 (mssql)

  • 772

如何优化以下查询？表中的记录数 > 1400 万。

此处提供了 7 月 1 日至 3 日期间“XVG/BTC”、“XRP/BTC”、“XMR/BTC”的初始数据示例

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

在此处输入图像描述

sql-server

1 个回答

  1. Best Answer

    更新了代码以反映 @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 行），查询速度很慢。

    在此处输入图像描述

    • 0

相关问题