时间戳

首页 > 解决方案 > SQL Server：性能问题：WHERE 子句中的 OR 语句替换

sql-server - SQL Server：性能问题：WHERE 子句中的 OR 语句替换

问题描述

我只想基于列select的表中的记录。应该在另一个名为 的表中。但是，两个表（客户端 1 和客户端 2）中当前都有 2 个，它们都有不同的.StockPostingDatePostingDateInitDateInitClientclientInitDate

使用下面的代码，我得到了我目前所需要的，基于下面还包含的示例数据。但是，出现了两个问题，首先是基于数百万条记录，查询花费的时间太长（几小时）。其次，每次包含新客户时，它都不是动态的。

解决性能问题的一个潜在选择是编写两个单独的查询，一个用于客户端 1，一个用于客户端 2，UNION中间有一个。不幸的是，这不够动态，因为可能有多个客户端。

    SELECT      
        Material
        ,Stock
        ,Stock.PostingDate
        ,Stock.Client
    FROM Stock
    LEFT JOIN (SELECT InitDate FROM InitClient where Client = 1) C1 ON 1=1
    LEFT JOIN (SELECT InitDate FROM InitClient where Client = 2) C2 ON 1=1  
    WHERE   
        (
            (Stock.Client = 1 AND Stock.PostingDate > C1.InitDate) OR
            (Stock.Client = 2 AND Stock.PostingDate > C2.InitDate)
        )

样本数据集：

CREATE TABLE InitClient
(
    Client  varchar(300),
    InitDate    date
);
INSERT INTO InitClient (Client,InitDate)
VALUES
    ('1', '5/1/2021'),
    ('2', '1/31/2021');
SELECT * FROM InitClient

CREATE TABLE Stock 
(
    Material    varchar(300),
    PostingDate varchar(300),
    Stock   varchar(300),
    Client  varchar(300)
);
INSERT INTO Stock (Material,PostingDate,Stock,Client)
VALUES
    ('322', '1/1/2021', '5', '1'),
    ('101', '2/1/2021', '5', '2'),
    ('322', '3/2/2021', '10', '1'),
    ('101', '4/13/2021', '5', '1'),
    ('400', '5/11/2021', '170', '2'),
    ('401', '6/20/2021', '200', '1'),
    ('322', '7/20/2021', '160', '2'),
    ('400', '8/9/2021', '93', '2');
SELECT * FROM Stock

期望的结果，但随后使用 OR 语句的替代品来提高性能：

| Material | PostingDate | Stock | Client |
|----------|-------------|-------|--------|
| 322      | 1/1/2021    | 5     | 1      |
| 101      | 2/1/2021    | 5     | 2      |
| 322      | 3/2/2021    | 10    | 1      |
| 101      | 4/13/2021   | 5     | 1      |
| 400      | 5/11/2021   | 170   | 2      |
| 401      | 6/20/2021   | 200   | 1      |
| 322      | 7/20/2021   | 160   | 2      |
| 400      | 8/9/2021    | 93    | 2      |

如果在上面的代码中可以替代以保持性能，同时使其动态化，有什么建议吗？

标签： sql-serversql-server-2012query-optimization

解决方案

您可以对这个查询进行相当多的优化。

  • 首先，这两个LEFT JOINs 基本上只是半连接，因为您实际上并没有从它们返回任何结果。所以我们可以把它们变成一个单一的EXISTS.
  • 您还将获得到 的隐式转换int，因为Clientisvarchar和1,2is an int。因此，将其更改为'1','2'，或者您可以更改列类型。
  • PostingDate也是varchar，那应该是真的date
SELECT      
    s.Material
    ,s.Stock
    ,s.PostingDate
    ,s.Client
FROM Stock s
WHERE s.Client IN ('1','2')
  AND EXISTS (SELECT 1
    FROM InitClient c
    WHERE s.PostingDate > c.InitDate
      AND c.Client = s.Client
);
  • 接下来，您要查看索引。对于此查询（不考虑正在运行的任何其他查询），您可能需要以下索引（删除INCLUDE聚集索引）
InitClient (Client, InitDate)

Stock (Client) INCLUDE (PostingDate, Material, Stock)
  • 即使使用这些索引，您也可能会在 上进行扫描Stock，因为IN功能类似于OR. 这并不总是发生，值得检查。如果是这样，您可以改写它以使用UNION ALL
SELECT      
    s.Material
    ,s.Stock
    ,s.PostingDate
    ,s.Client
FROM (
    SELECT *
    FROM Stock s
    WHERE s.Client = '1'
    UNION ALL
    SELECT *
    FROM Stock s
    WHERE s.Client = '2'

) s
WHERE EXISTS (SELECT 1
    FROM InitClient c
    WHERE s.PostingDate > c.InitDate
      AND c.Client = s.Client
);

db<>小提琴

推荐阅读