Very slow query

[Bill Murphy]

The following query is taking about 70 seconds to run on my fast local PC.
I expected it to take 5 or 10 seconds at most. The two join fields are
indexed and each of the fields used in the criteria are indexed. The two
fields in the Order By are also indexed. tblDetail contains about 240,000
records and tblChecks contains 84,000 records. I would appreciate any
thoughts on this:

SELECT tblDetail.Date AS BankDate, tblDetail.Account,
tblDetail.Name, -[tbldetail]![amount] AS PaidAmount, tblDetail.Description,
tblDetail.BankReference, tblDetail.CustomerReference
FROM tblDetail LEFT JOIN tblChecks ON (tblDetail.Account = tblChecks.DDA)
AND (tblDetail.CustomerReference = tblChecks.CheckNumber)
WHERE
(((tblDetail.Date)<=[forms]![frmMain]![frmDisbClearingReconciliationContainer].[form]![txtReconciliationDate])
AND ((tblDetail.Account)=684968506) AND ((tblChecks.DDA) Is Null) AND
((tblChecks.CheckNumber) Is Null) AND ((tblDetail.ClearedDate) Is Null Or
(tblDetail.ClearedDate)>[forms]![frmMain]![frmDisbClearingReconciliationContainer].[form]![txtReconciliationDate])
AND ((tblDetail.UserCode)=475))
ORDER BY tblDetail.Account, tblDetail.CustomerReference;

Bill

 

[Dale Fye]

Bill,

I think part of your problem is that you are joining the table on fields
that you expect to contain null values (both tblChecks.DDA and
tblChecks.CheckNumber). When you do this with a Left Join, Jet will ignore
the records from the table on the "right" which have a NULL value in any of
the fields that are being joined.

It looks like what you are trying to do is identify the records in your
details table for a particular user (475) account (684968506), which were
created before a certain date which either have not cleared, or cleared after
the date indicated above, but I'm not sure what your tblChecks table is for.

Can you explain what your are trying to accomplish in words? If so, maybe I
can help you rewrite the query to get better results.

BTW, Date is a reservered word, and should not be used as a field name. If
you can change that field name to be more descriptive (TransDate, EntryDate,
....) it would be to your advantage.

Also, since you are specifying the account number in your where clause, you
can take that out of the Order By clause.

 

[Jerry Whittle]

Nulls aren't indexed so the criteria on the tblChecks is probably slowing you
down. You might be able to use NOT IN or NOT EXISTS statements on that table
to speed things up. I'd try converting it to a NOT EXISTS first and if that
doesn't help, try NOT IN.

Something like this might give you a starting place:

SELECT tblDetail.Date AS BankDate,
tblDetail.Account,
tblDetail.Name,
-[tblDetail]![amount] AS PaidAmount,
tblDetail.Description,
tblDetail.BankReference,
tblDetail.CustomerReference
FROM tblDetail
WHERE Not Exists (SELECT "X"
FROM tblChecks
WHERE tblDetail.Account = tblChecks.DDA
AND tblDetail.CustomerReference = tblChecks.CheckNumber
AND tblChecks.DDA Is Null
AND tblChecks.CheckNumber Is Null)
AND tblDetail.ClearedDate Is Null
AND tblDetail.Account=684968506
AND tblDetail.UserCode=475
ORDER BY tblDetail.Account, tblDetail.CustomerReference;

 

[Bill Murphy]

Jerry and Dale,

I tinkered with this query a bit more, did not make any significant changes,
but it's now running in about 2 seconds. Probably will never know why this
occurred.

Thanks for your help.

Bill