Query taking too long

From: Lisa Lewis <lewis_at_med.pitt.edu>
Date: Fri, 5 Feb 1999 07:56:53 -0500
Message-ID: <79epvu$qnb$1@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 - 06:56:53 CST

Query taking too long - please help