SQL Server中的慢速两张表查询 [英] Slow two-table query in SQL Server

问题描述

我正在处理的应用程序会生成一个类似于以下内容的SQL查询:

The application I work on generates an SQL query somewhat like this:

Select 
    VISIT_VIEW.VISIT_ID, VISIT_VIEW.PATIENT_ID, VISIT_VIEW.MRN_ID, 
    VISIT_VIEW.BILL_NO, INSURANCE.INS_PAYOR'
FROM 
    'VISIT_VIEW 
LEFT JOIN 
    INSURANCE ON VISIT_VIEW.visit_id = INSURANCE._fk_visit '
WHERE 
    'VISIT_VIEW.VISIT_ID IN (1002, 1003, 1005, 1006, 1007, 1008, 1010, 1011, <...>, 1193, 1194, 1195, 1196, 1197, 1198, 1199)'

<...>代表一长串ID.列表的大小取决于上一个查询的结果，并取决于为生成该查询而选择的参数.

The <...> represents a long list of ids. The size of the list depends on the results of a previous query, and in turn on the parameters selected to generate that query.

ID列表的长度可以从100个项目到2000年以上的任何地方.

The list of IDs can be anywhere from 100 items long to above 2000.

INSURANCE表很大，超过900万行.访问表也很大，但不那么大.

The INSURANCE table is large, over 9 million rows. The visit table is also large, but not quite as large.

随着ID数量的增加，持续时间从不到一秒急剧增加到超过15分钟.增加开始于大约175个id.

As the number of IDs goes up there is a fairly sharp increase from a duration of less than a second to over 15 minutes. The increase starts somewhere around 175 ids.

如果更改了用于生成查询的参数，从而未选择INS_PAYOR列，因此没有左连接，则查询将在不到一秒钟的时间内运行，即使列表中有2000多个ID.

If the parameters used to generate the query are changed so that the INS_PAYOR column is not selected, and thus there is no left join, the query runs in less than a second, even with over 2000 items in the list of IDs.

执行计划表明，查询时间的97％专门用于INSURANCE表上的集群查找.

The execution plan shows that 97% of the query time is devoted to a clustered seek on the INSURANCE table.

如何重新处理此查询以减少恐怖的延迟以获得相同的结果?

How can I rework this query to get the same results with a less horrific delay?

请记住，SQL是由代码而不是手工生成的.它由字段列表(知道哪个字段属于哪个表)和要检查的主表中的ID列表生成.我确实可以访问用于生成查询的代码，并且可以更改它，前提是查询的最终结果完全相同.

Do remember that the SQL is being generated by code, not by hand. It is generated from a list of fields (with knowledge of which field belongs to which table) and a list of IDs in the primary table to check. I do have access to the code that does the query generation, and can change it provided that the ultimate results of the query are exactly the same.

谢谢

推荐答案

使用以下方法查看是否有任何改进...

See if you see any improvements using the following...

IF OBJECT_ID('tempdb..#VisitList', 'U') IS NOT NULL 
DROP TABLE #VisitList;

CREATE TABLE #VisitList (
    VISIT_ID INT NOT NULL PRIMARY KEY
    );

INSERT #VisitList (VISIT_ID) VALUES (1002),(1003),(1005),(1006),(1007),(1008),(1010),(1011),(<...>),(1193),(1194),(1195),(1196),(1197),(1198),(1199);

SELECT 
    vv.VISIT_ID, 
    vv.PATIENT_ID, 
    vv.MRN_ID, 
    vv.BILL_NO, 
    ix.INS_PAYOR
FROM 
    VISIT_VIEW vv
    JOIN #VisitList vl
        ON vv.VISIT_ID = vl.VISIT_ID
    CROSS APPLY (
                SELECT TOP 1
                     i.INS_PAYOR
                FROM 
                    INSURANCE i
                WHERE 
                    vv.visit_id=i._fk_visit
                ) ix;

这篇关于SQL Server中的慢速两张表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！