Postgres Pro
Downloads
Home   >   mailing lists

Thread:

From
"Rajat Katyal"
Date:
Hi:
 
The below pasted trigger function is executing slow
 
Please refer to the lines of the code in BOLD commented as performance degradation.
 
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.
 
CREATE FUNCTION "public"."transform_item_tgtinsertupdate" () RETURNS trigger AS'
 
declare
cursorExistingRows refcursor;
tempRecord         record;
updateSql          varchar;
checkPKSql          varchar;
 
begin
updateSql = 'UPDATE transform_item_tgt set ';
IF NEW.itm_id_tgt is not null then
 updateSql := updateSql || ' itm_id_tgt = ' || quote_literal(NEW.itm_id_tgt) || ', ';
END IF;
IF NEW.custom_tariffs_tgt is not null then
 updateSql := updateSql || ' custom_tariffs_tgt = ' || quote_literal(NEW.custom_tariffs_tgt) || ', ';
END IF;
IF NEW.if_kit_tgt is not null then
 updateSql := updateSql || ' if_kit_tgt = ' || quote_literal(NEW.if_kit_tgt) || ', ';
END IF;
IF NEW.itm_name_tgt is not null then
 updateSql := updateSql || ' itm_name_tgt = ' || quote_literal(NEW.itm_name_tgt) || ', ';
END IF;
IF NEW.itm_price_unit_tgt is not null then
 updateSql := updateSql || ' itm_price_unit_tgt = ' || quote_literal(NEW.itm_price_unit_tgt) || ', ';
END IF;
IF NEW.itm_rate_tgt is not null then
 updateSql := updateSql || ' itm_rate_tgt = ' || quote_literal(NEW.itm_rate_tgt) || ', ';
END IF;
IF NEW.itm_type_tgt is not null then
 updateSql := updateSql || ' itm_type_tgt = ' || quote_literal(NEW.itm_type_tgt) || ', ';
END IF;
IF NEW.itm_unit_tgt is not null then
 updateSql := updateSql || ' itm_unit_tgt = ' || quote_literal(NEW.itm_unit_tgt) || ', ';
END IF;
IF NEW.status_tgt is not null then
 updateSql := updateSql || ' status_tgt = ' || quote_literal(NEW.status_tgt) || ', ';
END IF;
IF NEW.stock_tgt is not null then
 updateSql := updateSql || ' stock_tgt = ' || quote_literal(NEW.stock_tgt) || ', ';
END IF;
 
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--------------------------
 
if FOUND then
 execute updateSql;
 close cursorExistingRows;
 return null;
else
 close cursorExistingRows;
 return new;
end if;
 
close cursorExistingRows;
 
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Re: Dynamically built and EXECUTEd query executing slowly

From
Richard Huxton
Date:
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

Slow execution of the function due to the execution of dynamically generated queries defined inside it

From
"Rajat Katyal"
Date:
Hi:
 
I have a trigger function defined for the insert (pasted below) for the table say "transform_item_tgt" . The purpose of my trigger function is that before inserting each row it checks whether value in the primary key column exists or not. The checking is through executing the below mentioned code: 
 
open cursorExistingRows for EXECUTE checkPKSql;
fetch cursorExistingRows into tempRecord;
 
here checkPKSql is the dynamically generated query u can see that inside the function itself. If it fetch record it means that the primary key value already exists and accordingly i update the record instead of inserting it.
 
The problem is that the execution of function is slow. As far as i think the performance degradation is bcoz of execution of dynamically generated query for each row . As a query run is not prepared and saved just once during the life of the server.
 
Some more information
 
My insert query is:
insert into "transform_item_tgt" ("custom_tariffs_tgt", "if_kit_tgt", "itm_id_tgt", "itm_name_tgt", "itm_rate_tgt", "stock_tgt") select  "item"."custom_tariffs",  "item"."if_kit",  "item"."itm_id",  "item"."itm_name",  "item"."itm_rate",  "item"."stock" from "item";
 
Trigger is defned on the table "transform_item_tgt"
 
Please help me and suggest some solution for this problem.
 
Thanks in advance.
 
Rajat.
 
 
CREATE FUNCTION "public"."transform_item_tgtinsertupdate" () RETURNS trigger AS'
declare
cursorExistingRows refcursor;
tempRecord         record;
updateSql          varchar;
checkPKSql          varchar;
 
begin
updateSql = 'UPDATE transform_item_tgt set ';
IF NEW.itm_id_tgt is not null then
 updateSql := updateSql || ' itm_id_tgt = ' || quote_literal(NEW.itm_id_tgt) || ', ';
END IF;
IF NEW.custom_tariffs_tgt is not null then
 updateSql := updateSql || ' custom_tariffs_tgt = ' || quote_literal(NEW.custom_tariffs_tgt) || ', ';
END IF;
IF NEW.if_kit_tgt is not null then
 updateSql := updateSql || ' if_kit_tgt = ' || quote_literal(NEW.if_kit_tgt) || ', ';
END IF;
IF NEW.itm_name_tgt is not null then
 updateSql := updateSql || ' itm_name_tgt = ' || quote_literal(NEW.itm_name_tgt) || ', ';
END IF;
IF NEW.itm_price_unit_tgt is not null then
 updateSql := updateSql || ' itm_price_unit_tgt = ' || quote_literal(NEW.itm_price_unit_tgt) || ', ';
END IF;
IF NEW.itm_rate_tgt is not null then
 updateSql := updateSql || ' itm_rate_tgt = ' || quote_literal(NEW.itm_rate_tgt) || ', ';
END IF;
IF NEW.itm_type_tgt is not null then
 updateSql := updateSql || ' itm_type_tgt = ' || quote_literal(NEW.itm_type_tgt) || ', ';
END IF;
IF NEW.itm_unit_tgt is not null then
 updateSql := updateSql || ' itm_unit_tgt = ' || quote_literal(NEW.itm_unit_tgt) || ', ';
END IF;
IF NEW.status_tgt is not null then
 updateSql := updateSql || ' status_tgt = ' || quote_literal(NEW.status_tgt) || ', ';
END IF;
IF NEW.stock_tgt is not null then
 updateSql := updateSql || ' stock_tgt = ' || quote_literal(NEW.stock_tgt) || ', ';
END IF;
 
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--------------------------
 
if FOUND then
 execute updateSql;
 close cursorExistingRows;
 return null;
else
 close cursorExistingRows;
 return new;
end if;
 
close cursorExistingRows;
 
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 

Re:

From
Guy Fraser
Date:
No subject = No Answer

Rajat Katyal wrote:

...snip...