Can't find query that works and is fast enough...

[Daniel]

I'm a newbie to databases and SQL, but here's my problem as best I can
describe. I'm using Access 2000 on WinXP.

I have a single table (CustomerVisits) that has CustomerID,
VisitTimeDate, and ItemPurchased as fields.

I want a query that returns the latest item that each Customer
purchased in the last 30 days. i.e., if I had 20,000 unique customers
in the last 30 days, I want what each of of them purchased on their
latest visit.

I came up with a query:

SELECT tbl1.ItemPurchased FROM CustomerVisits As tbl1
WHERE VisitTimeDate = (
SELECT Max(tbl2.VisitTimeDate) FROM CustomerVisits As tbl2
WHERE tbl2.CustomerID = tbl1.CustomerID AND
tbl2.VisitTimeDate >= curr_date - 30 AND
tbl2.VisitTimeDate <= curr_date
);

This works fine when there are only a few thousand records in the
table, but the more records I have, the longer it takes. With 1.5
million records, the time it takes to retrieve the results is
unacceptable.

I've indexed CustomerID and VisitTimeDate, but still too long.

I found the following query that is plenty fast even with 1.5M records:

SELECT CustomerID, Max(VisitTimeDate) FROM CustomerVisits
WHERE VisitTimeDate >= (curr_date - 30) AND
VisitTimeDate <= (curr_date)
GROUP BY CustomerID;

This gives me the CustomerID and VisitTimeDate of all the records that
I want, but not the ItemPurchased field from those records. I cannot,
for the life of me, figure out how to modify the above query to return
only (or at least including) the ItemPurchased field. Any suggestions?

 

[Dale Fye]

How about:

SELECT CV.CustomerID, CV.VisitTimeDate, CV.ItemPurchased
FROM CustomerVisits as CV
INNER JOIN (SELECT CustomerID, MAX(VisitTimeDate) as MostRecent
FROM CustomerVisits
WHERE VisitTimeDate > dateadd("d", -30, Date())
GROUP BY CustomerID) as t1
ON CV.CustomerID = t1.CustomerID AND CV.VisitTimeDate = t1.MostRecent

Does your table have indexes on it? If not you might want to try indexing
on CustomerID and VisitTimeDate as separate indexes which allow duplicates

HTH
Dale

 

[Daniel]

Thank you, that works much better!. More than two orders of magnitude
faster than the old query. I was already indexing the CustomerID and
VisitTimeDate, so it must have been the join.

Thanks!

 

[Dale Fye]

Glad I could help.

Thank you, that works much better!. More than two orders of magnitude
faster than the old query. I was already indexing the CustomerID and
VisitTimeDate, so it must have been the join.

Thanks!