Adding Criteria to a Calculated Expression in a query dramatically slows performance (1 Viewer)

bignose2

Registered User.
Local time
Today, 22:42
Joined
May 2, 2010
Messages
219
I have a query that runs normally in a fraction of a second.
One of the (many) fields is
DaysWaiting: Date()-DateValue(FirstBookedDogs([ID]))
Admittedly quite complicated but when run, say returns 10 records in 1/4 of a second.
So all the DaysWaiting numbers are show instantly so I assume calculated extremely fast.
If I put say, <30 in the criteria, it takes about 20 seconds to run, with the correct results etc.
(same if I use a me.filter)
I cannot see why this would be he case, never come across this sort of thing before. Would prefer not to make a table and also would like to understand why.

LenStay: [Outdate]-[Indate]
Nowhere near as complicated but is instant.

I assumed it filtered on the date range (all indexed) first and then on the DaysWaiting etc, I now wonder if it goes through the whole table calculating on every record rather than just the date range, but even then their is only 2800 fairly simple records.
I struggle with subquerys & try to avoid too many nested.


is the whole query
SELECT DISTINCTROW Dogs.Key, Dogs.Names, Dogs.Indate, Dogs.Outdate, Dogs.Comment, Dogs.Code, FirstBookedDogs([ID]) AS AskedDate, IfCancC([Comment]) AS CopyRight, Dogs.Link, Dogs.RegID, Dogs.RegBreed, Dogs.MessageID, Dogs.EmailAddr, [Forms]![DogsWantedRange]![StartDate]-[Indate] AS PreDays, [Outdate]-[Forms]![DogsWantedRange]![EndDate] AS PostDays, Date()-DateValue(FirstBookedDogs([ID])) AS DaysWaiting, IIf(InStr([Comment],"¬")>0,Left([Comment],1),"") AS Expr1, [PreDays]-[PostDays] AS MinDif, [Outdate]-[Indate] AS LenStay
FROM Dogs
WHERE (((Dogs.Indate)<=[Forms]![DogsWantedRange]![EndDate]) AND ((Dogs.Outdate)>=[Forms]![DogsWantedRange]![StartDate]) AND ((Dogs.Code)="C")) OR (((Dogs.Indate)<=[Forms]![DogsWantedRange]![EndDate]) AND ((Dogs.Outdate)>=[Forms]![DogsWantedRange]![StartDate]) AND ((IfCancC([Comment]))>0));
 

Dreamweaver

Well-known member
Local time
Today, 22:42
Joined
Nov 28, 2005
Messages
2,466
can you post a copy witout any private data it would help us see whats happening

mick
 

bignose2

Registered User.
Local time
Today, 22:42
Joined
May 2, 2010
Messages
219
Thanks for your reply,
not easy to post a copy, data is private'ish and not easy replace enough of it with dummy, also a linked table.
I tried a nested query & just as bad but perhaps goes back to the root query to refresh

What is weird is I removed all the date range criteria so it had to go through ALL the records thinking that may be the slow bit but that is v.fast.
The whole table & query with that calculated expression and DaysWaiting resulting number show within half a second.
As soon as I add just that one criteria e.g. <50 it take 20 seconds. really does not make sense to me.

I have compiled, decompiled, restarted etc etc.

Almost bug like but perhaps I am missing something.
 

bignose2

Registered User.
Local time
Today, 22:42
Joined
May 2, 2010
Messages
219
FirstBookedDogs = Format(DMin("DogsModDate", "[DogsModDate]", "[DogsID] =" & DogsID & ""), "dd-mmm-yyyy")

DogsModeDate is both a table & one of the fields (I don't normally do that but sure oK)
It is a table that I link to the main Dogs table using the ID's
Most fields indexed.

Whenever the main booking on the Dogs table is altered it just logs the date & time. I use this so I can see the last & first time it was accessed.
 

Dreamweaver

Well-known member
Local time
Today, 22:42
Joined
Nov 28, 2005
Messages
2,466
"[DogsID] =" & DogsID & ""
Try "[DogsID] ='" & DogsID & "'"
that is it is a string and not a number?
 

Minty

AWF VIP
Local time
Today, 22:42
Joined
Jul 26, 2013
Messages
10,355
You would be better of creating a separate grouped query to get the minimum date for all your records and joining that to your output.
Domain functions e.g. The DMin() will effectively run a query for every record, exponentially taking longer and longer to run as the dataset gets bigger.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:42
Joined
Feb 28, 2001
Messages
27,001
You have an INSTR function which essentially negates the use of ANY indexing for that field, which (I think) negates the effectiveness of indexing for the entire query.

Code:
IIf(InStr([Comment],"¬")>0,Left([Comment],1),"") AS Expr1

You ALSO have a Domain Aggregate which is inside a query, which means you are running a separate query for every record queried in your main table. This query will get slower as the size of the table increases because (A) it will take Access longer to find the matching records for each run of the DMIN and then (B) it will have to run more cycles of the DMIN because the main table has more records for which to make the comparison.

Code:
FirstBookedDogs = Format(DMin("DogsModDate", "[DogsModDate]", "[DogsID] =" & DogsID & ""), "dd-mmm-yyyy")

For the DMIN, create a separate SQL query that does a SELECT DogsID, MIN(DogsModDate) AS FBD ;

Then do a JOIN of your main query to this secondary query using DogsID as the JOIN criterion.

Then in the main query you can reference "FBD" as the first booked date for that dog. However, you still have the issue of having to use an outer JOIN (LEFT or RIGHT depending on how your write it) and will have to test for the case where the dog in question HAS no "first booked date" because it has not been booked before.
 

Users who are viewing this thread

Top Bottom