Report performance question

[Mark 123]

A97.

I have a landscape report with 4 sub-reports. All reports including the main
report use the same recordsource.

The LinkMasterFields and LinkChildFields properties for all sub-reports
read:

ClientName;JobID;JobID;JobID;

In this case the dynamic report is grouping on Client name only. The other 3
grouping levels are there in case the user wants to use them.

Now, when I run the report with 400 Job records in the database it takes 11
seconds to load. This is OK since the report is 56 pages long and has quite
a bit of formatting code.

However, when the report is run with 5000 records, it takes over one minute.
The thing is that it is still only 56 pages long since the report filters
out Jobs are finished, i.e. IsNull([FinishedDate])=False -- 4600 jobs are
actually finished leaving 400 jobs to report on.

I have a myriad of indexes including:

Index 1: FinishedDate

Index 2: ClientName

Index 3: JobID

Index 4: FinishedDate, JobID

Index 5: ClientName, FinishedDate

The question is, why does the report take longer to run even though the same
amount of records are being reported on? Any thoughts?

TIA
Mark

[jimfortune]

Mark 123 wrote:

The question is, why does the report take longer to run even though the same amount of records are being reported on? Any thoughts?

TIA
Mark

Try putting the IsNull([FinishedDate]) = False from your Filter into
the report recordsource as:
"AND [FinishedDate] IS NOT NULL"

There was a discussion about this recently. Using the recordsource
might pull just the records needed. The filter might pull all the
records in the recordset (including calculations) before applying the
filter criterion. Try it out and let us know. Note that if your
dynamic report relies on changing the filter you can set the report's
recordsource dynamically instead.

James A. Fortune

[Mark 123]

It was in the recordsource anyway:

WHERE (((tblJobs.DateCompleted) Is Null) AND
((tblReportsTeamWorkloadDialogSubformSource.LimitT oSelect) Like
IIf([forms]![frmReportOptionsTeamWorkloadWorkform]![Sub0].[Form]![SelectTeam
Member].[Value]=-1,True,'*')));

<ji********@compumarc.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Mark 123 wrote:

The question is, why does the report take longer to run even though

the same

amount of records are being reported on? Any thoughts?

TIA
Mark

Try putting the IsNull([FinishedDate]) = False from your Filter into
the report recordsource as:
"AND [FinishedDate] IS NOT NULL"

There was a discussion about this recently. Using the recordsource
might pull just the records needed. The filter might pull all the
records in the recordset (including calculations) before applying the
filter criterion. Try it out and let us know. Note that if your
dynamic report relies on changing the filter you can set the report's
recordsource dynamically instead.

James A. Fortune

[jimfortune]

Mark 123 wrote:

It was in the recordsource anyway:

WHERE (((tblJobs.DateCompleted) Is Null) AND
((tblReportsTeamWorkloadDialogSubformSource.LimitT oSelect) Like
IIf([forms]![frmReportOptionsTeamWorkloadWorkform]![Sub0].[Form]![SelectTeam Member].[Value]=-1,True,'*')));

Hmmm. That IIf looks like a good candidate for optimization. I'd
create the SQL string in VBA and get rid of it.

James A. Fortune

[Mark 123]

Excellent tip thanks.

On the other issue, I think we have found the cause.

The report recordsource SQL had a sub-query in it. This sub-query doesn't
have any WHERE criteria limiting it to where (tblJobs.DateCompleted) Is
Null).

It appears that Jet doesn't pass, through inheritance, the criteria to the
subquery. When I manually inserted the WHERE clause into the sub-query, the
report ran just as fast.

Thanks for your help too.
<ji********@compumarc.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Mark 123 wrote:

It was in the recordsource anyway:

WHERE (((tblJobs.DateCompleted) Is Null) AND
((tblReportsTeamWorkloadDialogSubformSource.LimitT oSelect) Like

IIf([forms]![frmReportOptionsTeamWorkloadWorkform]![Sub0].[Form]![SelectTeam

Member].[Value]=-1,True,'*')));

Hmmm. That IIf looks like a good candidate for optimization. I'd
create the SQL string in VBA and get rid of it.

James A. Fortune