时间戳

首页 > 解决方案 > 如何加快使用 SELECT 中的子查询来计算值的查询？

sql - 如何加快使用 SELECT 中的子查询来计算值的查询？

问题描述

我有一个查询，它查找给定患者的信息，然后根据一些相关表中的数据计算更多信息。一些计算字段与相同的项目相关。例如，“l_purchase_date”和“l_data”都引用数据库中的相同条目。查询正在运行，但速度非常慢。我相信必须有一些更快的方法来做到这一点。

SELECT p.id AS 'patient_id', p.first_name AS 'f_name', p.last_name AS 'l_name', p.date_of_birth AS 'dob', p.email AS 'email_address', p.street1 AS 'street1', p.street2 AS 'street2', p.street3 AS 'street3', p.city AS 'city', p.[state] AS 'state', p.zip AS 'zip1',
        p.phone1 AS 'primary_phone',
        CASE
            WHEN p.phone1_type = 'cell' THEN p.phone1
            WHEN p.phone2_type = 'cell' THEN p.phone2
            WHEN p.phone3_type = 'cell' THEN p.phone3
            WHEN p.phone4_type = 'cell' THEN p.phone4
            ELSE NULL
        END AS 'mobile_phone',
        'test_date' = (SELECT MAX(test_date)
                       FROM tbl_appointment sa
                           JOIN tbl_hearing_test t ON sa.id = t.appt_id
                       WHERE sa.patient_id = p.id AND t.test_date <= GETDATE()),
        'l_purchase_date' = (SELECT TOP(1) sili.[date]
                             FROM tbl_invoice_line_items sili
                                 JOIN tbl_invoices si ON si.id = sili.invoice_id
                             WHERE si.patient_id = p.id AND sili.[type] = 'hearing_aid' AND sili.side = 'left' AND sili.date <= GETDATE()
                             ORDER BY sili.[date] DESC),
        'r_purchase_date' = (SELECT TOP(1) sili.[date]
                             FROM tbl_invoice_line_items sili
                                 JOIN tbl_invoices si ON si.id = sili.invoice_id
                             WHERE si.patient_id = p.id AND sili.[type] = 'hearing_aid' AND sili.side = 'right' AND sili.date <= GETDATE()
                             ORDER BY sili.[date] DESC),
        'l_data' = (SELECT TOP(1) sili.[data]
                    FROM tbl_invoice_line_items sili
                        JOIN tbl_invoices si ON si.id = sili.invoice_id
                    WHERE si.patient_id = p.id AND sili.[type] = 'hearing_aid' AND sili.side = 'left' AND sili.date <= GETDATE()
                    ORDER BY sili.[date] DESC),
        'r_data' = (SELECT TOP(1) sili.[data]
                    FROM tbl_invoice_line_items sili
                        JOIN tbl_invoices si ON si.id = sili.invoice_id
                    WHERE si.patient_id = p.id AND sili.[type] = 'hearing_aid' AND sili.side = 'right' AND sili.date <= GETDATE()
                    ORDER BY sili.[date] DESC),
        p.phone_status AS 'call_status', p.mail_status, p.text_status, p.email_status, p.[type],
        'max_purchase_date' = (SELECT MAX(sili.[date])
                               FROM tbl_invoice_line_items sili
                                   JOIN tbl_invoices si ON si.id = sili.invoice_id
                               WHERE si.patient_id = p.id AND sili.[date] <= GETDATE()),
        'last_appt_date' = (SELECT TOP(1) sa.[date]
                            FROM tbl_appointment sa
                            WHERE sa.patient_id = p.id AND sa.[date] <= GETDATE()
                            ORDER BY sa.[date] DESC),
        'last_appt_status' = (SELECT TOP(1) sa.[status]
                              FROM tbl_appointment sa
                              WHERE sa.patient_id = p.id AND sa.[date] <= GETDATE()
                              ORDER BY sa.[date] DESC),
        'last_appt_type' = (SELECT TOP(1) sa.[type]
                            FROM tbl_appointment sa
                            WHERE sa.patient_id = p.id AND sa.[date] <= GETDATE()
                            ORDER BY sa.[date] DESC),
        'next_appt_date' = (SELECT TOP(1) sa.[date]
                            FROM tbl_appointment sa
                            WHERE sa.patient_id = p.id AND sa.[date] > GETDATE()
                            ORDER BY sa.[date]),
        'next_appt_status' = (SELECT TOP(1) sa.[status]
                              FROM tbl_appointment sa
                              WHERE sa.patient_id = p.id AND sa.[date] > GETDATE()
                              ORDER BY sa.[date]),
        'next_appt_type' = (SELECT TOP(1) sa.[type]
                            FROM tbl_appointment sa
                            WHERE sa.patient_id = p.id AND sa.[date] > GETDATE()
                            ORDER BY sa.[date])
    FROM tbl_patient p
    GROUP BY p.id, p.first_name, p.last_name, p.date_of_birth, p.email, p.street1, p.street2, p.street3, p.city, p.[state], p.zip, p.phone1, p.phone1_type, p.phone2, p.phone2_type, p.phone3, p.phone3_type, p.phone4, p.phone4_type, p.phone_status, p.mail_status, p.text_status, p.email_status, p.[type]

相关项目：

如果我可以将这些行中的每一行中的查询字段组合成一个查找，我认为它将大大加快这个查询的速度。提前感谢您的任何建议。

标签： sqlsql-server

解决方案

我会考虑建立一个临时表来收集一些必要的数据。看起来你在每个子查询中使用了很多相同的数据。构建一个临时表以收集该数据，然后加入临时表而不是多个子查询。

推荐阅读