Speeding up a most recent date query

  • Thread starter Thread starter annysjunkmail
  • Start date Start date
A

annysjunkmail

Hio Group,

After reading several newsgroup postings, I was able to construct the
following SQL, which filters records according to the most recent date,
but my problem is that it's so slow. There are only 5,000 records in
my DB and it takes about approx 4 mins to return 175 records. Is there
any way of speeding it up or maybe someone can suggest a faster but
different approach?

SELECT qryProjectEndDatesLeaderPlus.ApplicationRefNo,
qryProjectEndDatesLeaderPlus.NewProjectEndDate,
qryProjectEndDatesLeaderPlus.OperationalProgrammeName
FROM qryProjectEndDatesLeaderPlus
WHERE (((qryProjectEndDatesLeaderPlus.NewProjectEndDate)=(Select
Max(t.[NewProjectEndDate]) as LastDate
FROM qryProjectEndDatesLeaderPlus as T
WHERE T.ApplicationRefNo =
qryProjectEndDatesLeaderPlus.ApplicationRefNo)));


Thanks
Tony
 
Instead of using a coordinated sub-query in the WHERE clause, try using a
subquery in the FROM clause.

Two query solution:
Create and save a query that gets the Maximum end date for each Project
(qLastProjEndDate)
Select ApplicationRefNo,
Max(NewProjectEndDate) as LastDate
FROM qryProjectEndDatesLeaderPlus
GROUP BY ApplicationRefNo

Now use that query along with qryProjectEndDatesLeaderPlus
SELECT ...
FROM qryProjectEndDateLeaderPlus INNER JOIN qLastProjEndDate
ON qryProjectEndDatesLeaderPlus.NewProjectEndDate =
qlastProjeEndDate.LastDate
AND qryProjectEndDatesLeaderPlus.ApplicationRefNo =
qlastProjeEndDate.ApplicationRefNo

You can often combine that into one query, that looks like the following
UNTESTED SQL statement
SELECT qryProjectEndDatesLeaderPlus.ApplicationRefNo,
qryProjectEndDatesLeaderPlus.NewProjectEndDate,
qryProjectEndDatesLeaderPlus.OperationalProgrammeName
FROM qryProjectEndDatesLeaderPlus INNER JOIN
(Select ApplicationRefNo,
Max(NewProjectEndDate) as LastDate
FROM qryProjectEndDatesLeaderPlus
GROUP BY ApplicationRefNo) as T
ON qryProjectEndDatesLeaderPlus.NewProjectEndDate = T.LastDate AND
qryProjectEndDatesLeaderPlus.ApplicationRefNo = T.ApplicationRefNo
 
Superb answer John...ran in under 3 secs!!!
Thanks very much - top drawer stuff

Tony

John said:
Instead of using a coordinated sub-query in the WHERE clause, try using a
subquery in the FROM clause.

Two query solution:
Create and save a query that gets the Maximum end date for each Project
(qLastProjEndDate)
Select ApplicationRefNo,
Max(NewProjectEndDate) as LastDate
FROM qryProjectEndDatesLeaderPlus
GROUP BY ApplicationRefNo

Now use that query along with qryProjectEndDatesLeaderPlus
SELECT ...
FROM qryProjectEndDateLeaderPlus INNER JOIN qLastProjEndDate
ON qryProjectEndDatesLeaderPlus.NewProjectEndDate =
qlastProjeEndDate.LastDate
AND qryProjectEndDatesLeaderPlus.ApplicationRefNo =
qlastProjeEndDate.ApplicationRefNo

You can often combine that into one query, that looks like the following
UNTESTED SQL statement
SELECT qryProjectEndDatesLeaderPlus.ApplicationRefNo,
qryProjectEndDatesLeaderPlus.NewProjectEndDate,
qryProjectEndDatesLeaderPlus.OperationalProgrammeName
FROM qryProjectEndDatesLeaderPlus INNER JOIN
(Select ApplicationRefNo,
Max(NewProjectEndDate) as LastDate
FROM qryProjectEndDatesLeaderPlus
GROUP BY ApplicationRefNo) as T
ON qryProjectEndDatesLeaderPlus.NewProjectEndDate = T.LastDate AND
qryProjectEndDatesLeaderPlus.ApplicationRefNo = T.ApplicationRefNo


Hio Group,

After reading several newsgroup postings, I was able to construct the
following SQL, which filters records according to the most recent date,
but my problem is that it's so slow. There are only 5,000 records in
my DB and it takes about approx 4 mins to return 175 records. Is there
any way of speeding it up or maybe someone can suggest a faster but
different approach?

SELECT qryProjectEndDatesLeaderPlus.ApplicationRefNo,
qryProjectEndDatesLeaderPlus.NewProjectEndDate,
qryProjectEndDatesLeaderPlus.OperationalProgrammeName
FROM qryProjectEndDatesLeaderPlus
WHERE (((qryProjectEndDatesLeaderPlus.NewProjectEndDate)=(Select
Max(t.[NewProjectEndDate]) as LastDate
FROM qryProjectEndDatesLeaderPlus as T
WHERE T.ApplicationRefNo =
qryProjectEndDatesLeaderPlus.ApplicationRefNo)));


Thanks
Tony
Click to expand...
Click to expand...
 
Back
Top