Report vs Query Execution Time

[stevenblanc]

Hi folks,

I have a report run based off of a query.

If I run the query directly, it loads in about 5 seconds; however, if I run the report it takes about 10-15 minutes to load and is only 3 pages long.

The query contains first entry/last exit times for employees filtered by date. The report summarizes this data to give total days late and days left early for the same range. I typically want to be able to see the last 6 - 12 months of history.

So thats:

2 x 70 employees = 140 records per day.
140 x 365 = 51,100 records for a year.

Thats only 63875 records with a 25 percent buffer.

Any thoughts on what is causing the difference in loading times or how I could resolve this extended time frame?

Cheers,


Steven

 

[Galaxiom]

If the query is not ordered it will load the first page as soon as it is available even though all the data has not been calculated.

 

[pbaldy]

I had the same thought. To test, when the query loads, click on the "last record" button and see how long it takes to jump to it. Also, are there domain aggregate functions in the report? They can slow it down significantly.

 

[stevenblanc]

There are aggregate calculations.

In order to determine the first and last swipes for each employee for the day I effect two queries and then join them.

The first query groups date, employee, etc, and calculates the minimum (first swipe) on time and sequence ID. The other does the maximum (last swipe).

I attempted to create an append query to filter the initial data by a date range and append it to a table in order to reduce the amount of data being aggregated later on.

However, this append query hangs when I try to run it. Still trying to figure this out.

 

[pbaldy]

Did you do the test I recommended? Another might be to base a new report on the query but don't add any aggregate functions, and see how fast that report opens. That will help identify where the speed issue is.