Slow report

[nairnmp]

Hi,

I have query that contains one sub-query. When I run the query by itself it takes about 30 seconds to return. The problem is if I make a basic report (no grouping or sorting) around this query it takes 7 minutes to return. Any suggestions?

Thanks,
Mike

 

[Trevil]

Look first at your queries. If you have more than 'x' tables in your queries and are using various selection criteria, there may be a better way to do it. 'x' can vary based on the number of tables, number of total records, indicies and table relationships.

I dealt with 7+ minute report last year. My solution was to create 'qry_Step1' which had ONLY the tables that had selection criteria (or enough tables to provide relationships). Then changed old query to use 'qrl_Step1' (of course don't duplicate the selection criteria).

"Hmmm, it worked when I tested it....

 

[dhookom]

Does your report have [Page] of [Pages]?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[nairnmp]

Trevil I do have a lot of tables in the sub query. I'm not sure that I can cut the number based on the way the database was designed (not by me). Thanks for the suggestion.

 

[nairnmp]

dhookom, No [Page] of [Pages].

 

[Trevil]

How about posting the SQL from your main query and the subquery. I have some code that will generate a database based on the SQL and will play around with it tonight to see if there is something obvious.

"Hmmm, it worked when I tested it....

 

[nairnmp]

Trevil

In the main query Regulator, Org Spons, License Type, and IDENTIFICATION_VALUE are the fields where the criteria changes. Everything else in the where stays the same.

The main query is:

SELECT DISTINCTROW [A Qry LicensesCC].Name, [A Qry LicensesCC].[License No], [A Qry LicensesCC].WORKING_CREDENTIAL_ID, [A Qry LicensesCC].Regulator, [A Qry LicensesCC].[Org Spons], [A Qry LicensesCC].Renewal, [A Qry LicensesCC].Termination, [A Qry LicensesCC].[License Type], IDENTIFICATION_AE.IDENTIFICATION_VALUE AS IDENTIFICATION_VALUE, ORGANIZATION.ORGANIZATION_NAME
FROM ([A Qry LicensesCC] LEFT JOIN (OCCUPIED_POSITION LEFT JOIN ORGANIZATION ON OCCUPIED_POSITION.ORG_ID = ORGANIZATION.ORG_ID) ON [A Qry LicensesCC].PERSON_ID = OCCUPIED_POSITION.PERSON_ID) LEFT JOIN (IDENTIFICATION_AE LEFT JOIN IDENTIFICATION ON IDENTIFICATION_AE.IDENTIFICATION_ID = IDENTIFICATION.IDENTIFICATION_ID) ON ORGANIZATION.ORG_ID = IDENTIFICATION_AE.RECORD_REF_ID
WHERE ((([A Qry LicensesCC].Regulator)="California" Or ([A Qry LicensesCC].Regulator)="Vermont") AND (([A Qry LicensesCC].[Org Spons])="Securities Corp.") AND (([A Qry LicensesCC].[License Type])="Securities") AND ((IDENTIFICATION_AE.IDENTIFICATION_VALUE)="052" Or (IDENTIFICATION_AE.IDENTIFICATION_VALUE)="336") AND ((IDENTIFICATION.IDENTIFICATION_NAME)="Cost Center") AND ((OCCUPIED_POSITION.PRIMARY_CC_FLAG)=Yes));


The Sub Query (A Qry LicensesCC) is:

SELECT working_credential.WORKING_CREDENTIAL_ID, main_goal.MAIN_GOAL_NAME AS [License Type], ORGANIZATION.ORGANIZATION_NAME AS Regulator, associate_credential_ae.CREQ_VALUE AS [Org Spons], IIf([Working_Credential]![registrant_table_id]=1,[ORGANIZATION_1]![ORGANIZATION_NAME],[PERSON_WHOLENAME]) AS Name, working_credential.CREQ_VALUE AS [License No], associate_credential_ae_2.CREQ_VALUE AS Renewal, associate_credential_ae_1.CREQ_VALUE AS Termination, PERSON.PERSON_ID
FROM (ORGANIZATION INNER JOIN (main_goal INNER JOIN (creq_category INNER JOIN ((associate_credential_ae AS associate_credential_ae_2 INNER JOIN ((associate_credential_ae AS associate_credential_ae_1 INNER JOIN creq_sub_category AS creq_sub_category_1 ON associate_credential_ae_1.CREQ_SUB_CATEGORY_ID = creq_sub_category_1.CREQ_SUB_CATEGORY_ID) INNER JOIN ((working_credential LEFT JOIN PERSON ON working_credential.REGISTRANT_RECORD_REF_ID = PERSON.PERSON_ID) LEFT JOIN ORGANIZATION AS ORGANIZATION_1 ON working_credential.REGISTRANT_RECORD_REF_ID = ORGANIZATION_1.ORG_ID) ON associate_credential_ae_1.WORKING_CREDENTIAL_ID = working_credential.WORKING_CREDENTIAL_ID) ON associate_credential_ae_2.WORKING_CREDENTIAL_ID = working_credential.WORKING_CREDENTIAL_ID) INNER JOIN creq_sub_category AS creq_sub_category_2 ON associate_credential_ae_2.CREQ_SUB_CATEGORY_ID = creq_sub_category_2.CREQ_SUB_CATEGORY_ID) ON creq_category.CREQ_CATEGORY_ID = working_credential.CREQ_CATEGORY_ID) ON main_goal.MAIN_GOAL_ID = working_credential.MAIN_GOAL_ID) ON ORGANIZATION.ORG_ID = working_credential.REG_ORG_ID) INNER JOIN (creq_sub_category INNER JOIN associate_credential_ae ON creq_sub_category.CREQ_SUB_CATEGORY_ID = associate_credential_ae.CREQ_SUB_CATEGORY_ID) ON working_credential.WORKING_CREDENTIAL_ID = associate_credential_ae.WORKING_CREDENTIAL_ID
WHERE (((associate_credential_ae_1.CREQ_VALUE) Is Null) AND ((creq_sub_category_1.CREQ_SUB_CATEGORY_NAME)="Termination") AND ((creq_category.CREQ_CATEGORY_NAME)="License") AND ((creq_sub_category.CREQ_SUB_CATEGORY_NAME)="Organization") AND ((creq_sub_category_2.CREQ_SUB_CATEGORY_NAME)="Renewal"))
ORDER BY main_goal.MAIN_GOAL_NAME;

 

[Trevil]

It appears the query can be made faster, but I first need to know some of the table relationships and indicies.

Can you please run the following code and send me the output from the debug window. Since I see this was your first post to Tek-Tips, excuse me if I offend with the detail:
1. Open your database and on menu bar, click "Insert | Module: (or click on Modules tab, then click "New" icon).
2. Paste the following code at the bottom:
Function Get_Table_Relationships()
Dim rel As Relation

For Each rel In CurrentDb.Relations

Debug.Print rel.Name & vbTab & "Table: " & rel.Table & vbTab & "Table2: " & rel.ForeignTable & _
vbTab & "Attributes: " & rel.Attributes
Next rel
End Function

Function List_Indicies()
Dim dbs As Database
Dim tdf As TableDef
Dim idx As Index

Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
Debug.Print tdf.Name

For Each idx In tdf.Indexes
Debug.Print " " & idx.Name & " " & idx.Fields & " " & idx.Unique & " " & idx.Required & " " & idx.Primary
'Debug.Print " " & idx.Properties(0).Name & " " & idx.Properties(1).Name & " " & idx.Properties(2).Name & " " & idx.Properties(3).Name & " " & idx.Properties(4).Name & " " & idx.Properties(5).Name & " " & idx.Properties(6).Name & " " & idx.Properties(7).Name
Next idx
End If
Next tdf
Set idx = Nothing
Set tdf = Nothing
Set dbs = Nothing
End Function


3. Click mouse anywhere within this new code and press the F5 key.
4. On menu bar, click "View | Immediate Window" (or press Ctrl+G)
5. Copy all of the text there and paste into this thread.

6. Then click your mouse anywhere in the "Function List_Indicies()" and press F5 again.
7. Copy the new data from the Immediate Window and paste in this thread.

Thanks.

"Hmmm, it worked when I tested it....

 

[Trevil]

I had another thought on something to try.
1. Change your Main query to a "make table" query and see how long it takes to run. You can't use a table name that is the same as any query.
2. Change your reports 'Record Source' to use that new table and see how long that runs.
If it's a lot faster, then that's the easy way out.

I did manage to restructure your queries a little, but have no idea how much impact that may have.

"Have a great day today and a better day tomorrow!

 

[nairnmp]

Trevil,

Thanks for the help. I tried making the main query a "make table" query, but that took about 7 min. So, what I did was run the main query as a select. Then went thru the record set and inserted the rows into a temp table myself. I set this temp table as the record source. The report now comes back in about 30 seconds.

Once again thanks for the help.