Excessive time for query to run - can it be easier

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

Sue Compelling

Hi

This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up with it?
ACCESS 2007 (DB has 7,000 records)

TIA


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));


an excissively
 
Try the following:

- rename the HAVING clause as a WHERE clause, and move the GROUP BY clause
at the end.
- change the NOT IN for an outer join (like does the query wizard about
finding unmatched records)
- eliminate the infamous OR


((tblContacts.ContactType)="volunteer"
Or (tblContacts.ContactType)="Supporter"
Or (tblContacts.ContactType)="Service Group" )


can be replace by

( tblContacts.ContactType IN("volunteer", "Supporter", "Service Group"))



And also

((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""))


can be replaced by


( 0=len(Trim(tblContacts.Emailname & "" )))




Vanderghast, Access MVP



Sue Compelling said:
Hi

This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up with
it?
ACCESS 2007 (DB has 7,000 records)

TIA


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON
tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));


an excissively
Click to expand...
 
First try moving the HAVING clause criteria to a WHERE clause. That should
help some.

Second, try to get rid of the NOT In clause. NOT IN is notoriously SLOW.

You might be able to substitute
tblContacts.ContactID IN (SELECT TEMP.contactID
FROM tblContacts as Temp LEFT JOIN [qryvolsyrshelpingcount] as Q
ON TEMP.ContactID = Q.ContactID
WHERE Q.ContactID is Null)

However, I don't understand how that clause works at all since you are joining
on tblContacts.ContactID = QryVolsYrsHelpingCount.ContactID. That should
return only records that are IN both the table and the query. The clause in
the where clause says to return records only if they are not in the query.

The WHERE (HAVING) clause is so complex with all those extra parentheses that
I can't spend the time to reconstruct it without the extra parentheses. Some
of them are undoubtedly required and some of them just add to the difficulty
of understanding the where clause.

I think you may find that you need to change the join from an INNER JOIN to a
LEFT JOIN

It may be that you can then just test for qryVolsYrsHelpingCount.ContactID
being NULL.

So then things become
....
FROM tblContacts LEFT JOIN QryVolsYrsHelpingCount
ON tblContacts.ContactID = QryVolsYrsHelpingCount.ContactID
WHERE QryVolsYrsHelpingCount.ContactID Is NULL
....

And you completely drop the subquery.

SELECT "0" AS CountNil
, tblContacts.ContactID
, tblContacts.OrgTitle
, [tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName
, tblContacts.ContactStatus
, tblContacts.ContactType
, tblContacts.OrgType
, tblContacts.HomePhone
, tblContacts.WorkPhone
, tblContacts.WorkExtension
, tblContacts.MobilePhone
, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount
ON tblContacts.ContactID = QryVolsYrsHelpingCount.ContactID

WHERE (((tblContacts.ContactID) In
(SELECT TEMP.contactID
FROM tblContacts as Temp LEFT JOIN [qryvolsyrshelpingcount] as Q
ON TEMP.ContactID = Q.ContactID
WHERE Q.ContactID is Null)
)
AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""))

GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Sue said:
Hi

This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up with it?
ACCESS 2007 (DB has 7,000 records)

TIA


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));


an excissively
Click to expand...
 
Sue said:
This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up with it?
ACCESS 2007 (DB has 7,000 records)


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));
Click to expand...


Since you are not aggregating any values, the first thing to
do is remove the GROUP BY clause and change the HAVING to
WHERE. If you were using GROUP BY to get distinct records,
use the DISTINCT predicate instead.

The next thing to do is simplify the WHERE clause to remove
some/most of the OR expressions. Reconstructing the SQL
using VBA can probably eliminate most of the WHERE clause.
 
Thanks as always John

In the end I kept plugging away and tried a completely different approach
(that works a treat) though I am definitely continuing to learn from your
advice below

--
Sue Compelling


John Spencer MVP said:
First try moving the HAVING clause criteria to a WHERE clause. That should
help some.

Second, try to get rid of the NOT In clause. NOT IN is notoriously SLOW.

You might be able to substitute
tblContacts.ContactID IN (SELECT TEMP.contactID
FROM tblContacts as Temp LEFT JOIN [qryvolsyrshelpingcount] as Q
ON TEMP.ContactID = Q.ContactID
WHERE Q.ContactID is Null)

However, I don't understand how that clause works at all since you are joining
on tblContacts.ContactID = QryVolsYrsHelpingCount.ContactID. That should
return only records that are IN both the table and the query. The clause in
the where clause says to return records only if they are not in the query.

The WHERE (HAVING) clause is so complex with all those extra parentheses that
I can't spend the time to reconstruct it without the extra parentheses. Some
of them are undoubtedly required and some of them just add to the difficulty
of understanding the where clause.

I think you may find that you need to change the join from an INNER JOIN to a
LEFT JOIN

It may be that you can then just test for qryVolsYrsHelpingCount.ContactID
being NULL.

So then things become
....
FROM tblContacts LEFT JOIN QryVolsYrsHelpingCount
ON tblContacts.ContactID = QryVolsYrsHelpingCount.ContactID
WHERE QryVolsYrsHelpingCount.ContactID Is NULL
....

And you completely drop the subquery.

SELECT "0" AS CountNil
, tblContacts.ContactID
, tblContacts.OrgTitle
, [tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName
, tblContacts.ContactStatus
, tblContacts.ContactType
, tblContacts.OrgType
, tblContacts.HomePhone
, tblContacts.WorkPhone
, tblContacts.WorkExtension
, tblContacts.MobilePhone
, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount
ON tblContacts.ContactID = QryVolsYrsHelpingCount.ContactID

WHERE (((tblContacts.ContactID) In
(SELECT TEMP.contactID
FROM tblContacts as Temp LEFT JOIN [qryvolsyrshelpingcount] as Q
ON TEMP.ContactID = Q.ContactID
WHERE Q.ContactID is Null)
)
AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""))

GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Sue said:
Hi

This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up with it?
ACCESS 2007 (DB has 7,000 records)

TIA


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));


an excissively
Click to expand...
Click to expand...
 
Thanks as always Marshall

In the end I kept plugging away and tried a completely different approach
(that works a treat) though appreciate your advice below

--
Sue Compelling


Marshall Barton said:
Sue said:
This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up with it?
ACCESS 2007 (DB has 7,000 records)


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or (tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));
Click to expand...


Since you are not aggregating any values, the first thing to
do is remove the GROUP BY clause and change the HAVING to
WHERE. If you were using GROUP BY to get distinct records,
use the DISTINCT predicate instead.

The next thing to do is simplify the WHERE clause to remove
some/most of the OR expressions. Reconstructing the SQL
using VBA can probably eliminate most of the WHERE clause.
Click to expand...
 
Thanks for the Michel

I didn't know about the infamous OR clause and will definitely use your
advice below in future.

In the end though I kept plugging away and tried a completely different
approach (that works a treat)

Cheers

--
Sue Compelling


Michel Walsh said:
Try the following:

- rename the HAVING clause as a WHERE clause, and move the GROUP BY clause
at the end.
- change the NOT IN for an outer join (like does the query wizard about
finding unmatched records)
- eliminate the infamous OR


((tblContacts.ContactType)="volunteer"
Or (tblContacts.ContactType)="Supporter"
Or (tblContacts.ContactType)="Service Group" )


can be replace by

( tblContacts.ContactType IN("volunteer", "Supporter", "Service Group"))



And also

((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""))


can be replaced by


( 0=len(Trim(tblContacts.Emailname & "" )))




Vanderghast, Access MVP



Sue Compelling said:
Hi

This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up with
it?
ACCESS 2007 (DB has 7,000 records)

TIA


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON
tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));


an excissively
Click to expand...
Click to expand...
 
Michel

I meant to ask - why is it infamous?
--
Sue Compelling


Michel Walsh said:
Try the following:

- rename the HAVING clause as a WHERE clause, and move the GROUP BY clause
at the end.
- change the NOT IN for an outer join (like does the query wizard about
finding unmatched records)
- eliminate the infamous OR


((tblContacts.ContactType)="volunteer"
Or (tblContacts.ContactType)="Supporter"
Or (tblContacts.ContactType)="Service Group" )


can be replace by

( tblContacts.ContactType IN("volunteer", "Supporter", "Service Group"))



And also

((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""))


can be replaced by


( 0=len(Trim(tblContacts.Emailname & "" )))




Vanderghast, Access MVP



Sue Compelling said:
Hi

This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up with
it?
ACCESS 2007 (DB has 7,000 records)

TIA


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON
tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType, tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone, tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));


an excissively
Click to expand...
Click to expand...
 
Because it is harder for the optimizer when the strategy is to eliminate
rows to be returned: with an AND, if the left condition is false, no need to
evaluate the right ones:

a AND b AND c ....

while with a OR, the 'row' will be eliminated only if all the conditions, up
to the last one, are all false. So, more work with an OR than with an AND
(when the strategy is about to eliminate row, or to prune sub trees).


Vanderghast, Access MVP



Sue Compelling said:
Michel

I meant to ask - why is it infamous?
--
Sue Compelling


Michel Walsh said:
Try the following:

- rename the HAVING clause as a WHERE clause, and move the GROUP BY
clause
at the end.
- change the NOT IN for an outer join (like does the query wizard about
finding unmatched records)
- eliminate the infamous OR


((tblContacts.ContactType)="volunteer"
Or (tblContacts.ContactType)="Supporter"
Or (tblContacts.ContactType)="Service Group" )


can be replace by

( tblContacts.ContactType IN("volunteer", "Supporter", "Service
Group"))



And also

((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""))


can be replaced by


( 0=len(Trim(tblContacts.Emailname & "" )))




Vanderghast, Access MVP



message
Hi

This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up
with
it?
ACCESS 2007 (DB has 7,000 records)

TIA


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType,
tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone,
tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON
tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType,
tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone,
tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open")
AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open")
AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open")
AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));


an excissively
Click to expand...
Click to expand...
Click to expand...
 
Cheers Michel
--
Sue Compelling


Michel Walsh said:
Because it is harder for the optimizer when the strategy is to eliminate
rows to be returned: with an AND, if the left condition is false, no need to
evaluate the right ones:

a AND b AND c ....

while with a OR, the 'row' will be eliminated only if all the conditions, up
to the last one, are all false. So, more work with an OR than with an AND
(when the strategy is about to eliminate row, or to prune sub trees).


Vanderghast, Access MVP



Sue Compelling said:
Michel

I meant to ask - why is it infamous?
--
Sue Compelling


Michel Walsh said:
Try the following:

- rename the HAVING clause as a WHERE clause, and move the GROUP BY
clause
at the end.
- change the NOT IN for an outer join (like does the query wizard about
finding unmatched records)
- eliminate the infamous OR


((tblContacts.ContactType)="volunteer"
Or (tblContacts.ContactType)="Supporter"
Or (tblContacts.ContactType)="Service Group" )


can be replace by

( tblContacts.ContactType IN("volunteer", "Supporter", "Service
Group"))



And also

((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""))


can be replaced by


( 0=len(Trim(tblContacts.Emailname & "" )))




Vanderghast, Access MVP



message
Hi

This query, which is basically returning a record set of names NOT in
another query is taking 15-30 seconds to run.

Is my structure making it so cumbersome or do I have to just put up
with
it?
ACCESS 2007 (DB has 7,000 records)

TIA


SELECT "0" AS CountNil, tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname] AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType,
tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone,
tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
FROM tblContacts INNER JOIN QryVolsYrsHelpingCount ON
tblContacts.ContactID
= QryVolsYrsHelpingCount.ContactID
GROUP BY "0", tblContacts.ContactID, tblContacts.OrgTitle,
[tblcontacts].[firstname] & " " & [tblcontacts].[lastname],
tblContacts.ContactStatus, tblContacts.ContactType,
tblContacts.OrgType,
tblContacts.HomePhone, tblContacts.WorkPhone,
tblContacts.WorkExtension,
tblContacts.MobilePhone, tblContacts.Emailname
HAVING (((tblContacts.ContactID) Not In (select contactID from
[qryvolsyrshelpingcount])) AND ((tblContacts.ContactStatus)="open") AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.HomePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open")
AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkPhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open")
AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.WorkExtension) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)="")) OR (((tblContacts.ContactStatus)="open")
AND
((tblContacts.ContactType)="volunteer" Or
(tblContacts.ContactType)="Supporter" Or
(tblContacts.ContactType)="Service
Group") AND ((tblContacts.MobilePhone) Is Not Null) AND
((tblContacts.Emailname) Is Null Or (tblContacts.Emailname)=" " Or
(tblContacts.Emailname)=""));


an excissively
Click to expand...
Click to expand...
Click to expand...
 
Back
Top