[AccessD] query to slow

Rocky Smolin rockysmolin at bchacc.com
Tue Sep 28 10:15:30 CDT 2010


Is Patientnr indexed? 

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pedro at plex.nl
Sent: Tuesday, September 28, 2010 5:05 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] query to slow

Dear List,

i have a query <qryKreatinine> with about 400000 records.
This query contains patientnumbers and dates


<qryKreatinine>
Patientnr   UitvoerDatum
01000616    1-6-2009
01000616    29-7-2009
01000661    1-5-2010
01000661    3-6-2010
01000661    3-6-2010
01000832    11-9-2009
01000832    17-9-2009
01000832    1-3-2010

I need to have for each Patientnumber, the last date (see final result).
I did this with two queries (see below), but only with low number queries (a
few thousand records.
With more then 500000 records it takes to much time to get the result.

Can i do this with 1 query in a normal time span for 400000 records??


Thanks

Pedro




SELECT qryKreatinine.Patientnr, qryKreatinine.UitvoerDatum FROM
qryKreatinine WHERE qryKreatinine.UitvoerDatum =(SELECT MAX(T.UitvoerDatum)
as MaxDate FROM qryKreatinine as T WHERE T.Patientnr =
qryKreatinine.Patientnr);


result:
Patientnr   UitvoerDatum
01000616    29-7-2009
01000616    29-7-2009
01000661    3-6-2010
01000661    3-6-2010
01000661    3-6-2010
01000832    1-3-2010
01000832    1-3-2010
01000832    1-3-2010




SELECT qryKreatinine_LaatsteDatum.Patientnr,
qryKreatinine_LaatsteDatum.UitvoerDatum
FROM qryKreatinine_LaatsteDatum
GROUP BY qryKreatinine_LaatsteDatum.Patientnr,
qryKreatinine_LaatsteDatum.UitvoerDatum
HAVING (((qryKreatinine_LaatsteDatum.Patientnr) In (SELECT [Patientnr] FROM
[qryKreatinine_LaatsteDatum] As Tmp GROUP BY [Patientnr]))) ORDER BY
qryKreatinine_LaatsteDatum.Patientnr; 

final result:
Patientnr   UitvoerDatum
01000616    29-7-2009
01000661    3-6-2010
01000832    1-3-2010
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list