Append Query with 2 Subqueries Runs extremely slow

[Hmadyson]

Hi helpful people,

I have been trying to do research on my own, but it is not helping.

What I want to do:

I have 2 tables with multiple records per person. For every record in table 1, I want to know what the previous date from table 2 is and also what the next date in table 2 is.

I have tried doing the following:

Running an Update query to the table using Dmin and Dmax functions (way too slow)
Running an update query to the table using subqueries (query says it is not updatable)
Running an append query using subqueries (the select query runs in about 10 sec, the same append query just continuously runs and I get too annoyed to wait for it to finish.

Table A has 1.8 million records
Table B has over 400,000 records

Here is the code below, and here is information Table A=Work_Hours1 has PK of SSN, CalcDate, and HoursDate1, also added an index on HoursDate1
Table B=WorkPayStatus1900 has PK of SSN, CalcDate, and PSEffDate, also added an index on PSEffDate.
HoursWithStatuses is blank when running, and has a PK on SSN, CalcDate, and HoursDate1

I also turned UseTransaction to False

[tt]INSERT INTO HoursWithStatuses ( ssn, calcdate, HoursDate1, Hours1, HoursEndDate1, PrevStatDate, NextStatDate )
SELECT h.ssn, h.calcdate, h.HoursDate1, h.Hours1, h.HoursEndDate1, (Select Max(p.PSEffDate) FROM WorkPayStatus1900 AS p WHERE (p.PSEffDate<=h.HoursDate1 and p.ssn=h.ssn and p.calcdate=h.calcdate)) AS PrevStatDate, (Select Min(p.PSEffDate) FROM WorkPayStatus1900 AS p WHERE (p.PSEffDate>=h.HoursDate1 and p.ssn=h.ssn and p.calcdate=h.calcdate)) AS NextStatDate
FROM Work_Hours1 AS h;[/tt]

Anyone have any ideas?
I can also add a where to the Work_Hours1 on the Hours field since I only care about outliers which would make the total set about 65,000 records versus 1.8 million, but that is not really helping the speed.

Thanks so much!

 

[ByteMyzer]

You might try the following SQL:

INSERT INTO HoursWithStatuses
    ( ssn, calcdate, HoursDate1, Hours1, HoursEndDate1, PrevStatDate, NextStatDate )
SELECT h.ssn, h.calcdate, h.HoursDate1, h.Hours1, h.HoursEndDate1, x.PrevStatDate, n.NextStatDate
FROM Work_Hours1 AS h
INNER JOIN (
    SELECT p.ssn, p.calcdate, Max(p.PSEffDate) AS PrevStatDate
    FROM WorkPayStatus1900 AS p
    INNER JOIN Work_Hours1 AS h ON p.ssn=h.ssn and p.calcdate=h.calcdate
    WHERE p.PSEffDate<=h.HoursDate1
    GROUP BY p.ssn, p.calcdate) AS x ON h.ssn=x.ssn and h.calcdate=x.calcdate
INNER JOIN (
    SELECT p.ssn, p.calcdate, Min(p.PSEffDate) AS NextStatDate
    FROM WorkPayStatus1900 AS p
    INNER JOIN Work_Hours1 AS h ON p.ssn=h.ssn and p.calcdate=h.calcdate
    WHERE p.PSEffDate>=h.HoursDate1
    GROUP BY p.ssn, p.calcdate) AS n ON h.ssn=n.ssn and h.calcdate=n.calcdate;