Slow Query when Including Criteria on Yes/No Field

C

Cascot

I have a select query which is acting very strangely. On my local
development system it was fine but during recent changes (no changes to
this specific query or the tables it queries) I noticed that in the new
version of the system the query was running slowly. Removing a criteria
of "False" from a non-indexed Yes/No type field speeded it up, but when
I replicated this change on the client site it made no difference -
still slow. I removed the criteria "True" from the other boolean field
included in the query and it displayed it's resultys in an instant. I
have tried redoing the query from scratch, importing all object into a
new mdb, etc all to no avail.

Can anyone suggest what could be causing this - runing very slowly, but
when I remove both of the "True" & "False" criteria it completes in a
flash as it should. Here is the SQL:

SELECT InvoiceHdr.InvoiceHdrId AS PrnInvoiceHdrId, InvoiceHdr.PrintNow,
TelephoneNo.Description, Call.CallTime,
IIf(nz([CarrierDestination].[Description],"")="",[Destination].[PrintDescription],[CarrierDestination].[Description])
AS Destination, Call.CallTelNo, Call.CallDuration,
SecondsToHHMMSS([CallDuration]) AS CallDurationB, Call.CallCost,
Call.CallDate, TelephoneNo.ExcludeFromItemisation
FROM Organisation INNER JOIN ((TelephoneNo INNER JOIN (InvoiceHdr INNER
JOIN (CarrierDestination RIGHT JOIN Call ON
CarrierDestination.CarrierDestinationId = Call.CarrierDestinationId) ON
InvoiceHdr.InvoiceHdrId = Call.InvoiceHdrId) ON
TelephoneNo.TelephoneNoId = Call.TelephoneNoId) INNER JOIN Destination
ON Call.DestinationId = Destination.DestinationId) ON
Organisation.OrganisationId = InvoiceHdr.OrganisationId
WHERE (((InvoiceHdr.PrintNow)=True) AND
((Call.CallCost)>=[ItemisationLevel] Or (Call.CallCost)<0) AND
((TelephoneNo.ExcludeFromItemisation)=False));

If I was doing this today I would consider replacing the NZ with a
Len([Fieldname])=0.

There is a call to a function called SecondsToHHMMSS, but this works
well and removing it does not noticeably speed up the query, it is only
removal of the criteria on the boolean fields which does the trick.

Any suggestions would be much appreciated.
 
M

Michel Walsh

Hi,


Try to put the Boolean field last in the WHERE clause, bring the most
restrictive conditions first, mainly if the field with the most restrictive
condition has index.



WHERE
(Call.CallCost>=[ItemisationLevel] Or Call.CallCost<0) AND
(InvoiceHdr.PrintNow=True) AND
(TelephoneNo.ExcludeFromItemisation=False);



***if*** there is very few records where:
TelephoneNo.ExcludeFromItemisation=False, consider making a query on
telephoneNo table, that will pick up just those:

SELECT *
FROM TelephoneNo
WHERE ExcludeFromItemisation=False


save it and use that query rather than your table in the FROM clause of you
big query. DO NOT USE that technique if the condition,
TelephoneNo.ExcludeFromItemisation=False, keep a relatively large proportion
of your records.


***if*** there is very few records where: InvoiceHdr.PrintNow=True, consider
making a query on InvoiceHdr table ... oh, well, you already know...as well
as when NOT using that technique.

***if*** there is really really few records where the Boolean condition
hold, consider making an index on that field (YES!) and bring back that
condition first in the WHERE clause. This case is unusual, but consider that
if the condition hold for just one or two records, if you have an index, you
immediately spot them, while without index, you need to examine all the
records one by one, a very slow process. So, usually, do not index Boolean
fields, but if they are on a field that hold mostly just one of the two
Boolean value, they may be very useful to spot the few exceptions.



Hoping it may help,
Vanderghast, Access MVP

Cascot said:
I have a select query which is acting very strangely. On my local
development system it was fine but during recent changes (no changes to
this specific query or the tables it queries) I noticed that in the new
version of the system the query was running slowly. Removing a criteria
of "False" from a non-indexed Yes/No type field speeded it up, but when
I replicated this change on the client site it made no difference -
still slow. I removed the criteria "True" from the other boolean field
included in the query and it displayed it's resultys in an instant. I
have tried redoing the query from scratch, importing all object into a
new mdb, etc all to no avail.

Can anyone suggest what could be causing this - runing very slowly, but
when I remove both of the "True" & "False" criteria it completes in a
flash as it should. Here is the SQL:

SELECT InvoiceHdr.InvoiceHdrId AS PrnInvoiceHdrId, InvoiceHdr.PrintNow,
TelephoneNo.Description, Call.CallTime,
IIf(nz([CarrierDestination].[Description],"")="",[Destination].[PrintDescription],[CarrierDestination].[Description])
AS Destination, Call.CallTelNo, Call.CallDuration,
SecondsToHHMMSS([CallDuration]) AS CallDurationB, Call.CallCost,
Call.CallDate, TelephoneNo.ExcludeFromItemisation
FROM Organisation INNER JOIN ((TelephoneNo INNER JOIN (InvoiceHdr INNER
JOIN (CarrierDestination RIGHT JOIN Call ON
CarrierDestination.CarrierDestinationId = Call.CarrierDestinationId) ON
InvoiceHdr.InvoiceHdrId = Call.InvoiceHdrId) ON
TelephoneNo.TelephoneNoId = Call.TelephoneNoId) INNER JOIN Destination
ON Call.DestinationId = Destination.DestinationId) ON
Organisation.OrganisationId = InvoiceHdr.OrganisationId
WHERE (((InvoiceHdr.PrintNow)=True) AND
((Call.CallCost)>=[ItemisationLevel] Or (Call.CallCost)<0) AND
((TelephoneNo.ExcludeFromItemisation)=False));

If I was doing this today I would consider replacing the NZ with a
Len([Fieldname])=0.

There is a call to a function called SecondsToHHMMSS, but this works
well and removing it does not noticeably speed up the query, it is only
removal of the criteria on the boolean fields which does the trick.

Any suggestions would be much appreciated.
Click to expand...
 
C

Cascot

Vanderghast,

Thank you for your very extensive reply. I had already tried breaking
the task down and putting the boolean type checks into another query,
but with no success, at least not on the client machine.

I have just tried adding an Index to the PrintNow field within the
InvoiceHdr table, but again this did not help. It does however seem to
be that precise field (PrintNow) that is causing the problem on the
client machine. I removed all other criteria and the query ran in no
time. I then added only [InvoiceHdr].[PrintNow]=True and the query took
3m06s to complete!!

I then replaced that with a criteria against another boolean field on
the same table - [InvoiceHdr].[Printed]=True and again the query was
instant. It almost seems like there is some kind of corruption on the
[PrintNow] field, or something along those lines.

Any thoughts?
 
G

Guest

What happens to the speed when you put =False?

Instead of true, try using = -1 which is the same as =True.

When all else fails, blame the network or hardware. ;-)
 
C

Cascot

Thanks for your reply Jerry.

I had tried -1 and <>0, etc all with no impact.

Using =False brings the result window up straight away, though the
entire recordset takes a few seconds to be completed.

I'm stumpted !!

I tried creating a new field and the result was the same. It seems that
whenever you are searching for the minority, say one or even zero
instances of true from a table of thousands the speed plummets. I now
have an index on this field (even though one shouldn't be necessary for
acceptable performance) but it appears to have no impact. I also tried
converting the field to an Integer and again no improvement.

I'm really running out of ideas.

Thanks for everybody's help so far. If anyboby has ANY other
suggestions, I'd be really grateful. The longer this goes and the odder
it gets, the more I want to solve it!
 
C

Cascot

I finally solved the problem with this query and another similar one,
by removing the TelephoneNo table (and thus the TelephoneNo.Description
and TelephoneNo.ExcludeFromItemisation boolean criteria check) and
including only the TelephoneNoId field in the query result. I then
created a 2nd query and in it linked the 1st query to the TelephoneNo
table I removed from the first query using the TelephoneNoId field and
within this 2nd query I added back in the 2 fields removed from the 1st
query, namely TelephoneNo.Description and
TelephoneNo.ExcludeFromItemisation (complete with the criteria of
=False).

So as is typical with Access problems, it sent me around the houses and
ultimately the root cause ended up being something different to what
the early evidence pointed to. Ah the joys...

Anyway, everything now works as it should.

Thank you to everyone who responded with their suggestions.
 
Top