I have the below mentioned function written in plpgsql which is working fine. I want someone to review it and let me know how can I improve its performance.
This function takes data from staging table and inserts into target tables. Before it inserts, it checks for the existing records, and if any records exist, it does a DELETE CASCADE
in two tables. Here I have a script for a DELETE
command in bills table, which in turn deletes from the bills_head_details
table as well.
It currently takes about 10-15 minutes to delete about 1200 records in bills table which has an approximate 1:6 ratio with the bills_head_details
. But I feel that 15 minutes for these many records is a very long duration. How can I improve its performance?
CREATE OR REPLACE FUNCTION ccdb.o_bills_update_for_update(integer)
RETURNS void AS
$BODY$
DECLARE
count integer;
vw_cnt integer;
BEGIN
SELECT ccdb_view_cnt
INTO vw_cnt
FROM ccdb.update_qtable
WHERE status_flag = 1
AND ddu_area = 2
AND ddu_action = 2
AND section_code = $1;
IF vw_cnt > 0 THEN
--delete from bills the records which are being updated
DELETE FROM ccdb.bills
WHERE bill_id IN (SELECT bill_id
FROM ccdb.bills
WHERE mbc_bill_id IN ( SELECT bu.mbc_bill_id
FROM ccdb.o_bills_update_stg bu
FULL JOIN ccdb.consumers c USING (consumer_num)
FULL JOIN ccdb.bill_type_master bt USING (bill_type_id)
JOIN ccdb.update_qtable uq ON bu.ccdb_qtable_id = uq.id
AND bu.ddu_action = 2
AND uq.ddu_action = 2
AND uq.ddu_area = 2
AND uq.status_flag = 1
WHERE bu.section_code = $1));
--insertion into bills which got updated
INSERT INTO ccdb.bills
(
bill_id
,source_system_id
,mbc_bill_id
,mbc_bill_no
,cin
,cust_connection_id
,consumer_number
,cust_type_flag
,bill_type_group_code
,bill_type_code
,bill_month
,total_consumption
,bill_date
,due_date
,dc_date
,dues_remind_date
,dc_remind_date
,org_unit_id
,parent_bill_id
,category_flag
,status_flag
,conn_cat_subgroup_id
,dispute_flag
,inst_flag
,approved_date
,bill_amt
,paid_amt
,past_arrear
,arrear_collected
,advance_amount
,creation_dt
,created_by
,last_update_dt
,last_updated_by
)
SELECT nextval('ccdb.bills_seq')
,1
,p.mbc_bill_id
,p.mbc_bill_no
,c.cin
,p.cust_connection_id
,coalesce(p.consumer_num,p.applicant_num)
,p.cust_type_flag
,p.bill_type_group_code
,bt.bill_type
,p.bill_month
,p.total_consumption
,p.bill_date
,p.due_date
,p.dc_date
,p.dues_remaind_date
,p.dc_remaind_date
,p.section_code
,p.parent_bill_id
,p.category_flag
,p.status_flag
,p.conn_cat_subgroup_id
,p.dispute_flag
,p.inst_flag
,p.approved_date
,p.bill_amt
,p.paid_amt
,p.past_arrear
,p.arrear_collected
,p.advance_amount
,now()
,'system'
,now()
,'system'
FROM ccdb.o_bills_update_stg p
FULL JOIN ccdb.consumers c USING (consumer_num)
FULL JOIN ccdb.bill_type_master bt USING (bill_type_id)
JOIN ccdb.update_qtable uq ON p.ccdb_qtable_id = uq.id
AND p.ddu_action = 2
AND uq.ddu_action = 2
AND uq.ddu_area = 2
AND uq.status_flag = 1
WHERE p.section_code = $1;
--insertion into bill head details
INSERT INTO ccdb.bill_head_details
( bill_id
,charge_head_code
,amount_billed
,amount_paid
,charge_head_descr
,ccdb_update_time
,creation_dt
,created_by
,tariff_id
,demand_date
,last_update_dt
,last_updated_by
)
SELECT i.bill_id
,h.charge_head_code
,h.amount_billed
,h.amount_paid
,NULL
,now()
,now()
,'system'
,h.tariff_id
,h.demand_date
,now()
,'system'
FROM ccdb.bills i
JOIN ccdb.o_bills_details_update_stg h ON h.mbc_bill_id = i.mbc_bill_id
JOIN ccdb.update_qtable uq ON h.ccdb_qtable_id = uq.id
AND h.ddu_action = 2
AND uq.ddu_action = 2
AND uq.ddu_area = 2
AND uq.status_flag = 1
AND uq.section_code = $1;
-- Updating the update qtable
SELECT COUNT(*)
INTO count
FROM ccdb.o_bills_update_stg pu,ccdb.update_qtable uq
WHERE pu.ddu_action = 2
AND pu.ccdb_qtable_id = uq.id
AND uq.ddu_area = 2
AND uq.ddu_action = 2
AND uq.status_flag = 1
AND uq.section_code = $1;
UPDATE ccdb.update_qtable uq
SET status_flag = 2
,ccdb_ucount = count
,ccdb_ulmdts = now()
,ccdb_remarks = 'Success'
FROM ccdb.o_bills_update_stg bu
WHERE uq.ddu_area = 2
AND bu.ddu_action = 2
AND uq.ddu_action = 2
AND uq.status_flag = 1
AND uq.id = bu.ccdb_qtable_id
AND uq.section_code = $1;
ELSE IF vw_cnt = 0 THEN
UPDATE ccdb.update_qtable uq
SET status_flag = 2
,ccdb_ucount = vw_cnt
,ccdb_ulmdts = now()
,ccdb_remarks = 'Success'
WHERE uq.ddu_area = 2
AND uq.ddu_action = 2
AND uq.status_flag = 1
AND uq.section_code = $1;
END IF;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
The Tables are something like this:
--Bills Table
CREATE TABLE ccdb.bills
(
bill_id bigint NOT NULL,
source_system_id smallint NOT NULL,
mbc_bill_id bigint,
mbc_bill_no numeric(13,0) NOT NULL,
cin numeric(13,0),
cust_connection_id bigint,
consumer_number numeric(13,0) NOT NULL,
cust_type_flag smallint,
bill_type_group_code character varying(30),
bill_type_code character varying(30),
bill_month character varying(30),
total_consumption numeric(12,3),
bill_date timestamp without time zone,
due_date timestamp without time zone,
dc_date timestamp without time zone,
dues_remind_date timestamp without time zone,
dc_remind_date timestamp without time zone,
org_unit_id smallint,
parent_bill_id bigint,
category_flag smallint,
status_flag smallint NOT NULL,
conn_cat_subgroup_id smallint,
dispute_flag smallint,
inst_flag smallint,
approved_date timestamp without time zone,
bill_amt numeric(13,2),
paid_amt numeric(13,2),
past_arrear numeric(13,2),
arrear_collected numeric(13,3),
advance_amount numeric(13,3),
creation_dt timestamp without time zone NOT NULL DEFAULT now(),
created_by character varying(30) NOT NULL,
last_update_dt timestamp without time zone,
last_updated_by character varying(30),
CONSTRAINT bills_pk PRIMARY KEY (bill_id)
)
WITH (
OIDS=FALSE
);
--Bill Details
CREATE TABLE ccdb.bill_head_details
(
bill_id bigint NOT NULL,
charge_head_code character varying(30),
amount_billed numeric(13,3),
amount_paid numeric(13,3),
charge_head_descr character varying(80),
ccdb_update_time timestamp without time zone,
creation_dt timestamp without time zone NOT NULL DEFAULT now(),
created_by character varying(30) NOT NULL,
last_update_dt timestamp without time zone,
last_updated_by character varying(30),
tariff_id character varying(20),
demand_date timestamp without time zone
)
WITH (
OIDS=FALSE
);
-- Bill Details Staging
CREATE TABLE ccdb.o_bills_details_update_stg
(
mbc_bill_id bigint,
demand_date timestamp(6) without time zone,
tariff_id bigint,
charge_head_code character varying(30),
amount_billed numeric(13,3),
amount_paid numeric(13,3),
ddu_action smallint,
onet_entry_time timestamp without time zone,
ccdb_qtable_id integer
)
WITH (
OIDS=FALSE
);
-- Bills Staging
CREATE TABLE ccdb.o_bills_update_stg
(
mbc_bill_id bigint,
mbc_bill_no numeric(13,0),
cust_connection_id bigint,
consumer_num numeric(13,0),
applicant_num bigint,
cust_type_flag smallint,
section_code integer,
bill_type_group_code character varying(30),
bill_type_id integer,
bill_type_code character varying(30),
bill_month bigint,
total_consumption numeric(12,3),
bill_date timestamp without time zone,
due_date timestamp without time zone,
dc_date timestamp without time zone,
dues_remaind_date timestamp without time zone,
dc_remaind_date timestamp without time zone,
parent_bill_id bigint,
category_flag smallint,
status_flag smallint,
conn_cat_subgroup_id smallint,
dispute_flag smallint,
inst_flag integer,
approved_date timestamp without time zone,
bill_amt numeric(13,2),
paid_amt numeric(13,2),
past_arrear numeric(13,2),
advance_amount numeric(13,3),
arrear_collected numeric(13,3),
ddu_action smallint,
onet_entry_time timestamp without time zone,
ccdb_qtable_id integer
)
WITH (
OIDS=FALSE
);
--Update Qtable
CREATE TABLE ccdb.update_qtable
(
id bigserial NOT NULL,
entry_time timestamp(6) without time zone NOT NULL,
section_code integer NOT NULL,
ddu_area smallint NOT NULL,
ddu_action smallint,
start_id bigint,
end_id bigint,
total_cnt integer,
status_flag smallint,
ccdb_ulmdts timestamp(6) without time zone,
onet_remarks text,
ccdb_remarks text,
ccdb_ucount integer,
onet_ackts timestamp(6) without time zone,
ccdb_view_cnt integer,
onet_ack_cnt integer,
ccdb_staging_time timestamp(0) without time zone,
CONSTRAINT update_qtable_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
These are the five tables that I am using while writing this function.
What it actually does is, we get date from the client in View which is populated in the staging tables using Talend Open Studio and we use the Function to populate the target from staging. Here in this 1 record in the bills Staging table can have a new record or a record which has an updated value for a bill which already exists in the target table. One bill can have multiple records corresponding to the bill id in the bill details target table. So when I get updated bills, then I delete the old record from the bills table and the bill details table which has the old value and insert it as a new record with a new bill_id.