SQL query very slow - weird!

[diwin]

(sort of a re-post ... re-examined and re-worded better )

The following query (1) will run fine -- it takes about 0.5 seconds.
It contains a lot of criteria, including 2 specific criteria (bolded and colored), one of which I have called the "Problem Item", that takes 45 seconds to run when paired on their own.

This long query string is the fast one, immediately below. The short (s-l o-o-o-w) query string that I stripped out of the long query is below it.

My question... Why is the short one so slow? It shouldn't be since it is made up of components from the long, fast one?

This is the long query that runs in less than one second...

QUERY (1) ... contains "Problem Item"
SELECT * FROM qryDonationSearchOutput
WHERE
((
Int(donationEntryDate) >= #5/18/2005# AND Int(donationEntryDate) < #11/10/2005#
AND donationAmount > 5
AND donationAmount <= 88
AND donationTypeID = 11
AND campaignID = 1
AND (((contactNameLast LIKE '*aaaaaaa*') OR (contactNameLast LIKE '*bbbbbb*') OR (contactNameLast LIKE '*ccccccc*'))
OR ((parentName LIKE '*candace*') OR (parentName LIKE '*fffffffff*')
OR (parentName LIKE '*ggggggggg*') OR (parentName LIKE '*hhhhhhh*')))
AND parentTypeID <> 12
AND [blue]donationID IN (SELECT dtDesignation_FdonationID FROM tblDTDesignation WHERE dtDesignation_FdesignationTypeID = 2) [/blue]
AND donationID IN (SELECT dtmemorial_FdonationID FROM tblDtMemorial WHERE dtmemorial_FLocation = 23)
AND donationPaid = -1
AND donationGroupID <> 0
AND donationID IN (SELECT dtReceipt_FdonationID FROM tblDtReceipt WHERE ((dtReceiptNumber = '00044')
OR (dtReceiptNumber = '00055') OR (dtReceiptNumber = '00066')))
OR (donationNotes LIKE '*kkkkk*')
))
OR (donationID = 970)
ORDER BY donationID DESC

This is the s-l-o-o-w-w query that takes 45 seconds to run...

QUERY (2) ... contains "Problem Item"
[SELECT * FROM qryDonationSearchOutput
WHERE
((
[blue]donationID IN (SELECT dtDesignation_FdonationID FROM tblDTDesignation WHERE dtDesignation_FdesignationTypeID = 2)[/blue]
))
OR (donationID = 970)
ORDER BY donationID DESC]

Additional Info:

Additionally indexed fields: dtDesignation_FdonationID, dtDesignation_FdesignationTypeID.

Also, by themselves, the two components of the slow query run fast. Just when those 2 get together there is a problem.

tblDTDesignation has about the same number of records as tblDTDesignation.

There are a total of 3,495 donation records
When I change the value of dtDesignation_FdesignationTypeID in QUERY (2) then QUERY (2) takes the following times (in seconds) to execute...

2,369 records have dtDesignation_FdesignationTypeID = 2 20.00 s
361 records have dtDesignation_FdesignationTypeID = 7 5.00 s
51 records have dtDesignation_FdesignationTypeID = 12 1.00 s
8 records have dtDesignation_FdesignationTypeID = 25 0.60 s
1 record has dtDesignation_FdesignationTypeID = 30 0.40 s

QUERY (3) ...(No criteria before "Problem Item")
SELECT * FROM qryDonationSearchOutput
WHERE ((donationID IN (SELECT dtDesignation_FdonationID FROM tblDTDesignation WHERE dtDesignation_FdesignationTypeID = 2)))
ORDER BY donationID DESC 0.10 s

QUERY (4) ... does not contain "Problem Item"
SELECT * FROM qryDonationSearchOutput
WHERE (donationID = 970)
ORDER BY donationID DESC] 0.04 s

QUERY (5) ... nothing before "Problem Item", but plenty after it.
SELECT * FROM qryDonationSearchOutput
WHERE
((
[blue]donationID IN (SELECT dtDesignation_FdonationID FROM tblDTDesignation WHERE dtDesignation_FdesignationTypeID = 2) [/blue]
AND donationID IN (SELECT dtmemorial_FdonationID FROM tblDtMemorial WHERE dtmemorial_FLocation = 23)
AND donationPaid = -1
AND donationGroupID <> 0
AND donationID IN (SELECT dtReceipt_FdonationID FROM tblDtReceipt WHERE ((dtReceiptNumber = '00044')
OR (dtReceiptNumber = '00055') OR (dtReceiptNumber = '00066')))
OR (donationNotes LIKE '*kkkkk*')
))
OR (donationID = 970)
ORDER BY donationID DESC 20.0 s



Daniel Dillon
o (<--- brain shown at actual size.)

 

[diwin]

Oops.
The "Problem Item" is the Bold Blue text.

Daniel Dillon
o (<--- brain shown at actual size.)

 

[diwin]

Oops Oops!

"tblDTDesignation has about the same number of records as tblDTDesignation"

...should read...

tblDTDesignation has about the same number of records as qryDonationSearchOutput

Daniel Dillon
o (<--- brain shown at actual size.)