Excessive time for query to run - can it be easier

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
 
M

Michel Walsh

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...
 
J

John Spencer MVP

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...
 
M

Marshall Barton

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.
 
S

Sue Compelling

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...
 
S

Sue Compelling

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...
 
S

Sue Compelling

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...
 
S

Sue Compelling

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...
 
M

Michel Walsh

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...
 
S

Sue Compelling

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Msg Box for a certain condition 2
How to fix Error 2465 8
SQL to combine columns of multiple rows into one row 9
VBA to Query. 2
REPOST Re: Problem running the example in MS KB Article 304428 - How to Use the Query by Form (QBF) 4
How to create a Query for a report based on a unrelated lookup table. 4
Multiple Criteria to run Parameter Query for a report 1
Problem with Proc Containing Temp Table for use in ADP Report 2

Top