slow query when asking for sum of minutes

[sf827]

Hi all,



I have a query (Query1) which includes a field "TimeDiff" which is produced by the following expression:

DateDiff("n", Query1.Time, Query1.PickupTime) AS TimeDiff

This works fine. The problem I am having is that when I try to run a sum expression on this field as:

Sum(Query1.TimeDiff)

The query runs so slowly as to be unusable. It does provide the correct answer eventually but it takes up to 10 minutes...

Can anyone provide an answer as to why the query is running so slowly?
How can I get the same result without the delays?

Thanks

[June7]

Is this a Totals query with Group By clause? Show the SQL statement for analysis.

Try building a report and do the aggregate calc there. See if that is faster.

[sf827]

Hi June7,

Yes to the 'group by' clause. I'm running Query1 with the DateDiff expression, then in a separate second query I have the following SQL:

SELECT Query1.State, Sum(Query1.TimeDiff)
FROM Query1
GROUP BY Query1.State

Do You think that the group by clause may be a problem?

[sf827]

As a further note, I've tested the SQL on a small data sample (50 records) and it worked instantly. The original data sample was 11 000 records...

Also, adding or removing the Query1.State clauses seemed to have no impact on how long it took for the SQL to finish the query.

The problem really seems to be the sum function... Anyone?

[June7]

Interesting that the SQL statement doesn't show an alias field name for the Sum expression.

My suggestion is to try building a report with the first query as RecordSource. Then use the report Grouping & Sorting functionality to do the aggregate group summaries. 11,000 records is not a lot.

If problem continues can provide project for analysis.

[sf827]

Hi June7,

I've gone away and tried to generate a report from 'Query1' but Access seems to just freeze as soon as I try to generate the report based on the query. I've tried to keep my questions on here simple by excluding certain info, but I think I'll just show my full SQL code and just see if anyone can pick a problem... Many thanks if you can help!

QUERY1
SELECT ExtractEvents.CustomerCode, ExtractEvents.ParcelNumber, ExtractEvents.Date, ExtractEvents.Time, ExtractEvents.EventCode, ExtractEvents.Outlet, ExtractEvents.State,
(SELECT TOP 1 Dupe.Time FROM ExtractEvents AS Dupe WHERE Dupe.ParcelNumber = ExtractEvents.ParcelNumber AND (Dupe.Date>ExtractEvents.Date OR (Dupe.Date = ExtractEvents.Date AND Dupe.Time>ExtractEvents.Time)) ORDER BY Dupe.Date, Dupe.Time DESC) AS PickupTime,
(SELECT TOP 1 Dupe.Date FROM ExtractEvents AS Dupe WHERE Dupe.ParcelNumber = ExtractEvents.ParcelNumber AND (Dupe.Date>ExtractEvents.Date OR (Dupe.Date = ExtractEvents.Date AND Dupe.Time>ExtractEvents.Time)) ORDER BY Dupe.Date, Dupe.Time DESC) AS PickupDate,
IIf([PickupTime]>0,Round([PickupTime]-[Time],4),0) AS TimeDiff,
IIf([PickupTime]>0,[PickupDate]-[Date],0) AS DateDiff
FROM ExtractEvents;

QUERY2
SELECT Query1.CustomerCode, Query1.ParcelNumber, Query1.Date, Query1.Time, Query1.EventCode, Query1.Outlet, Query1.State, (Query1.TimeDiff+Query1.DateDiff)*24 AS ParcelAge
FROM Query1;

QUERY3
SELECT Query2.State, Sum(Query2.ParcelAge) AS AvgParcelAge
FROM Query2
GROUP BY Query2.State;

Is it possible that the complex source of the data I'm trying to run the Sum function on in query3 is causing the slow performance? Thx

[June7]

Suggest not using DateDiff as field name as this is a reserved word, else enclose in [] (same for any reserved word, such as Date). However, doubt this will correct the issue.

Is Time field a number or text datatype? If it is text and only time entered (as in 8:42 PM), cannot calculate without converting to date/time value.

Date field has only date, no time?

[sf827]

Date field is date only - 01/01/2011, Time field is time only - 08:00 AM, both exist as a date/time format in the original table - ExtractEvents.

[June7]

I tested your query structure on very small dataset and runs fine. I would have to test with your data if you want to provide project.

I assume Date and Time are supposed to be coming from the same record? Consider this (not tested):
SELECT ExtractEvents.CustomerCode, ExtractEvents.ParcelNumber, CDate(ExtractEvents.Date & " " & ExtractEvents.Time) As [DateTime], ExtractEvents.EventCode, ExtractEvents.Outlet, ExtractEvents.State,
(SELECT TOP 1 CDate([Date] & " " & [Time]) As [DateTime] FROM ExtractEvents AS Dupe WHERE Dupe.ParcelNumber = ExtractEvents.ParcelNumber AND (Dupe.Date>ExtractEvents.Date OR (Dupe.Date = ExtractEvents.Date AND Dupe.Time>ExtractEvents.Time)) ORDER BY Dupe.Date, Dupe.Time DESC) AS PickupDateTime,
IIf([PickupDateTime]>0,Round([PickupDateTime]-[DateTime],4),0) AS DateTimeDiff FROM ExtractEvents;

[sf827]

Yes I also tested on a small sample and it runs perfectly - it just doesn't handle anything over 2000 records without delaying.

I just tested your modified code but access returned an error message for the "select top 1" subquery - I'm thinking maybe it won't accept the CDate function due to the sorting?

Regardless, how would i go about providing you with a sample of my data? I'm new to this forum.

Thanks for your perseverance June7!

[June7]

You can attach the project (or other files) to post. Make copy, remove confidential data, run Compact & Repair, zip if large. The Attachment Manager is below the Advanced post editor.

What was the exact error message?

I just tested TOP with a concatenated DateTime using CDate and it works but I sorted on the constructed DateTime field and no filtering.
I can get pieces to work but not attempting a complete replication of your data and query. It is complex. Provide the project and I will have a go.

[sf827]

The error message was a generic one about syntax in the whole expression being wrong. Changing the sort did the trick.

I've attached the relevant items for this project.

Thanks!

[June7]

Had errors in my suggested query. Turns out DateTime is a reserved word so had to enclose in [] or you can create a different field name. Also, extraneous comma in front of the last FROM. No other changes and snap! it runs. It opens almost immediately but takes a while for navigation to last record.

Attempt to build and run report with this query gets error: Multi-level GROUP BY clause is not allowed in a subquery.

Maybe going about this all wrong, TOP might not be needed. Every parcel appears to have an IN and OUT record - always the case? Which is the PickUp date and time? Try this approach:

SELECT ParcelNumber, CustomerCode, Outlet, State, Max(IIf([EventCode]="Out",CDate([Date] & " " & [Time]),Null)) AS Out, Max(IIf([EventCode]="In",CDate([Date] & " " & [Time]),Null)) AS [In], [Out]-[In] AS DateTimeDiff
FROM ExtractEvents
GROUP BY ParcelNumber, CustomerCode, Outlet, State;

This reveals that not all parcels have an IN and OUT. Maybe some are pending but also some parcels are coded as RETURNED/COLLECTED. Do you want to filter out the RETURNED/COLLECTED? Or are RETURNED and COLLECTED synonymous with IN and OUT?

[sf827]

I've just tried that approach on my end and it has worked beautifully (from what I've seen so far). Had the feeling that the TOP was the problem but couldn't figure out how to replace the expression. With a few tweaks this should give me exactly what I'm after.

thanks again.