Process is slowing down while progressing through loop

[tim.oshea]

Hello,

We run an Access 2016 database to manage the parish and parishioner data for our local diocese. I've added an email function to the system so that we are able to output and send reports to each parish monthly.

It all runs fine but I've noticed that as the system generated the reports, it begins to slow down about halfway through. The first 50 or so parishes will export in a few seconds each, but by the back half of the recordset, it takes upwards to 30 seconds to export a single report. Is there something that I should be clearing at the end of each loop?

Thank you for any help!


<---------------------------------------------Code----------------------------------------------->

Sub ParishionerChangesReport()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MinDate As String

'===Use form's textbox to set date range for data pull==='
MinDate = Me.TB_MinDate.Value

'===Fetch list of parishes with at least one change. These parishes will have a report generated==='
Set db = CurrentDb

Set qdf0 = db.QueryDefs("Parishioner_Changes_EmailRecipients")

qdf0.SQL = "SELECT DISTINCT z.Parish_No, PM.Parish_Name, PM.Parish_City FROM Parish_Master as PM Inner Join (SELECT D.AAA_ID, D.Parish_No FROM Donors AS D WHERE d.Change_Date >= #" & MinDate & "#) AS Z on PM.Parish_No = z.Parish_No"


Set rs = db.OpenRecordset("Parishioner_Changes_EmailRecipients")
Set qdf1 = db.QueryDefs("In House - Changes by Timeframe")
Set qdf2 = db.QueryDefs("In House - Parishioner Change Report")

'===Pull data on all parishioners that have changed since specified MinDate==='
qdf1.SQL = "SELECT d.AAA_ID, TRIM(d.Prefix &' '&d.First&' '&d.Last&' '&d.Suffix) as [Full Name], " _
& "TRIM([d.Address1]&iif(((d.Address2 is Null) or (d.Address2 not like '*[a-z]*')), ', ', ', '&d.Address2&', ')&d.City&', '&d.State&' '&d.Zip) as [Full Address], " _
& "PM.Parish_No, PM.Parish_Name, PM.Parish_City, d.ChangeCode, d.Change_Date, d.Last, c.Comments, c.Change_Date as [CommentDate], ('Changes made between ' & [Forms]![Frm_ReportEmail]![TB_MinDate] & ' AND ' & Date() & '') as [ChangeRange], " _
& "IIf([ChangeCode]='a','Added',(IIf([ChangeCode]='d','Deleted','Changed'))) AS ChangeCategory " _
& "FROM (Donors as D Inner Join Parish_Master as PM on d.Parish_NO = PM.Parish_NO) Left Join " _
& "(SELECT DC.Donor_ID, DC.Change_Date, DC.Change_Code, DC.Comments, DC.Deleted_By FROM Donor_Changes as DC WHERE (DC.Change_Date BETWEEN [Forms]![Frm_ReportEmail]![TB_MinDate] and Date()) and DC.Deleted_By Is Null) as C on d.ID = c.Donor_ID " _
& "WHERE d.Change_Date BETWEEN [Forms]![Frm_ReportEmail]![TB_MinDate] AND Date()"

'===Loop through parishes with at least one change and generate report==='
Do Until rs.EOF

qdf2.SQL = "SELECT * FROM [In House - Changes by Timeframe] WHERE [Parish_No] ='" & rs![Parish_No] & "'"

DoCmd.OutputTo acOutputReport, "In House - Parishioner Change Report", acFormatPDF, "M:\AAA\Export\In-House Report Exports\Parishioner Change Report-" & rs![Parish_No] & " - " & rs![Parish_Name] & "(" & Format(Date, "mm-dd-yyyy") & ").PDF"


rs.MoveNext

DoEvents
Loop

rs.Close

End Sub

<--------------------------------End of Code------------------------------------>

 

[Minty]

You don't appear to be declaring your qdef objects anywhere.
I doubt that's a problem but seems odd not to. Have you got Option Explicit at the top of all your code modules?

How many records are being returned beyond the initial 50?

I'm not sure continually changing the query def that is the source of the report is very efficient. I suspect that might be where the problem lies.
I would open the report (hidden) filtered to a single record using a where clause from your recordset, then output that, then close the report.

 

[tim.oshea]

Hi,

I don't have Option Explicit set at the top of my code so I was able to get away with not declaring the qdefs. I'll add those in.

I should be generating about 110 reports with each report ranging anywhere from one or two records listed to 50 or 60. I ran a test with the date range of only two days to limit the results and it still hit the breaks pretty hard by the back half of the recordset.

I changed the process from updating the query def each time to filtering the report before exporting and it worked perfectly and run in a fraction of the time.

Thank you so much for your help!

 

[The_Doc_Man]

If there is a way to make this a parameter query, you would perhaps see a more uniform timing. I'm with Minty in that constantly changing your querydef isn't the most efficient way to do things. I am also wondering about the problem of memory bloat, since changing a querydef is one of the things that consumes memory in a way that updating parameters does not. Among other things, Access will have to recompute a query plan for every "new" querydef and that ALSO consumes memory for that session. Probably wouldn't hurt to do a Compact & Repair as a precaution. Just to be safe, remember to always make a backup copy before attempting a C&R just in case something doesn't work right during the repair phase.

 

[Gasman]

All I can think of is you are not closing and releasing the recordset rs?

I don't have Option Explicit set at the top of my code so I was able to get away with not declaring the qdefs. I'll add those in.

That will come back to bite you. Add it on every module that now exists. For new modules see https://www.google.com/search?q=opt...0i512j69i57.7545j0j7&sourceid=chrome&ie=UTF-8