More Data requested increases runtime exponentially.

[Phil Smith]

I have a report. It consists of 5 sub reports. Each works from it's
own query which in turn are all based on the same base query. They
just sort and subtotal the data in different ways.

This all comes down to a Customer sales "report card", one customer to a
page, driven by a list of customer numbers.

If I put one customer # in that list, I get a result, meaning I see page
one in the preview pane, in 64 seconds. If I put two customers, it
takes 75 seconds for the first page to show up. At this point, I am not
sure how this progresses, except that I put 240 customers in this list,
and after 16 hours, I still have not even seen page one. Yet, I know if
I run it one page at a time, I can get it in about 5 hours.

The main query to drive the report is nothing. It only takes a few
seconds to go from beginning to end, even with a the full pull of 4
thousand customers.

I am looking for some guidance here. None of the queries are fancy.
The base query just pulls data between date ranges limited to customer
numbers. The other queries, just sort and subtotal based on different
fields, Item Brand, Item Type, etc.

It is only whan it comes all together that it does this. How would I go
about troubleshooting this brick?

Thanx

Phil

 

[Golfinray]

You may have already done these things, but I would start with going to all
my queries in design view, right click in the gray area and set output all
records to no.
Sometimes the query is producing way more records than you actually need.
Next I would check what king of links i have on all my queries. Click on the
line from one table to the next and edit join properties. You may need to
change to 2 or 3. Next check your relationships and check for some bad joins
there. Again, click on the line and check join properties to make sure your
relationships are the way you want them. Then, anytime you have 5 subs, it's
going to be a little slow. You might consider splitting the report into two
pieces. Another thing is check the section headers on the main report. Right
click to get properties and go to force new page. You may need to force a new
page between sections of your report. Sometimes putting the subreports in the
footer of the main report works better for me.

 

[Klatuu]

Golfinray has some good suggestions, particularly about the added overhead
with multiple sub reports.
Another thing to look at is are there any calculations, sub queries or
Domain Aggreate Functions in the query?

If there are, you would be suprised how much faster it will run if you move
all cacluations and Domain Aggregate functions to the report. I once had
someone to ask me why a report was running so slowly. It was doing Domain
Aggregate functios in many of the fields and returning calculated fields.
The report took over an hour to run. I moved all the logic out of the query
and into the report. In ran in four minutes.

Here is why. Jet is not a client sever database engine. It is a file
server engine. By that I mean with most database engines, like SQL Server,
for example, you pass a request for data to the engine and it returns only
the records you requested. But with Jet, all processing takes place on the
computer where Access is running, so it has to retrieve the entire recordset,
evaluate which records should be included, and perform all the logic before
it passes the results to Access.

I never put any logic or filtering in my reports record source queries. I
only include the field I need to display on the report or use for
calculations. I use the Where argument of the OpenReport method to filter my
reports.

 

[Phil Smith]

First thing to point out, This is running from a MySQL server. I tried
pulling the tables locally for a test, and it did not make any difference.

I am not sure what you mean by a "Domain" aggregate. The base query
sections out the data, with some logic. Basicly, I break down my sales
into to sales periods, on a form. So I will have Period1start,
period1end, period2start, period2end, and two calculated fields, iif
invoice date is between period 1, sales, 0, and iif invoice date is
between period 2, sales, 0. Those two fields are being totaled in the
sub report queries...

It sounds like this is what you ar warning me against?

 

[Klatuu]

Yes. Remove the logic from the queries and do it in the reports.
If you are using MySQL, you could improve performance by using a stored
procedure that will return the data. In this case, putting the logic in the
stored procedure would not hurt performance because it is being processed on
the server and will return only those rows requested.