Report performance question

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

re: Report performance question

Mark 123 wrote:
[color=blue]
> The question is, why does the report take longer to run even though[/color]
the same[color=blue]
> amount of records are being reported on? Any thoughts?
>
> TIA
> Mark[/color]

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

re: Report performance question

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,'*')));



<jimfortune@compumarc.com> wrote in message
news:1108061531.471820.95780@f14g2000cwb.googlegro ups.com...[color=blue]
> Mark 123 wrote:
>[color=green]
> > The question is, why does the report take longer to run even though[/color]
> the same[color=green]
> > amount of records are being reported on? Any thoughts?
> >
> > TIA
> > Mark[/color]
>
> 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
>[/color]

re: Report performance question

Mark 123 wrote:[color=blue]
> It was in the recordsource anyway:
>
> WHERE (((tblJobs.DateCompleted) Is Null) AND
> ((tblReportsTeamWorkloadDialogSubformSource.LimitT oSelect) Like
>[/color]
IIf([forms]![frmReportOptionsTeamWorkloadWorkform]![Sub0].[Form]![SelectTeam[color=blue]
> Member].[Value]=-1,True,'*')));[/color]

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

re: Report performance question

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.


<jimfortune@compumarc.com> wrote in message
news:1108066436.951390.196260@o13g2000cwo.googlegr oups.com...[color=blue]
> Mark 123 wrote:[color=green]
> > It was in the recordsource anyway:
> >
> > WHERE (((tblJobs.DateCompleted) Is Null) AND
> > ((tblReportsTeamWorkloadDialogSubformSource.LimitT oSelect) Like
> >[/color]
>[/color]
IIf([forms]![frmReportOptionsTeamWorkloadWorkform]![Sub0].[Form]![SelectTeam[color=blue][color=green]
> > Member].[Value]=-1,True,'*')));[/color]
>
> 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
>[/color]