Re: Speeding up subselect ?

On Mon, 8 Jul 2002 18:10:05 +0200, "Peter Alberer"
<h9351252@obelix.wu-wien.ac.at> wrote:
>Hi there,
>
>i have the following query in a pl/pgsql procedure:
>
>    update lr_object_usage
>    set status = (case status
>        when ''OPEN_SUCC'' then ''CLOSED_SUCC''
>        when ''OPEN_FAIL'' then ''CLOSED_FAIL'' end)
>    where lr_object_usage_id in (select lr_object_usage_id from
>lr_locked_objects where context = p_exam_usage_id);
>
>the problem is the result of the subselect is obviously calculated for
>every row of lr_object_usage (30K rows) -> the update takes very long.

Peter, try

UPDATE lr_object_usage
SET status = CASE status
             WHEN ''OPEN_SUCC'' THEN ''CLOSED_SUCC''
             WHEN ''OPEN_FAIL'' THEN ''CLOSED_FAIL''
             END
FROM lr_locked_objects o
WHERE lr_object_usage.lr_object_usage_id = o.lr_object_usage_id
  AND <onetable>.context = <othertable>.p_exam_usage_id;

I didn't figure out where context and p_exam_usage_id come from.
Use at your own risk, I did not test it.

Servus
 Manfred