How to unravel complex query?

Question

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.

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())));

Answer 1

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

1. You can try changing the "allow duplicates" to unique. If you don't have any duplicates it should work.
2. Pick natural primary keys for every table if possible.

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.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

Thanks for the advice.

I think I'll just change one thing at a time, check the execution speed, and see if the simple things make a difference first.

Would it make much difference to have the table relationships defined in the back end?

At the moment there are only two tables defined there but there are many more related tables than that.

Answer 3

I would start by reformatting the mare's nest. To my eye, the best job is done at https://codebeautify.org/sqlformatter

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 4

What is the best way to unravel this mess? It looks like many tables need redesign, which will affect forms, macros and other queries.

Hi All...,

It must have taken quite a long time to construct such a query. Compliments to the designer for his patience.

In contrast to Bill I am in favour of artificial keys (Autonumbers) as primary key for (almost) all tables. One reason is that the retrieval of data is faster for shorter keys, and autonumbers are almost the shortest. Another reason is that for joining, you only have one field with a naming convention related to the table, and these "FROM-constructs" can automatically be generated by only knowing the relation between two tables.

The complicated part is the joining of the tables on all kinds of fields. Most of these field are "coded" fields (to make the joining field smaller?) which means that besides the normal names for an entity, you also need all kind of codes. Using an autonumber, you don't need many codings any more, because the autonumber itself is already the (invisible) coding.

The way I would handle this complex query, is to modify the tables one by one. Investigate what the best primary key would be. If this key is not yet available, add a new one as alternate key. Then use this new key for the joining in all kinds of queries. If all queries are converted, then you can drop the old primary key and replace it by the new key. Do this table by table.

I am afraid it will take a lot of time. Perhaps as much as the original designer needed to construct the query.

Imb.

Answer 5

Would it make much difference to have the table relationships defined in the back end?

Hi All...,

In my applications I do not use relations defined in the BE. I have defined the relations in a meta data table in the FE. An additional relation in the BE would only "protect" against direct manipulation of tables, but users "don't come there".

Imb.

Answer 6

After ensuring all the tables have a unique primary key, the query runs no faster.

I just discovered that six of the joins are joining a field of type Byte to a field of type Long Integer.

Would this slow down the query appreciably?

I guess I should change this and try it out to see if it makes a difference.

Also the way the builder has constructed the SQL seems quite bizarre to me.

My next step will be to re-code it manually and see if that makes a difference.

Joins of Byte to Long Int:

 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

Answer 7

After ensuring all the tables have a unique primary key, the query runs no faster.

Hi All...,

The primary is one of the thing. But you should also look at alternate (or secondary) keys.

When you have a 1:n join, then you refer to an related field in a different table, that can have multiple occurances. When this field is not keyed, then always the whole table has to be searched. Therefore, give FK-fields an alternate key allowing duplicates.

Imb.

Answer 8

Imb - I also favor AutoNumbers as primary keys, but it's too late for that now. GoodNames already has parent/child relationships that are shaky. There would be no way to add foreign keys at this point.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 9

Thanks for the advice.

I think I'll just change one thing at a time, check the execution speed, and see if the simple things make a difference first.

Would it make much difference to have the table relationships defined in the back end?

At the moment there are only two tables defined there but there are many more related tables than that.

Defining relationships in the the back end should make a BIG difference provided the relationships are possible. If you have duplicates in your indices you might not be able to create the joins.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 10

Defining relationships in the the back end should make a BIG difference provided the relationships are possible.

Hi Bill,

In my applications I manage the relations through meta data tables in the FE. How can defining relationships in the back give a BIG difference?

Imb.

Answer 11

Defining relationships in the the back end should make a BIG difference provided the relationships are possible.

Hi Bill,

In my applications I manage the relations through meta data tables in the FE. How can defining relationships in the back give a BIG difference?

Imb.

Relationships are pre-compiled indices with enforced integrity. I'm not sure how your meta data tables work in the FE. I'd love to understand that method.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 12

Defining relationships in the the back end should make a BIG difference provided the relationships are possible.

...

Relationships are pre-compiled indices with enforced integrity. I'm not sure how your meta data tables work in the FE. I'd love to understand that method.

Hi Bill,

I interpreted your "BIG difference" as increases in speed, but I could not relate that directly to relationships. In my opinion speed (keys) and relations (referential integrity) are two different things.

However. referential integrity IS very important, if you can change the PK-values. In my applications I only use Autonumbers as PK, where the change of a PK-value is (almost) impossible.

The advantages of having relations in a meta data table is that in the BeforeUpdate event of a control I can already signal that the new value would give a conflict with keys later on. On defining fields (controls) these relations are automatically stored. I'd like to discuss this way of working, but it is out of the scope of this thread.

Imb.

Answer 13

Imb - A foreign key is indexed. I can't find a way to see it in Access tables, but if you use a SQL Server back end you can see the index.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 14

Suggestions:

1. Shorten the query text by assigning short aliases to each table.

ex:

• QOC_Data --> D
• QOC_RBHAs --> RBHA
• QOC_RHBA_Clinics --> CLINIC
• QOC_Substantiation_Levels_1 = S1
• QOC_Substantiation_Levels_1 = S2

This will make it easier to analyze. Just to be clear, in Design View, right-click, Properties on a table and change the alias to a shorter but descriptive one.

2. Spend some time and make a support form that finds specified text in:

• queries
• form's RecordSource
• controls in forms, ex. combo box RowSource, listbox, those that has SQL
• reports
• modules

eg. programatically open each form and find, say "QOC_Data" in RecordSource
This will make it faster in finding where a table/query is used.

3. Use Excel to quickly build queries to find duplicates.

ex:

• put table name in column A, column name in B
• build query in column C
• SELECT Count(*), <table>.<field> FROM <table> GROUP BY <field> HAVING Count(*)>1;

Then just copy/paste to quickly check if a column is unique.

Or make a support form. Two textbox for TableName and ColumnName. Button1 checks for duplicates, Button2 adds the unique index. Then expand as the need arises.

4. Regarding the query in question. Put an index on:

• both sides of a JOIN, ex. ON QOC_Data.Fund_Srce = QOC_Funding_Source.Complaint_Funding_Source_Code
• where condition, ex. QOC_Data.Rec_Dte>#3/1/2016#

Unique or not depends on the actual use. 

5. Move the core of the SQL to a query. It might make it faster since it pre-processed a bit. Just leave out the WHERE conditions on the form/report using it.

5. Remove functions calls within the query. Apply it when displaying.

• CIDName( [Complaint_CID01] ) AS CID01
• SIDName( [Complaint_CID01], [Complaint_SID01] ) AS SID01

Or make query1 without the functions. Use query1 in query2 then apply the functions.

6. Try running the query on a faster computer with more ram and SSD. Hopefully no changes is even required and just let the next guy worry about it :)

 

Answer 15

Imb - A foreign key is indexed. I can't find a way to see it in Access tables, but if you use a SQL Server back end you can see the index.

Hi Bill,

I do not agree completely.

There is an option (that is set by default) that automatically indexes are created. If that option is not set, then no indexes are created automatically.

The automatic creation of indexes is further dependant on the use of certain (parts of) words. These "token" words are language dependant. For me not enough to go blind for it.

Where we agree is that the a foreign key is "normally" indexed in most cases.

Imb.

Answer 16

Imb - A foreign key is indexed. I can't find a way to see it in Access tables, but if you use a SQL Server back end you can see the index.

Hi Bill,

I do not agree completely.

There is an option (that is set by default) that automatically indexes are created. If that option is not set, then no indexes are created automatically.

The automatic creation of indexes is further dependant on the use of certain (parts of) words. These "token" words are language dependant. For me not enough to go blind for it.

Where we agree is that the a foreign key is "normally" indexed in most cases.

Imb.

I agree with everything you stated. I think we are basically saying the same thing. Access indexes foreign keys if the relationship is established whether through the relationships window or DDL statements such as CREATE TABLE. But it is a hidden index.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 17

But it is a hidden index.

Hi Bill,

I create the indexes through  "CREATE INDEX ...", and they are all visible.

Never create indexes automatically based on occurance of some text in the fieldname. Most of the English word ending on -ty end in Dutch on -heid, and Access interprets it as ending with "id". This has given me a couple of unwanted situations in the early days, thus never again ...

Imb.

Answer 18

Hi AllTheGood...

Do you need all the outputs of this complex query? I had a query that took minutes to run. When I examined the results of that query I found data I didn’t need. I then deleted those parts in Access query editor. It was not more than three columns. Now the same (new) Query takes 5 seconds.

---

Best // Peter Forss Stockholm GMT &#43;1.00