sql-server - 痛苦的缓慢查询，我有什么选择？

Question

我在医院做了一些 sql 工作（还没有 COVID 病例！）。有一个表格 ，[dbo].A.diagnosis其中包含我们所有患者的所有诊断的历史记录。我不是专家，但桌子……很糟糕。这个古老的软件使用它来处理诊断（除其他外）。该表的工作方式，它有 30+ 列和 300k+ 行，但没有索引（除了它的主键）。每次患者更新他们的诊断时，他们的所有诊断都会重新写入新的diagnosis_date. diagnosis_date存储在数据类型date而不是datetime，但患者一天内多次更新诊断的情况并不少见。

我需要获得一份我们所有目前入院患者的名单，并使其保持合理的最新状态（我认为在过去 24 小时内是合理的，但越早越好）。

我目前最好的查询在运行时间上仍然变化很大，运行时间从 1 到 15(!!!) 分钟不等。这是不可接受的，所以我想知道我的选择是什么来改进它。

数据样本（虚构，仅相关列）：

-- [dbo].A.diagnosis
+------------+----------------+----------------+----------------+-----------------------------+
| patient_id | diagnosis_type | diagnosis_date | diagnosis_code | diagnosis_text              |
+------------+----------------+----------------+----------------+-----------------------------+
| 0369344991 | I              | 2020-01-04     | E669           | Obesity, unspecified        |
| 0369344991 | I              | 2020-01-04     | M545           | Low back pain               |
| 0369344991 | I              | 2020-01-04     | NULL           | NULL                        | -- Separator
| 0369344991 | U              | 2020-01-04     | E669           | Obesity, unspecified        |
| 0369344991 | U              | 2020-01-04     | M545           | Low back pain               |
| 0369344991 | U              | 2020-01-04     | L709           | Acne, unspecified           | -- Updated later that day to add the acne diagnosis
| 0369344991 | U              | 2020-01-04     | NULL           | NULL                        |
| 0369344991 | U              | 2020-01-16     | E669           | Obesity, unspecified        |
| 0369344991 | U              | 2020-01-16     | L709           | Acne, unspecified           |
| 0369344991 | U              | 2020-01-16     | E785           | Hyperlipidemia, unspecified | -- Updated 12 days later, low back pain resolved, added hyperlipidemia
| 0369344991 | U              | 2020-01-16     | NULL           | NULL                        |
+------------+----------------+----------------+----------------+-----------------------------+

-- [dbo].A.patients
+------------+
| patient_id |
+------------+
| 0369344991 |
+------------+

-- [dbo].B.diagnosis_priority
+----------------+--------------------+
| diagnosis_type | diagnosis_priority |
+----------------+--------------------+
| I              | 1                  |
| A              | 2                  |
| U              | 3                  |
| D              | 4                  |
+----------------+--------------------+

查询：

SELECT DISTINCT dx.patient_id -- (decimal(10,0), null)
,       dx.diagnosis_date -- (date, null)
,       dx.diagnosis_code -- (varchar(5), null)
,       dx.diagnosis_text -- (varchar(253, null) 
,       dx.diagnosis_type -- (varchar(1), null)
FROM    [dbo].A.patients -- Starting with a list of our current patients.
JOIN    [dbo].A.diagnosis dx
    ON  [dbo].A.patients.patient_id = dx.patient_id
JOIN    [dbo].B.diagnosis_priority dp 
    ON  dx.diagnosis_type = dp.diagnosis_type
-- This is a table I wrote to help determine which diagnoses are more 'up-to-date' if multiple updates are done on 
-- a single day. The join assigns a priority number to each diagnosis_type as diagnosis_priority.
WHERE   dx.diagnosis_code IS NOT NULL
AND     dx.diagnosis_date = ( -- Trying to get the diagnoses as of the most recent diagnosis date.
            SELECT  MAX(dx_a.diagnosis_date) 
            FROM    [dbo].A.diagnosis dx_a 
            WHERE   dx_a.patient_id = dx.patient_id
            ) 
AND     dp.diagnosis_priority = (  
-- Trying to get the highest priority diagnoses applied on the most recent date.
-- A patient will not get a lower priority diagnosis on a later date, but newer diagnoses will not 
-- necessarily get a higher priority in [dbo].A.diagnosis
            SELECT  MAX(dp_a.diagnosis_priority) 
            FROM    [dbo].A.diagnosis dx_a 
            JOIN    [dbo].B.diagnosis_priority dp_a 
                ON dx_a.diagnosis_type = dp_a.diagnosis_type 
            WHERE dx_a.patient_id = dx.patient_id
            )

我是db_datareaderon的成员[dbo].A，但我是同一服务器上的 on 的db_owner成员。[dbo].B由于上述古老的软件，修改方式[dbo].A.diagnosis功能是不可行的。

如果无法显着改进查询，我想知道我有哪些选项[dbo].B可以维护当前在医院的患者的当前诊断列表。

标签： sql-servertsqloptimizationdatabase-designquery-optimization

Answer 1

将所有数据流式传输到临时表并在临时表上运行查询。

CREATE TABLE #diagnosis_tmp (patient_id decimal(10,0), diagnosis_type varchar(1), diagnosis_date date, diagnosis_code varchar(5), diagnosis_text varchar(253))

INSERT INTO #diagnosis_tmp (patient_id,diagnosis_type,diagnosis_date,diagnosis_code)
SELECT patient_id,diagnosis_type,diagnosis_date,diagnosis_code
FROM [dbo].A.diagnosis
    WHERE diagnosis_code IS NOT NULL

--CREATE INDEX i_patient_date ON #diagnosis_tmp (patient_id,diagnosis_date)