SQL string hangs when executing

[dannyboyo]

When the first string runs, it takes 45 seconds to execute. When I run the
second string, it runs immediately. Any idea why??????

SELECT * FROM qryDonationSearchOutput
WHERE (donationID IN (SELECT dtDesignation_FdonationID FROM tblDTDesignation
WHERE dtDesignation_FdesignationTypeID = 2) AND ((parentName LIKE
'*candace*')))
OR (donationID = 970)
ORDER BY donationID DESC

SELECT * FROM qryDonationSearchOutput
WHERE (donationID IN (SELECT dtDesignation_FdonationID FROM tblDTDesignation
WHERE dtDesignation_FdesignationTypeID = 2) AND ((parentName LIKE
'*candace*')))
ORDER BY donationID DESC

 

[dannyboyo]

The tblDTDesignation table has roughly the same number of records as
qryDonationSearchOutput and this query runs fast with most criteria. But
something obscene is happening as described above. I was told the "OR"
operator can confuse access because it has to query twice and merge the
results, but many searches using SEVERAL "OR's" and SEVERAL "LIKE's" run
fast. For example...

SELECT * FROM qryDonationSearchOutput
WHERE (Int(donationEntryDate) >= #5/11/2005#
AND Int(donationEntryDate) < #11/3/2005#
AND donationAmount > 20
AND donationAmount <= 50
AND donationTypeID = 1
AND campaignID = 1
AND (((contactNameLast LIKE '*wrmosca*'))
OR ((parentName LIKE '*ddddddddddd*')
OR (parentName LIKE '*tttttttttttt*')
OR (parentName LIKE '*kkkkkkkkkkk*')))
AND parentTypeID <> 2
AND donationGroupID <> 0
AND donationID IN (SELECT dtReceipt_FdonationID FROM tblDtReceipt WHERE
((dtReceiptNumber = '00003')
OR (dtReceiptNumber = '00004')
OR (dtReceiptNumber = '00005')
OR (dtReceiptNumber = '00006')))
AND ((donationID = 66)
OR (donationID = 77)
OR (donationID = 88))
AND ((donationNotes LIKE '*eeeee*')
OR (donationNotes LIKE '*ttttt*')))
OR (donationID = 613)
OR (donationID = 613)
ORDER BY donationID DESC

....runs in less than one second.

Just the above comination in my first question screws up, that I can find.
Is it a syntax problem? A sequence problem, placing them in the wrong order
in the string? Is it a problem with (brackets)? Why does...

"(donationID IN (SELECT dtDesignation_FdonationID FROM tblDTDesignation
WHERE dtDesignation_FdesignationTypeID = 2)"

....not work fast when...

"OR (donationID = 970)"

....is in the same string??? Whatever it is, I cannot see it. Makes no sense
to my small brain.

Question 1 : Indexing? Y/N
Question 2: Problem with tblDTDesignation?

 

[dannyboyo]

This one takes just over one second to execute. It has plenty of "OR
(donationID = 2635)" clauses, but no "AND donationID IN (SELECT
dtDesignation_FdonationID FROM tblDTDesignation WHERE
dtDesignation_FdesignationTypeID = 2)" clause...

So why the 45-second wait??? I remain stumped.
(<--- brain shown at actual size.)
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 '*eeeeeee*')
OR (parentName LIKE '*fffffffff*')
OR (parentName LIKE '*ggggggggg*')
OR (parentName LIKE '*hhhhhhh*')))
AND parentTypeID <> 12
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')))
AND ((donationID = 777)
OR (donationID = 888)
OR (donationID = 999))
AND ((donationNotes LIKE '*jjjjj*')
OR (donationNotes LIKE '*hhhhh*')
OR (donationNotes LIKE '*kkkkk*')))
OR (donationID = 3471) OR (donationID = 3471)
OR (donationID = 2994) OR (donationID = 2994)
OR (donationID = 2874) OR (donationID = 2874)
OR (donationID = 2874) OR (donationID = 2874)
OR (donationID = 2874) OR (donationID = 2874)
OR (donationID = 2874) OR (donationID = 2874)
OR (donationID = 2822) OR (donationID = 2822)
OR (donationID = 2822) OR (donationID = 2638)
OR (donationID = 2638) OR (donationID = 2635)
OR (donationID = 2635) OR (donationID = 2635)
OR (donationID = 2309) OR (donationID = 2309)
OR (donationID = 2257) OR (donationID = 2257)
OR (donationID = 2212) OR (donationID = 2212)
OR (donationID = 2212) OR (donationID = 2212)
OR (donationID = 2212) OR (donationID = 2032)
OR (donationID = 2032) OR (donationID = 1831)
OR (donationID = 1831) OR (donationID = 1831)
OR (donationID = 1831) OR (donationID = 1831)
OR (donationID = 970) OR (donationID = 970)
OR (donationID = 613) OR (donationID = 613)
ORDER BY donationID DESC

 

[Robert Morley]

I've seen a variety of problems like this in Access when you use subqueries.
Try writing two queries, as follows (this is written by-hand, so I don't
promise it's perfect, but should get you on the right track):

Query 1:
SELECT dtDesignation_FdonationID FROM tblDTDesignation
WHERE dtDesignation_FdesignationTypeID = 2

Query 2: (Assumes Query 1 was saved as Q1)
SELECT * FROM qryDonationSearchOutput
RIGHT JOIN Q1 ON qryDonationSearchOutput.donationID =
Q1.dtDesignation_FdonationID
WHERE (qryDonationSearchOutput.donationID = 970) OR
((Q1.dtDesignation_FdonationID IS NOT NULL) AND
(qryDonationSearchOutput.parentName LIKE '*candace*'))
ORDER BY donationID DESC

If you only need the queries temporarily, simply delete them after you're
done with...if I remember correctly, it's been a long
time...CurrentDB().TableDefs().Delete <table name>...but you probably
already know that.


Rob

 

[Alex Dybenko]

Try to enable showplan and see what is going on.
look for JETSHOWPLAN in this article:
http://msdn.microsoft.com/archive/d...MicrosoftAccessMicrosoftJetDatabaseEngine.asp