Painfully slow report

[matey56]

Hi all,


I have a report that runs off of a query built off of 3 tables. It takes 15-20 seconds just to open. How can I do this differently to speed it up? The State is the connector for each table and I use fields from each table in the report.

[Minty]

How many records in each table. Are you restricting what you pull into the report in any way?
How much grouping and sorting are you doing in the report? Remember that in a Report, Access creates a hidden version of the source query with the grouping and sorting applied so it could be that, that is causing the slow down.

Do you have access to the SQL server those tables are linked to?

If you do you could create a view of the joined tables and return just the fields you need, I suspect it would work quicker.

[Welshgasman]

Have you indexed the necessary fields?
Sorting input is a waste of time for a report, has to be done within the report.

[matey56]

How many records in each table. Are you restricting what you pull into the report in any way?
How much grouping and sorting are you doing in the report? Remember that in a Report, Access creates a hidden version of the source query with the grouping and sorting applied so it could be that, that is causing the slow down.

Do you have access to the SQL server those tables are linked to?

If you do you could create a view of the joined tables and return just the fields you need, I suspect it would work quicker.

I do have access to the SQL server. How do I created a view of the joined tables?

[matey56]

Have you indexed the necessary fields?
Sorting input is a waste of time for a report, has to be done within the report.

I don't believe so. Can you explain what you mean by sorting input in the report?

[madpiet]

How many records are in each of the tables in your query? If there are a lot, and there are no indexes on the columns you're joining on or filtering, then the database engine is forced to do a table scan (basically read from the first record to the last, because there's no easy way (index) to find the records it needs to fulfill the query, so it reads the entire table. To prevent that, you need to index both sides of the join (Primary and foreign keys), and then the columns you're filtering on.

If you do that, you should see a considerable increase in speed in the query. Don't go crazy indexing everything, because every time you insert, update, or delete records, the affected indexes have to be maintained as well.

[matey56]

How many records are in each of the tables in your query? If there are a lot, and there are no indexes on the columns you're joining on or filtering, then the database engine is forced to do a table scan (basically read from the first record to the last, because there's no easy way (index) to find the records it needs to fulfill the query, so it reads the entire table. To prevent that, you need to index both sides of the join (Primary and foreign keys), and then the columns you're filtering on.

If you do that, you should see a considerable increase in speed in the query. Don't go crazy indexing everything, because every time you insert, update, or delete records, the affected indexes have to be maintained as well.

I found a script to index all the foreign keys in SQL but it didn't speed it up. :-(

[matey56]

How many records are in each of the tables in your query? If there are a lot, and there are no indexes on the columns you're joining on or filtering, then the database engine is forced to do a table scan (basically read from the first record to the last, because there's no easy way (index) to find the records it needs to fulfill the query, so it reads the entire table. To prevent that, you need to index both sides of the join (Primary and foreign keys), and then the columns you're filtering on.

If you do that, you should see a considerable increase in speed in the query. Don't go crazy indexing everything, because every time you insert, update, or delete records, the affected indexes have to be maintained as well.

There aren't many records at all. Less than a hundred in each table. How do I index both sides of the JOIN as you say?

[Minty]

I doubt the indexing will help the Access report generator very much.

Regarding the SQL Server view - if your query is simple switch to sql view and copy the SQL.

In SSMS (SQL Server Managment Studio - I assume you have it) paste the code into a new query on the SQL database - something like;

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*
Created by Minty - 30/07/2024
Simple example View 

*/

CREATE VIEW [dbo].[vw_My_First View]

AS

SELECT 
    Field1
   ,Field2
   ,Field3
   ,Field4
    
FROM YourTable
WHERE Field2 = 'MyFilterValue'

You will need to use the correct delimiters for SQL server - single quotes for Strings, and dates need to be in the format of '2024-07-31'
If you get stuck post up the original SQL from Access here and someone should be able to knock it into shape.

[matey56]

I doubt the indexing will help the Access report generator very much.

Regarding the SQL Server view - if your query is simple switch to sql view and copy the SQL.

In SSMS (SQL Server Managment Studio - I assume you have it) paste the code into a new query on the SQL database - something like;

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*
Created by Minty - 30/07/2024
Simple example View 

*/

CREATE VIEW [dbo].[vw_My_First View]

AS

SELECT 
    Field1
   ,Field2
   ,Field3
   ,Field4
    
FROM YourTable
WHERE Field2 = 'MyFilterValue'

You will need to use the correct delimiters for SQL server - single quotes for Strings, and dates need to be in the format of '2024-07-31'
If you get stuck post up the original SQL from Access here and someone should be able to knock it into shape.

Here's the SQL code for my query. I would appreciate help creating a view in SQL

SELECT dbo_72_tbl_State_Registration_Requirements.State_Name, dbo_72_tbl_State_Registration_Requirements.Labels, dbo_72_tbl_State_Registration_Requirements.Letter, dbo_72_tbl_State_Registration_Requirements.Summary_of_Changes, dbo_72_tbl_State_Registration_Requirements.EPA_Stamped_Documents, dbo_72_tbl_State_Registration_Requirements.EPA_Notification_Documents, dbo_72_tbl_State_Registration_Requirements.SDS, dbo_72_tbl_State_Registration_Requirements.Registration_Comments, dbo_72_tbl_State_Submission_Access.Submision_Email, dbo_72_tbl_State_Submission_Access.Online_PortalFROM (dbo_72_tbl_State_Registration_Requirements LEFT JOIN dbo_72_tbl_State_Contacts ON dbo_72_tbl_State_Registration_Requirements.State_Name = dbo_72_tbl_State_Contacts.State_Name) LEFT JOIN dbo_72_tbl_State_Submission_Access ON dbo_72_tbl_State_Contacts.State_Name = dbo_72_tbl_State_Submission_Access.State
WHERE (((dbo_72_tbl_State_Contacts.Registration_Type) Like "*Revisions*") AND ((dbo_72_tbl_State_Registration_Requirements.Registration_Type)="Revisions/Supplementals"))
ORDER BY dbo_72_tbl_State_Registration_Requirements.State_Name, dbo_72_tbl_State_Contacts.Registration_Type;

[NoellaG]

In SQL server: in table 72_tbl_State_Registration_Requirements put an index on field Registration_type and include field State_name. Then run the query directly in the management studio query window with the option "include actual query plan" switched on. Check the execution plan for missing index clues. I don't think creating a view will speed up things but it might make things easier for you in Access.

The query in SQL should look like:

SELECT SR.State_Name, SR.Labels
    , SR.Letter, SR.Summary_of_Changes, SR.EPA_Stamped_Documents, SR.EPA_Notification_Documents
    , SR.SDS, SR.Registration_Comments
    , SSA.Submision_Email, SSA.Online_Portal 
FROM 72_tbl_State_Registration_Requirements SR LEFT OUTER JOIN 72_tbl_State_Contacts SC ON SR.State_Name = SC.State_Name
         LEFT OUTER JOIN 72_tbl_State_Submission_Access SSA ON SC.State_Name = SSA.State
WHERE SC.Registration_Type Like '%Revisions%' AND SR.Registration_Type ='Revisions/Supplementals'
ORDER BY SR.State_Name, SC.Registration_Type;

assuming dbo is the schemaname and 72_xxx the table names

[madpiet]

That's super weird... unless the database server is crazy busy.

[matey56]

In SQL server: in table 72_tbl_State_Registration_Requirements put an index on field Registration_type and include field State_name. Then run the query directly in the management studio query window with the option "include actual query plan" switched on. Check the execution plan for missing index clues. I don't think creating a view will speed up things but it might make things easier for you in Access.

The query in SQL should look like:

SELECT SR.State_Name, SR.Labels
    , SR.Letter, SR.Summary_of_Changes, SR.EPA_Stamped_Documents, SR.EPA_Notification_Documents
    , SR.SDS, SR.Registration_Comments
    , SSA.Submision_Email, SSA.Online_Portal 
FROM 72_tbl_State_Registration_Requirements SR LEFT OUTER JOIN 72_tbl_State_Contacts SC ON SR.State_Name = SC.State_Name
         LEFT OUTER JOIN 72_tbl_State_Submission_Access SSA ON SC.State_Name = SSA.State
WHERE SC.Registration_Type Like '%Revisions%' AND SR.Registration_Type ='Revisions/Supplementals'
ORDER BY SR.State_Name, SC.Registration_Type;

assuming dbo is the schemaname and 72_xxx the table names

I executed the query as instructed. Then what?

[madpiet]

How long does this query usually take? Or do you not time it?

You need to tell SQL Server to return statistics etc
SET STATISTICS IO ON; -- Return statistics about the execution
SET TIME ON; -- return the time the query took to execute


Maybe start here:
Getting IO and time statistics for SQL Server queries (mssqltips.com)

then you should get something like this along with your query results

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 16 ms.


(1 row affected)


Completion time: 2024-07-31T13:27:27.4876990-05:00

but the showplan should give you a graphical representation of what's taking a lot of time.

[matey56]

How long does this query usually take? Or do you not time it?

You need to tell SQL Server to return statistics etc
SET STATISTICS IO ON; -- Return statistics about the execution
SET TIME ON; -- return the time the query took to execute


Maybe start here:
Getting IO and time statistics for SQL Server queries (mssqltips.com)

then you should get something like this along with your query results

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.


SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 16 ms.


(1 row affected)


Completion time: 2024-07-31T13:27:27.4876990-05:00

but the showplan should give you a graphical representation of what's taking a lot of time.

It runs instantly in SQL. It's the qry in Access that is holding me up.