Re: Speeding up subselect ?

On Mon, 8 Jul 2002 18:10:05 +0200, "Peter Alberer"
<h9351252(at)obelix(dot)wu-wien(dot)ac(dot)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