Painfully slow report

[madpiet]

SET ANSI_NULLS ONGO


SET QUOTED_IDENTIFIER ON
GO
/*
Created by Minty - 30/07/2024
Simple example View 
Minor mods: Pieter - 31/07/2024
*/


CREATE VIEW [dbo].[vw_My_First View]


AS


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';

Unless I'm completely losing my mind, you can't use ORDER BY in a view unless you use TOP...



What happens if you turn the query into a Pass-through query?

[NoellaG]

In reply to the question #13:

"I executed the query as instructed. Then what?"

If you executed the query with the option "Include actual query plan" you should get a query plan similar to the included picture. If there are missing indexes for the plan they will be stated in green, right click these green lines to see the proposed index.
If not, in the plan you can exactly see how many seconds each step takes (read from right to left) and where you can improve the query.

As I already stated: creating a view won't help the speed. When executing the engine just performs the query as it would do if you start it manually. In a view "ORDER BY" makes no sense, unless you include a TOP statement, for example to get the top 100 newest entries ordered by creation date DESC.

[Minty]

Unless I'm completely losing my mind, you can't use ORDER BY in a view unless you use TOP...

What happens if you turn the query into a Pass-through query?

The Order By clause will become irrelevant as the report does the sorting separately.
I suspect the real issue is Access manipulating the data on the fly for the Report, which is causing it to re-query the server multiple times.

Using the view as a linked table (you can connect to it just like a table) try using that as source for your report.
If, as suggested, that doesn't improve matters, just for experiments sake, use the view to make a local temp table in Access then run the report from that local table.

You never did answer the question about how many rows of data are involved?

[madpiet]

Didn't think of that part... dumping the data to a temp table. Gets my vote!