主页 / dba / 问题 / 264095
Accepted
Kirk Saunders
Asked: 2020-04-02 11:42:14 +0800 CST

在子表中查找带有 WHERE 条件的顶级记录的方法

  • 772

设置

最近，我对返回患者最近使用的处方产品的 SQL 查询进行了优化。原始查询通过 3 个嵌套CTE的 's 处理此问题，并且在迁移到新服务器后性能非常差。我们没有尝试使原始版本与服务器调整或索引更改一起工作，而是使用ROW_NUMBER()分区修改了查询。这使最终输出中大约 215,000 行的查询执行时间从大约 10 分钟缩短到大约 6 秒。

问题

我正在寻找是否有比我重写它的方式更好的方法，因为在我看来，这几乎像是一个 hack。我只是想看看这是否仅仅因为我们的数据集的大小而运作良好，或者这是否真的是一个很好的解决方案。我假设我们宁愿使用某种类型的解决方案，该解决方案利用几个不同JOIN的 s 来找到这种信息，而不是使用ROW_NUMBER()and a CTE。

额外细节

为了这个问题，我将在我们的数据库中省略某些不太理想的细节（复合主键，使用VarChar(1)而不是 aBIT等），以便更好地解决问题的根源。

我们在范围内有 4 个表：

  • Patient- 我们只打算从这个表中返回标识列
  • OrderHeader- 保存有关包裹的信息，例如它要去的地址。并且是 的父记录OrderDetail。Patient该表通过列绑定PatientID。
  • OrderDetail- 建立关系，OrderHeader并Product指明哪些产品在发货时放入实际包装盒中
  • Product- 持有产品清单

我们需要一个查询，它为每个患者返回顶部OrderDetail记录，顶部OrderHeader记录，其中Product记录与记录绑定OrderDetail= Product.IsRx1。

不幸的是，我们不能只获得where = 1的MAX值，因为它可能不属于. 我们也不能依赖value ，因为它不能保证有记录，也不能保证那里的任何记录都有 a = 1OrderDetail.IDProduct.IsRxOrderDetail.IDMAXOrderHeader.IDMAXOrderHeader.IDOrderDetailProduct.IsRx

我通过以下查询解决了这个问题：

WITH CTE
(
    PatientID,
    OrderHeaderID,
    OrderDetaillID,
    ProductNDCCode,
    ProductNDCDescription,
    RowNumber
)
AS
(
    SELECT P.ID AS 'PatientID',
    H.ID AS 'OrderHeaderID',
    D.ID AS 'OrderDetaillID',
    P.NDCCode AS 'ProductNDCCode',
    P.NDCDescription AS 'ProductNDCDescription',
    ROW_NUMBER() 
    OVER
    (
    PARTITION BY P.ID
    ORDER BY 
        P.ID ASC, --This part in the ORDER BY may not be needed, I apologize if it is unnecessary
        SH.OrderHdrID DESC, 
        SD.OrderDtlID DESC
    ) AS 'RowNumber'
    FROM Patient P
        INNER JOIN OrderHeader H
            ON P.ID = H.PatientID
        INNER JOIN OrderDetail D
            ON H.ID = D.OrderHeaderID
        INNER JOIN Product PR
            ON PR.ID = D.ProductID
    WHERE PR.IsRx = 1
)
SELECT PatientID,
OrderHeaderID,
OrderDetaillID,
ProductNDCCode,
ProductNDCDescription
FROM CTE
WHERE RowNumber = 1

我可以获得的指导或专业知识将非常有帮助。

sql-server t-sql

1 个回答

  1. Best Answer

    我不会认为这是黑客行为。包括 ROW_NUMBER 在内的窗口函数相当有效，并且通常执行等于或优于替代方法。在 SQL Server 的新实例上，我假设 FIRST_VALUE 的性能会更高，但尚未具体确认这一点。

    注意：我已经看到 LAST_VALUE 产生不正确的结果，建议不要使用它，而是选择使用 FIRST_VALUE 和相反的排序。

    但是，请注意。如果您的订购标准不够具体，无法生成唯一的“最佳”记录（例如，假设您省略了 OrderDtlID，则该订单的任何详细记录都可能是第一个），那么选择的项目在功能上是随机的（或者，更准确地说，基于在许多情况下您既不容易发现也无法控制的隐藏变量）。如果您从 ROW_NUMBER 更改为 RANK 并且您的随机性发生了变化（您开始获得不同数量的结果），这是一个很好的问题指标。同样值得指出的是，这可能是其他选择最新/最佳记录的方法的问题，而不仅仅是 ROW_NUMBER。

    即使您对选择哪个不关心，这种查询的结果也可能无法重复，尽管您可能不会立即看到它（隐藏变量在每次查询后通常不会改变）。它可能会在以后导致故障排除问题，并且如果您运行任何类型的并行环境 (dev/qa)，则无法在两者之间进行准确比较。

    • 1

相关问题