Scotto123
asked on
Access only runs on one processor?
I was running this query today and my PC was freaking out!
SELECT Patient.PatientID, Patient.VisitID, Patient.AdmitDateTime, (select max([AdmitDateTime]) from Patient P where P.PatientID=Patient.Patien
FROM Patient
ORDER BY Patient.PatientID, Patient.AdmitDateTime;
The query ran for 30 minutes.
The CPU was maxed out and the RAM was all used up. I learned that Access only runs on one of the 4 CPUs I have. Is this right? Seems crazy that my PC can handle so much and it doesn't utilize the full system. Please let me know what I can do to speed up queries like this.
SELECT Patient.PatientID, Patient.VisitID, Patient.AdmitDateTime, (select max([AdmitDateTime]) from Patient P where P.PatientID=Patient.Patien
FROM Patient
ORDER BY Patient.PatientID, Patient.AdmitDateTime;
The query ran for 30 minutes.
The CPU was maxed out and the RAM was all used up. I learned that Access only runs on one of the 4 CPUs I have. Is this right? Seems crazy that my PC can handle so much and it doesn't utilize the full system. Please let me know what I can do to speed up queries like this.
Brian Harrington
What version are you using?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have Access 2010. I could run the data in SQL Server Mgt Studio. I am anayzing data and want to be able to do it easier without having to build SQL tables and such. Maybe I should change my analysis style.
It's your nested select statements that are causing the problem.
Try creating a query like this:
SELECT N.PatientID, N.AdmitDateTime, Max(P.AdmitDateTime) as PrevAdmit, Max(P.DischargeDateTime) as PrevDischarge
FROM Patients N
LEFT JOIN Patients P
ON N.PatientID = P.PatientID
AND P.DischargeDateTime < N.AdmitDateTime
GROUP BY N.PatientID, N.AdmitDateTime
That should give you every PatientID, Admission, and the patients most recent previous admission and discharge. If that works, then use that as a subquery and join it to your Patients table on PatientID and AdmitDateTime to get the other fields you need from the Patients table.
But do you really need every patient/admit combination? If not you could limit the results of this query significantly by adding a WHERE clause on the N.AdmitDateTime field. Or by minimizing the number of records in the N part of the query by using something like:
SELECT N.PatientID, N.AdmitDateTime, Max(P.AdmitDateTime) as PrevAdmit, Max(P.DischargeDateTime) as PrevDischarge
FROM (SELECT PatientID, MAX(AdmitDateTime) as MostRecentVisit FROM Patients) as N
LEFT JOIN Patients P
ON N.PatientID = P.PatientID
AND P.DischargeDateTime < N.MostRecentVisit
GROUP BY N.PatientID, N.AdmitDateTime
By using the subquery in this, you limit N to one record per patient, and the resulting recordset, with the PrevAdmit and PrevDischarge will only contain one record as well.
Try creating a query like this:
SELECT N.PatientID, N.AdmitDateTime, Max(P.AdmitDateTime) as PrevAdmit, Max(P.DischargeDateTime) as PrevDischarge
FROM Patients N
LEFT JOIN Patients P
ON N.PatientID = P.PatientID
AND P.DischargeDateTime < N.AdmitDateTime
GROUP BY N.PatientID, N.AdmitDateTime
That should give you every PatientID, Admission, and the patients most recent previous admission and discharge. If that works, then use that as a subquery and join it to your Patients table on PatientID and AdmitDateTime to get the other fields you need from the Patients table.
But do you really need every patient/admit combination? If not you could limit the results of this query significantly by adding a WHERE clause on the N.AdmitDateTime field. Or by minimizing the number of records in the N part of the query by using something like:
SELECT N.PatientID, N.AdmitDateTime, Max(P.AdmitDateTime) as PrevAdmit, Max(P.DischargeDateTime) as PrevDischarge
FROM (SELECT PatientID, MAX(AdmitDateTime) as MostRecentVisit FROM Patients) as N
LEFT JOIN Patients P
ON N.PatientID = P.PatientID
AND P.DischargeDateTime < N.MostRecentVisit
GROUP BY N.PatientID, N.AdmitDateTime
By using the subquery in this, you limit N to one record per patient, and the resulting recordset, with the PrevAdmit and PrevDischarge will only contain one record as well.
ASKER
You anwered my question harrington. Thanks!