Left Join with OR运行需要很长时间

Question

我花了很长时间编写一个联接，它最终获得了我需要的所有条件，但运行起来需要很长时间。关于如何加快速度有什么想法吗？我在下面附上了一个查询计划的链接，但我不能100%确定如何阅读它并找到查询的瓶颈。

SELECT
A.procedure_code
,a.modifier_one
,A.invoice_id
,A.invoice_service_date
,A.procedure_total_billed_fee
,DATEADD(month, DATEDIFF(month, 0, A.invoice_service_date), 0) AS VisitMonth
,A.facility_name
,A.invoice_insert_date
,DATEADD(month, DATEDIFF(month, 0, A.invoice_insert_date), 0) AS InvoiceInsertMonth
,A.claim_entry_date
,A.claim_last_modify_date
,A.primary_insurance_name
,A.primary_payer_level_three
,A.patient_id
,A.procedure_id
,D.[MedicareRate]
,a.cms_locality_name
FROM
CIPBackUps.cip.[2020_02_05_month_end_services] A
LEFT JOIN (SELECT [MedicareRate],[Locality Name],[Locality Number],HCPCS, [mod] FROM TESTDB.dbo.[medicare2018] WHERE [Medicare Administrative Contractor (MAC)] = '4412')  D 
ON a.cms_locality_number = D.[Locality number] AND 
(
(concat(A.procedure_code,a.modifier_one) = concat(D.HCPCS,d.mod) AND a.modifier_one in ('','TC','26','53') AND procedure_code NOT IN ('99281','99282','99283','99284','99285','99291','99292') ) 
OR (A.procedure_code = D.HCPCS AND a.modifier_one NOT in ('','TC','26','53')
OR (A.procedure_code = D.HCPCS AND (a.modifier_one IS NULL or procedure_code IN ('99281','99282','99283','99284','99285','99291','99292')) )) AND A.procedure_code NOT LIKE 'G%'
)
WHERE 
procedure_total_billed_fee <> 0
AND a.invoice_service_date between '10/01/2018' AND '12/31/2018 23:59:59'

粘贴计划链接

Answer 1

我想您不需要CONCAT。这可以处理CONCAT('123', '45') = CONCAT('12', '345')的情况，但在正确构建的数据库中，a.procedure_code = d.hcpcs AND a.modifier_one = d.mod难道不足够吗？如果这个假设是真的，那么您可以将条件a.procedure_code = d.hcpcs移动到OR之外，查询的下半部分将显示为：

FROM cipbackups.cip.[2020_02_05_month_end_services] a
LEFT JOIN testdb.dbo.[medicare2018] d
ON d.[Medicare Administrative Contractor (MAC)] = '4412'
AND a.cms_locality_number = d.[Locality number] 
AND a.procedure_code = d.hcpcs
AND
(
(
a.modifier_one = d.mod AND 
a.modifier_one IN ('','TC','26','53') AND
a.procedure_code NOT IN ('99281','99282','99283','99284','99285','99291','99292') 
) 
OR
(
a.procedure_code NOT LIKE 'G%' 
AND
(
a.modifier_one NOT IN ('','TC','26','53') OR 
a.modifier_one IS NULL OR
a.procedure_code IN ('99281','99282','99283','99284','99285','99291','99292') 
)
)
)
WHERE a.procedure_total_billed_fee <> 0
AND a.invoice_service_date >= '2018-10-01' AND a.invoice_service_date < '2019-01-01';

为了通过invoice_service_date和procedure_total_billed_fee快速找到A行，您可能需要以下索引：

CREATE INDEX idx1 ON cipbackups.cip.[2020_02_05_month_end_services] 
(
invoice_service_date,
procedure_total_billed_fee,
cms_locality_number,
procedure_code,
modifier_one
);

(如果只有少数行与procedure_total_billed_fee <> 0匹配，则此列可能比invoice_service_date更有选择性，并且您可能希望交换索引中的两列。(

这将立即为您提供cms_locality_number、procedure_code和modifier_one，您希望使用它们来查找D行。为了快速实现这一点，您需要在相关列上建立索引：

CREATE INDEX idx2 ON testdb.dbo.[medicare2018]
(
[Medicare Administrative Contractor (MAC)],
[Locality number],
hcpcs,
mod
);

(如果[Locality number]比[Medicare Administrative Contractor (MAC)]更具选择性，您可能需要交换它们的位置。如果对此不安全，请添加另一个具有交换列的索引，并查看DBMS使用哪个。(

更新：一个额外的想法

AND
(
a.modifier_one NOT IN ('','TC','26','53') OR 
a.modifier_one IS NULL OR
a.procedure_code IN ('99281','99282','99283','99284','99285','99291','99292') 
)

与相同

AND NOT
(
a.modifier_one IN ('','TC','26','53') AND
a.procedure_code NOT IN ('99281','99282','99283','99284','99285','99291','99292') 
)

您可以尝试将前者替换为后者，以使DBMS的优化器清楚地看到一个条件在上

AND
(
a.modifier_one IN ('','TC','26','53') AND
a.procedure_code NOT IN ('99281','99282','99283','99284','99285','99291','99292') 
)

另一个在上

AND NOT
(
a.modifier_one IN ('','TC','26','53') AND
a.procedure_code NOT IN ('99281','99282','99283','99284','99285','99291','99292') 
)

不过，我不知道这是否会有所不同。