SQL Server 利用T-SQL更新功能提高查询效率

6ovsh4lw 于 2023-01-12 发布在其他

关注(0)|答案(1)|浏览(79)

我用一种非常基本且低效的方式编写了这个T-SQL查询：

SELECT
    e.ExchangeId,
    e.ExchangeName,
    s.StockId,
    s.StockName,
    sp.StockDate,
    sp.StockPrice,
    DATEADD(YEAR, -3, sp.StockDate) AS YearTDate,
    (SELECT spm.StockPrice FROM dbo.StockPrices spm WITH(NOLOCK) WHERE (spm.StockId = sp.StockId) AND (spm.StockDate = DATEADD(YEAR, -3, sp.StockDate))) AS YearTPrice,
    (SELECT TOP 1 spm.StockDate FROM dbo.StockPrices spm WITH(NOLOCK) WHERE (spm.StockId = sp.StockId) AND (spm.StockDate = DATEADD(YEAR, -3, sp.StockDate)) ORDER BY spm.StockPriceId) AS LatestDate,
    (SELECT TOP 1 spm.StockPrice FROM dbo.StockPrices spm WITH(NOLOCK) WHERE (spm.StockId = sp.StockId) AND (spm.StockDate = DATEADD(YEAR, -3, sp.StockDate)) ORDER BY spm.StockPriceId) AS LatestPrice,
    ((SELECT TOP 1 spm.StockPrice FROM dbo.StockPrices spm WITH(NOLOCK) WHERE (spm.StockId = sp.StockId) AND (spm.StockDate = DATEADD(YEAR, -3, sp.StockDate)) ORDER BY spm.StockPriceId) - sp.StockPrice) AS PL,
    CASE WHEN sp.StockPrice < (SELECT MIN(spm.StockPrice) FROM dbo.StockPrices spm WITH(NOLOCK) WHERE (spm.StockId = sp.StockId) AND (spm.StockDate BETWEEN DATEADD(YEAR, -3, sp.StockDate) AND DATEADD(DAY, -1, sp.StockDate))) THEN 'Opportunity' ELSE 'None' END AS [Status]
FROM dbo.StockPrices sp WITH(NOLOCK)
INNER JOIN dbo.Stocks s WITH(NOLOCK)
        ON s.StockId = sp.StockId
INNER JOIN dbo.Exchanges e WITH(NOLOCK)
        ON e.ExchangeId = s.ExchangeId
GO

我如何重写这个查询以提高效率？例如，使用WITH关键字或其他一些我可能不知道的特性。

sql-server

来源：https://stackoverflow.com/questions/75083508/leverage-t-sql-newer-capabilities-to-make-query-more-efficient

1条答案

按热度按时间

bvjxkvbb1#

我将开始把你的5个子查询移到一个查询中，在4个TOP (1)查询中，除了一个查询外，所有查询都是按StockPriceId排序的，所以我假设YearTPrice（当前返回任意行）也是如此。
对于MIN值，我使用窗口化的MIN。
我还删除了NOLOCK提示，因为它们显然被滥用了。如果您“msut”（您不需要）需要针对查询中的 * 每个表 * 使用NOLOCK提示，那么请更改事务的隔离级别。

SELECT e.ExchangeId,
       e.ExchangeName,
       s.StockId,
       s.StockName,
       sp.StockDate,
       sp.StockPrice,
       DATEADD(YEAR, -3, sp.StockDate) AS YearTDate,
       spm.StockPrice AS YearTPrice,
       spm.StockDate AS LatestDate,
       spm.StockPrice AS LatestPrice,
       spm.StockPrice - sp.StockPrice AS PL,
       CASE WHEN sp.StockPrice < spm.MinPrice THEN 'Opportunity' ELSE 'None' END AS [Status]
FROM dbo.StockPrices sp
     INNER JOIN dbo.Stocks s ON s.StockId = sp.StockId
     INNER JOIN dbo.Exchanges e ON e.ExchangeId = s.ExchangeId
     --I use an outer apply, as I don't know if a row is guarenteed to be returned
     OUTER APPLY (SELECT TOP (1)
                         dt.StockPrice,
                         dt.StockDate,
                         dt.MinPrice
                  FROM (SELECT ca.StockPriceId,
                               ca.StockPrice,
                               ca.StockDate,
                               MIN(StockPrice) OVER (PARTITION BY ca.StockId) AS MinPrice
                        FROM dbo.StockPrices ca
                        WHERE ca.StockId = sp.StockId
                          AND ca.StockDate = DATEADD(YEAR, -3, sp.StockDate)) dt --This isn't SARGable, so will result in a scan
                  ORDER BY dt.StockPriceId) spm;

当然，这些都是完全未经测试的，因为不存在样本数据，所以我无法知道这会对查询产生多大的影响（甚至影响结果，因为我无法测试），但它确实将5或6次StockPrices扫描减少到了1或2次

赞(0）回复(0）举报 2023-01-12

我来回答

SQL Server 利用T-SQL更新功能提高查询效率

1条答案

相关问题

热门标签

最新问答