Access only runs on one processor?

Posted on 2012-09-11
Last Modified: 2012-09-11
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.PatientID and P.AdmitDateTime<Patient.AdmitDateTime) AS PrevAdmissionDate, DateDiff("d",[PrevAdmissionDate],[AdmitDateTime]) AS AdmissonDiff, Patient.DischargeDateTime,(select max([DischargeDateTime]) from Patient P where P.PatientID=Patient.PatientID and P.DischargeDateTime<Patient.DischargeDateTime) AS PrevDischargeDate, DateDiff("d",[PrevDischargeDate],[DischargeDateTime]) AS DischargeDiff
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.
Question by:Scotto123
    LVL 9

    Expert Comment

    What version are you using?
    LVL 9

    Accepted Solution

    Actually that doesn't matter. Access does not support multi threading.  Consider using SQL Express which does.

    Author Comment

    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.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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.

    Author Closing Comment

    You anwered my question harrington.  Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now