INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Append Query with 2 Subqueries Runs extremely slow

Append Query with 2 Subqueries Runs extremely slow

(OP)
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

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;


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!


RE: Append Query with 2 Subqueries Runs extremely slow

You might try the following SQL:

CODE -->

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; 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close