Re: Query taking too long

From: Lisa Lewis <lewis_at_med.pitt.edu>
Date: Fri, 5 Feb 1999 16:05:14 -0500
Message-ID: <79fmjk$8s$1@usenet01.srv.cis.pitt.edu>

explain plan:

FILTER
NESTED LOOPS OUTER 7727 1

      NESTED LOOPS OUTER  7725 1
        NESTED LOOPS   7721 2
          TABLE ACCESS FULL VISIT 7715 3
          TABLE ACCESS BY INDEX ROWID PATIENT 2 455567
            INDEX UNIQUE SCAN PATIENT_PK_ACCTID 1 455567
        TABLE ACCESS BY INDEX ROWID PATIENTNKIN 2 105151
          INDEX UNIQUE SCAN PATIENTNKIN_PK 1 105151
      TABLE ACCESS BY INDEX ROWID PATIENTNKIN 2 105151
        INDEX UNIQUE SCAN PATIENTNKIN_PK 1 105151
    SORT AGGREGATE   1
      TABLE ACCESS BY INDEX ROWID VISIT 6 3
        INDEX RANGE SCAN VISIT_PK 3 3

for

    SELECT p.mrn, p.source, p.lname, p.fname, p.mname, p.dob, p.sex, p.race,
           p.addr1, p.addr2, p.city, p.state, p.zip, p.country, p.hphone,
           p.bphone, p.acctid, p.ssn, v.domain,
           nr.lname, nr.fname, nr.addr1, nr.addr2,
           nr.city, nr.state, nr.zip, nr.country, nr.hphone,
           ec.lname, ec.fname, ec.addr1, ec.addr2,
           ec.city, ec.state, ec.zip, ec.country, ec.hphone

FROM Mars.patient p, Mars.patientnkin nr, Mars.Visit v, Mars.patientnkin ec

    WHERE p.acctid = nr.acctid (+)
    AND p.source = nr.source (+)
    AND nr.contactrole (+) = 'NR'
    AND p.acctid = ec.acctid (+)
    AND p.source = ec.source (+)
    AND ec.contactrole (+) = 'EC'

    AND   p.acctid = v.acctid
    AND   p.source = v.source
    AND   v.updatedate = ( SELECT MAX(v2.updatedate)
                           FROM visit v2
                           WHERE v2.acctid = v.acctid
                           AND v2.source = v.source );

Again, any insight is appreciated!

Lisa Lewis wrote in message <79epvu$qnb$1_at_usenet01.srv.cis.pitt.edu>...
>Hi all,
>
>I am hoping someone can give me suggestions on how to get this query to run
>faster. This query is part of a pro*c program.
>
> EXEC SQL DECLARE c_adtdemo CURSOR FOR
> SELECT p.mrn, p.source, p.lname, p.fname, p.mname, p.dob, p.sex,
p.race,
> p.addr1, p.addr2, p.city, p.state, p.zip, p.country, p.hphone,
> p.bphone, p.acctid, p.ssn, v.domain,
> nr.lname, nr.fname, nr.addr1, nr.addr2,
> nr.city, nr.state, nr.zip, nr.country, nr.hphone,
> ec.lname, ec.fname, ec.addr1, ec.addr2,
> ec.city, ec.state, ec.zip, ec.country, ec.hphone
> FROM Mars.patient p, Mars.patientnkin nr, Mars.Visit v,
Mars.patientnkin
>ec
> WHERE p.acctid = nr.acctid (+)
> AND p.source = nr.source (+)
> AND nr.contactrole (+) = 'NR'
> AND p.acctid = ec.acctid (+)
> AND p.source = ec.source (+)
> AND ec.contactrole (+) = 'EC'
> AND p.acctid = v.acctid
> AND p.source = v.source
> AND v.updatedate = ( SELECT MAX(v2.updatedate)
> FROM visit v2
> WHERE v2.acctid = v.acctid
> AND v2.source = v.source );
>
>Note: the purpose of this query is to extract patient demographics along
>with data about the patients latest visit and some next of kin information.
>A little background: The patient table has about 500,000 rows. The visit
>table has about 2,000,000 rows and the patientnkin has about 450,000 rows.
>The pk for all of the tables has acctid and source as the first two
elements
>of the pk. There is no indexes defined on ec.contactrole. Note: This is
a
>decision support system with no users currently so there is no activity
>going on that should slow this down.
>
>I am obviously most concerned about the correlated subquery. What is
>strange is that the other day I ran this query against the tables described
>above and it took about 5 hours and produced about 460,000 rows. The next
>day I ran this query, based on the progress it was making, estimations
would
>put it at about 7 days to complete. The only thing I did between the two
>runs is to re-analyze some tables so that the statistics could be most up
to
>date. ( I thought that this would help but it seemed to have the opposite
>effect). I am now in the process of deleting some of my statistics to see
>what the impact of that would be. I know that I can create a temporary
>table to instead of using the correlated subquery, I will try that as a
>last resort. The strange thing is that this query did once run to
>completion in 5 hours.
>
>Please respond via e-mail.
>I would appreciate all help!!!
>
>Thanks
>Lisa
>
>
Received on Fri Feb 05 1999 - 15:05:14 CST

Re: Query taking too long - please help