Re: Query is taking 5 HOURS to Complete on 8.1 version

On 7/3/07, smiley2211 <smiley2211(at)yahoo(dot)com> wrote:

> CREATE OR REPLACE VIEW temp_consent2 AS
> SELECT DISTINCT temp_consent.id, temp_consent.daterecorded
> FROM temp_consent
> WHERE temp_consent.answer::text = 'Yes'::text
> ORDER BY temp_consent.daterecorded DESC, temp_consent.id;

Get rid of the order by on this view. It is a waste of resources. If you
need it ordered else where, order it on the fly i.e. select * from
temp_consent2 order by .....

CREATE OR REPLACE VIEW people_consent AS
> SELECT people.id, people.firstname, people.lastname, people.homephone,
> people.workphone, people.altphone, people.eligibilityzipcode,
> people.address1, people.address2, people.city, people.state,
> people.zipcode1, people.zipcode2, people.email, people.dayofbirth,
> people.monthofbirth, people.yearofbirth, people.ethnic_detail,
> people.external_id, people.highestlevelofeducation_id,
> people.ethnicgroup_id, people.ethnicotherrace, people.entered_at,
> people.entered_by, people.besttimetoreach_id, people.language_id,
> people.otherlanguage, people.gender_id, people.hispaniclatino_id,
> people.canscheduleapt_id, people.mayweleaveamessage_id, people.ethnictribe
> ,
> people.ethnicasian, people.ethnicislander
> FROM people
> WHERE (people.id IN ( SELECT temp_consent2.id
> FROM temp_consent2))
> UNION
> SELECT people.id, '***MASKED***' AS firstname, '***MASKED***' AS lastname,
> '***MASKED***' AS homephone, '***MASKED***' AS workphone, '***MASKED***'
> AS
> altphone, '***MASKED***' AS eligibilityzipcode, '***MASKED***' AS
> address1,
> '***MASKED***' AS address2, '***MASKED***' AS city, '***MASKED***' AS
> state,
> '***MASKED***' AS zipcode1, '***MASKED***' AS zipcode2, people.email,
> '***MASKED***' AS dayofbirth, '***MASKED***' AS monthofbirth,
> '***MASKED***'
> AS yearofbirth, people.ethnic_detail, people.external_id,
> people.highestlevelofeducation_id, people.ethnicgroup_id,
> people.ethnicotherrace, people.entered_at, people.entered_by,
> people.besttimetoreach_id, people.language_id, people.otherlanguage,
> people.gender_id, people.hispaniclatino_id, people.canscheduleapt_id,
> people.mayweleaveamessage_id, people.ethnictribe, people.ethnicasian,
> people.ethnicislander
> FROM people
> WHERE NOT (people.id IN ( SELECT temp_consent2.id
> FROM temp_consent2));

Try linking the people and temp_consent2 like this
where people.id not in (select temp_consent2.id from temp_consent2 where
temp_consent2.id = people.id)

That will help a lot.

HTH,

Chris