- Mailing list pgsql-general
| From | Rajat Katyal |
|---|---|
| Subject | |
| Date | |
| Msg-id | 000801c40d83$fb217fd0$2105a8c0@coffee Whole thread Raw |
| Responses |
Re: Dynamically built and EXECUTEd query executing slowly
Re: |
| List | pgsql-general |
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;
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.
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;
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;
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: