Slow execution of the function due to the execution of dynamically generated queries defined inside it - Mailing list pgsql-general
| From | Rajat Katyal |
|---|---|
| Subject | Slow execution of the function due to the execution of dynamically generated queries defined inside it |
| Date | |
| Msg-id | 006c01c40db0$5a220280$2105a8c0@coffee Whole thread Raw |
| In response to | ("Rajat Katyal" <rajatk@intelesoftech.com>) |
| List | pgsql-general |
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;
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;
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;
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--------------------------
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;
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;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
pgsql-general by date: