如何解开复杂的查询？ [英] How to unravel complex query?

问题描述

用户说查询需要11-12分钟才能运行，当你看到它（下图）时，你不会感到惊讶。许多正在加入的表没有主键，或者在一个索引字段上加入，当它们显然是唯一的时，表示"允许重复"。许多是
加入非索引字段。解开这个混乱的最佳方法是什么？它看起来像许多表需要重新设计，这将影响形式，宏 和其他查询

User says query takes 11-12 minutes to run, when you see it (below) you wont be surprised. Many of the tables being joined have no primary key or are being joined on an indexed field that says 'duplicates allowed' when they are clearly unique. Many being joined on non-indexed fields. What is the best way to unravel this mess? It looks like many tables need redesign, which will affect forms, macros and other queries.

选择QOC_Data [雅各布法]，QOC_Data.Complaintant，QOC_Data.First_Name，QOC_Data.Last_Name， QOC_Data.DOB，QOC_Data.Case_No，QOC_RBHAs.RBHA_Category_Name，QOC_RHBA_Clinics.Clinic_Name，QOC_Referral_Source.QOC_Referral_Source_Name，[Special Identifier]。[Special
Identifier Name]，QOC_Data.Rec_Dte，QOC_Data.Cse_Clsed_Dte，QOC_Case_Opened_By。[代码打开的情况名]，CIDName（[Complaint_CID01]）AS CID01，SIDName（[Complaint_CID01]，[Complaint_SID01]）AS SID01，DIdName（[Complaint_CID01]，[Complaint_SID01]，[Complaint_DID01]）$ ​​b $ b AS DID01，QOC_Data。[比[关注]，QOC_Substantiation_Levels.Substantiation_Level_Category_Name，CIDName（[Complaint_CID02]）AS CID02，SIDName（[Complaint_CID02]，[Complaint_SID02]）AS SID02，DIdName（[Complaint_CID02]，[Complaint_SID02]，[Complaint_DID02]）
AS DID02， QOC_Data。[特别关注2]，QOC_Substantiation_Levels_1.Substantiation_Level_C ategory_Name，CIDName（[Complaint_CID03]）AS CID03，SIDName（[Complaint_CID03]，[Complaint_SID03]）AS SID03，DIdName（[Complaint_CID03]，[Complaint_SID03]，[Complaint_DID03]）
AS DID03，QOC_Data。[具体问题] 3]，QOC_Substantiation_Levels_2.Substantiation_Level_Category_Name，QOC_Data。[简要投诉说明]，QOC_Data。[投诉结论]，QOC_Corrective_Actions。[纠正措施代码名称]，QOC_Corrective_Actions_1。[纠正
行动代码名称]，QOC_Corrective_Actions_2。[纠正措施代码NAME]

FROM（（（（（[计划类型] INNER JOIN（（（（（（（（（（（（（（QOC_Data INNER JOIN QOC_Funding_Source ON QOC_Data.Fund_Srce = QOC_Funding_Source.Complaint_Funding_Source_Code）INNER JOIN QOC_Behavioral_Health_Categories ON QOC_Data.BX_HX_Cat = QOC_Behavioral_Health_Categories.Bx_Hx_Category_Code）
INNER JOIN QOC_Case_Opened_By ON QOC_Data.Case_Open_By = QOC_Case_Opened_By。[案例由代码开业]）LEFT JOIN QOC_RBHAs ON QOC_Data.RBHA = QOC_RBHAs.RBHA_Code）INNER JOIN QOC_Tre atment_Setting ON QOC_Data.Trtmnt_Setting = QOC_Treatment_Setting。[处理设置
代码]）LEFT JOIN QOC_RHBA_Clinics ON（QOC_Data.Pro_Clnc_Assign = QOC_RHBA_Clinics.ClinicID）AND（QOC_Data.RBHA = QOC_RHBA_Clinics.RBHAID））LEFT JOIN QOC_Disposition ON QOC_Data.Ref_to = QOC_Disposition。[配置代码]）LEFT JOIN QOC_Substantiation_Levels
关于QOC_Data.Complaint_LOS01 = QOC_Substantiation_Levels.Substantiation_Level_Code）LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_1 ON QOC_Data.Complaint_LOS02 = QOC_Substantiation_Levels_1.Substantiation_Level_Code）LEFT JOIN QOC_Substantiation_Levels
AS QOC_Substantiation_Levels_2 ON QOC_Data.Complaint_LOS03 = QOC_Substantiation_Levels_2.Substantiation_Level_Code）LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_3 ON QOC_Data.Complaint_LOS04 = QOC_Substantiation_Levels_3.Substantiation_Level_Code）
LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_4 ON QOC_Data.Complaint_LOS05 = QOC_Substantiation_Levels_4.Substantiation_Level_Code）LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_5 ON QOC_Data.Complaint_LOS06 = QOC_Substantiation_Levels_5.Substantiation_Level_Code）
LEFT JOIN AS QOC_Substantiation_Levels_6 ON QOC_Data.Complaint_LOS07 = QOC_Substantiation_Levels_6.Substantiation_Level_Code ON [计划类型] [计划类型代码] QOC_Substantiation_Levels）= QOC_Data [计划类型]）INNER JOIN QOC_Referral_Source ON QOC_Data.Ref_Srce
= QOC_Referral_Source.QOC_Referral_Source_Code）LEFT JOIN QOC_Corrective_Actions ON QOC_Data.Cor_Acts_Imple_1 = QOC_Corrective_Actions。[解决代码]）LEFT JOIN AS QOC_Corrective_Actions_1 QOC_Corrective_Actions ON QOC_Data.Cor_Acts_Imple_2 = QOC_Corrective_Actions_1。 [纠正
行动代码]）LEFT JOIN QOC_Corrective_Actions AS QOC_Corrective_Actions_2 ON QOC_Data.Cor_Acts_Imple_3 = QOC_Corrective_Actions_2。[纠正措施代码]）INNER JOIN [特殊标识符] ON QOC_Data。[特殊标识符] = [特殊标识符]。[特殊标识符
代码]
$
WHERE（（（[特殊标识符]。[特殊标识符名称]）=""DCS"）AND（（QOC_Data.Rec_Dte ）>＃3/1/2016年＃和（QOC_Data.Rec_Dte）GT =＃6/1 /到2015年＃和（（QOC_Data.Rec_Dte）之间＃1/1/2000＃和现在（）或（QOC_Data.Rec_Dte ）在＃1/1/2000＃And Now（））之间））
OR（（（[特殊标识符]。[特殊标识符名称]）="采用"）AND（（QOC_Data.Rec_Dte）> ＃3/1/2016＃And（QOC_Data.Rec_Dte）> =＃6/1/2015＃And（（QOC_Data.Rec_Dte）介于＃1/1/2000＃和现在（）或（QOC_Data.Rec_Dte）之间＃ 1/1/1000＃And Now（））））
OR（（（[特殊标识符]。[特殊标识符名称]）=" foster"）AND（（QOC_Data.Rec_Dte）>＃3 / 1/2016＃And（QOC_Data.Rec_Dte）> =＃6/1/2015＃And（（QOC_Data.Rec_Dte）介于＃1/1/2000＃和Now（）或（QOC_Data.Rec_Dte）之间＃1/1 / 2000＃And Now（））））;

SELECT QOC_Data.[Jacobs Law], QOC_Data.Complaintant, QOC_Data.First_Name, QOC_Data.Last_Name, QOC_Data.DOB, QOC_Data.Case_No, QOC_RBHAs.RBHA_Category_Name, QOC_RHBA_Clinics.Clinic_Name, QOC_Referral_Source.QOC_Referral_Source_Name, [Special Identifier].[Special Identifier Name], QOC_Data.Rec_Dte, QOC_Data.Cse_Clsed_Dte, QOC_Case_Opened_By.[Case Opened by Code Name], CIDName([Complaint_CID01]) AS CID01, SIDName([Complaint_CID01],[Complaint_SID01]) AS SID01, DIdName([Complaint_CID01],[Complaint_SID01],[Complaint_DID01]) AS DID01, QOC_Data.[Specific Concerns], QOC_Substantiation_Levels.Substantiation_Level_Category_Name, CIDName([Complaint_CID02]) AS CID02, SIDName([Complaint_CID02],[Complaint_SID02]) AS SID02, DIdName([Complaint_CID02],[Complaint_SID02],[Complaint_DID02]) AS DID02, QOC_Data.[Specific Concerns 2], QOC_Substantiation_Levels_1.Substantiation_Level_Category_Name, CIDName([Complaint_CID03]) AS CID03, SIDName([Complaint_CID03],[Complaint_SID03]) AS SID03, DIdName([Complaint_CID03],[Complaint_SID03],[Complaint_DID03]) AS DID03, QOC_Data.[Specific Concerns 3], QOC_Substantiation_Levels_2.Substantiation_Level_Category_Name, QOC_Data.[Brief Complaint Description], QOC_Data.[Complaint Conclusion], QOC_Corrective_Actions.[Corrective Action Code Name], QOC_Corrective_Actions_1.[Corrective Action Code Name], QOC_Corrective_Actions_2.[Corrective Action Code Name]
FROM ((((([Plan Type] INNER JOIN ((((((((((((((QOC_Data INNER JOIN QOC_Funding_Source ON QOC_Data.Fund_Srce = QOC_Funding_Source.Complaint_Funding_Source_Code) INNER JOIN QOC_Behavioral_Health_Categories ON QOC_Data.BX_HX_Cat = QOC_Behavioral_Health_Categories.Bx_Hx_Category_Code) INNER JOIN QOC_Case_Opened_By ON QOC_Data.Case_Open_By = QOC_Case_Opened_By.[Case Opened by Code]) LEFT JOIN QOC_RBHAs ON QOC_Data.RBHA = QOC_RBHAs.RBHA_Code) INNER JOIN QOC_Treatment_Setting ON QOC_Data.Trtmnt_Setting = QOC_Treatment_Setting.[Treatment Setting Code]) LEFT JOIN QOC_RHBA_Clinics ON (QOC_Data.Pro_Clnc_Assign = QOC_RHBA_Clinics.ClinicID) AND (QOC_Data.RBHA = QOC_RHBA_Clinics.RBHAID)) LEFT JOIN QOC_Disposition ON QOC_Data.Ref_to = QOC_Disposition.[Disposition Code]) LEFT JOIN QOC_Substantiation_Levels ON QOC_Data.Complaint_LOS01 = QOC_Substantiation_Levels.Substantiation_Level_Code) LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_1 ON QOC_Data.Complaint_LOS02 = QOC_Substantiation_Levels_1.Substantiation_Level_Code) LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_2 ON QOC_Data.Complaint_LOS03 = QOC_Substantiation_Levels_2.Substantiation_Level_Code) LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_3 ON QOC_Data.Complaint_LOS04 = QOC_Substantiation_Levels_3.Substantiation_Level_Code) LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_4 ON QOC_Data.Complaint_LOS05 = QOC_Substantiation_Levels_4.Substantiation_Level_Code) LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_5 ON QOC_Data.Complaint_LOS06 = QOC_Substantiation_Levels_5.Substantiation_Level_Code) LEFT JOIN QOC_Substantiation_Levels AS QOC_Substantiation_Levels_6 ON QOC_Data.Complaint_LOS07 = QOC_Substantiation_Levels_6.Substantiation_Level_Code) ON [Plan Type].[Plan Type Code] = QOC_Data.[Plan Type]) INNER JOIN QOC_Referral_Source ON QOC_Data.Ref_Srce = QOC_Referral_Source.QOC_Referral_Source_Code) LEFT JOIN QOC_Corrective_Actions ON QOC_Data.Cor_Acts_Imple_1 = QOC_Corrective_Actions.[Corrective Action Code]) LEFT JOIN QOC_Corrective_Actions AS QOC_Corrective_Actions_1 ON QOC_Data.Cor_Acts_Imple_2 = QOC_Corrective_Actions_1.[Corrective Action Code]) LEFT JOIN QOC_Corrective_Actions AS QOC_Corrective_Actions_2 ON QOC_Data.Cor_Acts_Imple_3 = QOC_Corrective_Actions_2.[Corrective Action Code]) INNER JOIN [Special Identifier] ON QOC_Data.[Special Identifier] = [Special Identifier].[Special Identifier Code]
WHERE ((([Special Identifier].[Special Identifier Name])="DCS") AND ((QOC_Data.Rec_Dte)>#3/1/2016# And (QOC_Data.Rec_Dte)>=#6/1/2015# And ((QOC_Data.Rec_Dte) Between #1/1/2000# And Now() Or (QOC_Data.Rec_Dte) Between #1/1/2000# And Now()))) OR ((([Special Identifier].[Special Identifier Name])="Adoption") AND ((QOC_Data.Rec_Dte)>#3/1/2016# And (QOC_Data.Rec_Dte)>=#6/1/2015# And ((QOC_Data.Rec_Dte) Between #1/1/2000# And Now() Or (QOC_Data.Rec_Dte) Between #1/1/2000# And Now()))) OR ((([Special Identifier].[Special Identifier Name])="foster") AND ((QOC_Data.Rec_Dte)>#3/1/2016# And (QOC_Data.Rec_Dte)>=#6/1/2015# And ((QOC_Data.Rec_Dte) Between #1/1/2000# And Now() Or (QOC_Data.Rec_Dte) Between #1/1/2000# And Now())));

推荐答案

您可以通过将嵌套查询保存为查询来获得一些性能，但是就像你的统计ed，索引缺乏很多。

You might pick up some performance by saving the nested queries as queries, but as you stated, the indexing lacks a lot.

1. 您可以尝试更改"允许重复项""独一无二如果你没有任何重复，它应该工作。
   
2. 如果可能的话，为每张桌子选择自然的主键。

如果你对形式很远，我不会开始更改表格设计和报告。你永远不会全力以赴。但关系可能不会太困难。

I wouldn't start changing table design if you are far into forms and reports. You will never sort it all out. But relationships might not be too difficult.

这篇关于如何解开复杂的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！