Re: Query taking too long - please help

Hi again,

An update: I created a temporary table in place of the correlated subquery and the query now takes about 7 hours instead of 7 days to complete. However, I am still a little confused because like I said in my original post I had ran the query with the correlated subquery the other day in 5 hours. I suspect that it has something to do with the way the optimizer chose the execution path ( of course, what else could it be). By the way I have CHOOSE as my optimizer setting and I'm not sure if all of my tables were analyzed when I got the good performance of 5 hours out of that query. Any insights would be greatly appreciated!! Lisa

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 - 09:38:27 CST