Re: Dynamically built and EXECUTEd query executing slowly

On Friday 19 March 2004 07:29, Rajat Katyal wrote:
> Hi:
>
> The below pasted trigger function is executing slow
>
> Please refer to the lines of the code in BOLD commented as performance
> degradation.

A small point Rajat, but try and include a subject-line next time - it makes
it easier for people to know if they can help.

> The same code I have mentioned below also:
>
> open cursorExistingRows for EXECUTE checkPKSql;
> fetch cursorExistingRows into tempRecord;
>
> When I commented out this statement, the function output is fast when i
> tested for more than 1000 records otherwise its take almost double time.
>
> Please tell me how to solve this problem.

OK - so below is all the important stuff (though I'm not clear what you're
trying to do).

> updateSql := substring(updateSql, 0, length(updateSql)-1);
> checkPKSql := 'select itm_id_tgt from transform_item_tgt ';
> updateSql := updateSql || ' where itm_id_tgt = ' ||
> quote_literal(new.itm_id_tgt); checkPKSql := checkPKSql || ' where
> itm_id_tgt = ' || quote_literal(new.itm_id_tgt); --Raise notice 'the query
> is: %', checkPKSql;
>
> ----------------------PERFORMANCE DEGRADATION--------------------------
> open cursorExistingRows for EXECUTE checkPKSql;
> fetch cursorExistingRows into tempRecord;
> ----------------------PERFORMANCE DEGRADATION--------------------------

OK - so creating a cursor and fetching from is slower that not doing so. Not
surprising to be honest. I'd not bother with the cursor, just run the query
and check FOUND. If you write it as something like:

SELECT INTO my_var itm_id_tgt FROM transform_item_tgt WHERE itm_id_tgt =
NEW.itm_id_tgt;
IF FOUND ...

That should save you planning time, but make sure when you compile the trigger
function it thinks using an index is a good idea.

See what difference that makes.

PS - what exactly are you trying to do here?
--
Richard Huxton
Archonet Ltd