Re: Query taking too long - please help

From: <andrewf_at_jaredgroup.com>
Date: Mon, 08 Feb 1999 19:09:16 GMT
Message-ID: <79ncol$eba$1@nnrp1.dejanews.com>

I applogize for my carelessness. It is of course more than that.

Select ...
from patient p, visti v1,

(Select acctid, source, Max(updatedate)
      From visit group by acctid, source) v2 ...
and use Merge hint

Or
Select ...
from patient p,

(Select acctid, source, domain

      From visit v1
      Where updatedate = (Select Max(updatedate) ...)

....

Or
Select ...
from patient p,

(Select acctid, source, domain,

      From visit v1,


     (Select acctid, source, Max(updatedate)


      From visit      group by acctid, source) v2 ...)

if the syntax supports it.

It's the join order, the most recent visit needs to be computed to eliminate most rows from visit, then join with patient and patientkin info. Merge or NL? I guess one is not always faster than the other for this join.

This is one of SQL's limitation. Ideally you should be able to go through visit table just once.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Feb 08 1999 - 13:09:16 CST