Process is slowing down while progressing through loop (1 Viewer)

[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.

 

[Pat Hartman]

I agree. The query should be a parameter query. It's hard to rebuild a query without the database but here is a shot at it. First off, I removed the sub query, it should not be necessary. A simple left join should suffice.

Save this as a querydef and DO NOT change the SQL at runtime. Access will evaluate the control reference just fine.
SELECT DISTINCT D.Parish_No, PM.Parish_Name, PM.Parish_City FROM Parish_Master as PM
Inner Join Doners as D on PM.Parish_No = D.Parish_No WHERE D.Change_Date >= Forms!yourformname!TB_MinDate

1. Save this as a querydef after fixing the reference to the unbound form control
2. Make sure the TB_MinDate control has a format of Short Date. That eliminates the need for the ## to surround the date since Access knows the field in the control is a date and so will treat it as a date.
3. You might want to change Doners to Donors if it isn't too late. If it is too late, at least fix it on your forms/reports. INMNOHO, it is never too late to fix typos like this. It is just an annoyance.

A. I can't tell how the report is being feed the Parish_No so you might need to add a second unbound control to the form and fill it with the current Parish_No as you iterate through the loop since the OutputTo doesn't offer a Where option.
B. I don't know what qdf1 is being used for. That could be a saved querydef also and it also doesn't need the subselect.

Not sure why you are using subselects this way but Access does not optimize them well so unless there is NO OTHER OPTION, don't use them. Just stick with simple inner or outer joins.

 

[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