I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day.
My initial approach was just clear data from my table and re-insert all records again.
-- CLEAR MY TABLE
DELETE FROM MY_TABLE;
-- POPULATE MY TABLE WITH MY FUNCTION'S RESULT
INSERT INTO MY_TABLE (COLUMN1, COLUMN2, COLUMN3)
SELECT COLUMN1, COLUMN2, COLUMN3
FROM MY_FUNCTION(PARAM1, PARAM2, PARAM3);
So far, so good. But my table has many triggers and when the function returns thousands of records, this approach shows to be very inefficient.
Then, I moved to this approach:
-- CREATE A TEMPORARY TABLE
CREATE GLOBAL TEMPORARY TABLE MY_TEMP_TABLE
(COLUMN1 TEXT, COLUMN2 TEXT, COLUMN3 TEXT);
-- POPULATE MY TEMP TABLE WITH MY FUNCTION'S RESULT
INSERT INTO MY_TEMP_TABLE (COLUMN1, COLUMN2, COLUMN3)
SELECT COLUMN1, COLUMN2, COLUMN3
FROM MY_FUNCTION(PARAM1, PARAM2, PARAM3);
-- CREATE AN INDEX FOR HELP PERFORMANCE
CREATE INDEX MY_TEMP_TABLE_INDEX ON MY_TEMP_TABLE (COLUMN1, COLUMN2, COLUMN3);
-- DELETE FROM MY TABLE WHERE NOT EXISTS IN MY TEMP TABLE
DELETE FROM MY_TABLE T
WHERE NOT EXISTS (SELECT 1
FROM MY_TEMP_TABLE T2
WHERE T2.COLUNN1 = T.COLUMN1);
-- UPDATE MY TABLE WHERE COLUMNS ARE DIFFERENT IN MY TEMP TABLE
UPDATE MY_TABLE T
SET COLUMN2 = T2.COLUMN2,
COLUMN3 = T2.COLUMN3
FROM MY_TEMP_TABLE T2
WHERE T2.COLUNN1 = T.COLUMN1
AND (T2.COLUMN2 <> T.COLUMN2 OR T2.COLUMN3 <> T.COLUMN3);
-- INSERT INTO MY TABLE WHER EXISTS IN MY TEMP TABLE
INSERT INTO FROM MY_TABLE T (COLUMN1, COLUMN2, COLUMN3)
(SELECT COLUMN1, COLUMN2, COLUMN3
FROM MY_TEMP_TABLE T2
WHERE NOT EXISTS (SELECT 1 FROM TABLE T3 WHERE T3.COLUNN1 = T2.COLUMN1);
But I still having performance issues. I believe that creating this temp_table consumes very much resources. Beside, I think this isn't the best way of doing it.
Can you guys suggest another approach? Or do you believe this is the best way?
EDIT:
For testing, you can run these scripts above:
Here is the script to creating tables / triggers / functions / etc...
-- THIS TABLE CONTAINS INFORMATION THAT USERS NEED
CREATE TABLE USER_INFO (USER_ID TEXT, INFO_ID TEXT, INFO1 TEXT, INFO2 TEXT, INFO3 TEXT, INFO4 TEXT, INFO5 TEXT);
ALTER TABLE USER_INFO ADD CONSTRAINT USER_INFO_PK PRIMARY KEY (USER_ID, INFO_ID);
-- THIS TABLE CONTAINS A KIND OF FLAG, INDICATING FOR USERS THEIR INFORMATION HAS BEEN "REFRESHED" AND THEY SHOULD GET ROWS FROM "USER_INFO"
CREATE TABLE USER_HAS_NEW_INFO (USER_ID TEXT, INFO_DATE TIMESTAMP);
ALTER TABLE USER_HAS_NEW_INFO ADD CONSTRAINT USER_HAS_NEW_INFO_PK PRIMARY KEY (USER_ID, INFO_DATE);
-- CREATE TRIGGER FUNCTION
CREATE OR REPLACE FUNCTION TF_USER_INFO()
RETURNS trigger AS
$BODY$
begin
-- IF SOME INFO HAS CHANGED
if (TG_OP = 'INSERT')
OR
(
(TG_OP = 'UPDATE')
AND
(
(COALESCE(NEW.INFO1,'') <> COALESCE(OLD.INFO1,'')) OR
(COALESCE(NEW.INFO2,'') <> COALESCE(OLD.INFO2,'')) OR
(COALESCE(NEW.INFO3,'') <> COALESCE(OLD.INFO3,'')) OR
(COALESCE(NEW.INFO4,'') <> COALESCE(OLD.INFO4,'')) OR
(COALESCE(NEW.INFO5,'') <> COALESCE(OLD.INFO5,''))
)
)
then
-- INSERT A NEW ROW INTO USER_HAS_NEW_INFO
INSERT INTO USER_HAS_NEW_INFO (USER_ID, INFO_DATE)
SELECT NEW.USER_ID, CURRENT_TIMESTAMP
WHERE NOT EXISTS (SELECT 1
FROM USER_HAS_NEW_INFO
WHERE USER_ID = NEW.USER_ID
AND INFO_DATE = CURRENT_TIMESTAMP
);
end if;
RETURN NEW;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;
-- CREATE TRIGGER
CREATE TRIGGER T_USER_INFO
AFTER INSERT OR UPDATE OR DELETE
ON USER_INFO
FOR EACH ROW
EXECUTE PROCEDURE TF_USER_INFO();
CREATE OR REPLACE FUNCTION CALCULATE_USERS_INFO()
RETURNS SETOF USER_INFO AS
$BODY$
DECLARE
vUSER_INFO USER_INFO%rowtype;
BEGIN
-- HERE GOES A COMPLEX QUERY PLUS SOME CALCS AND VALIDATIONS
-- BUT, FOR TESTING PORPOUSES, WE CAN DO FOLLOWING:
FOR vUSER_INFO IN
SELECT USER_ID,
INFO_ID,
'A=' || TRUNC(RANDOM() * 1000) || '|' ||
'B=' || TRUNC(RANDOM() * 1000) || '|' ||
'C=' || TRUNC(RANDOM() * 1000) AS INFO1,
'A=' || TRUNC(RANDOM() * 1000) || '|' ||
'B=' || TRUNC(RANDOM() * 1000) || '|' ||
'C=' || TRUNC(RANDOM() * 1000) AS INFO2,
'A=' || TRUNC(RANDOM() * 1000) || '|' ||
'B=' || TRUNC(RANDOM() * 1000) || '|' ||
'C=' || TRUNC(RANDOM() * 1000) AS INFO3,
'A=' || TRUNC(RANDOM() * 1000) || '|' ||
'B=' || TRUNC(RANDOM() * 1000) || '|' ||
'C=' || TRUNC(RANDOM() * 1000) AS INFO4,
'A=' || TRUNC(RANDOM() * 1000) || '|' ||
'B=' || TRUNC(RANDOM() * 1000) || '|' ||
'C=' || TRUNC(RANDOM() * 1000) AS INFO5
FROM GENERATE_SERIES(1,1500) AS USER_ID
CROSS JOIN GENERATE_SERIES(1,500) AS INFO_ID
LOOP
RETURN NEXT vUSER_INFO;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
And here is the script I run many times a day:
-- CREATE A TEMPORARY TABLE
CREATE GLOBAL TEMPORARY TABLE USER_INFO_TEMP
(USER_ID TEXT, INFO_ID TEXT, INFO1 TEXT, INFO2 TEXT, INFO3 TEXT, INFO4 TEXT, INFO5 TEXT)
ON COMMIT DROP;
-- POPULATE MY TEMP TABLE WITH MY FUNCTION'S RESULT
INSERT INTO USER_INFO_TEMP (USER_ID, INFO_ID, INFO1, INFO2, INFO3, INFO4, INFO5)
SELECT USER_ID, INFO_ID, INFO1, INFO2, INFO3, INFO4, INFO5
FROM CALCULATE_USERS_INFO();
-- CREATE AN INDEX FOR HELP PERFORMANCE
CREATE INDEX USER_INFO_TEMP_INDEX ON USER_INFO_TEMP (USER_ID, INFO_ID);
-- DELETE FROM MY TABLE WHERE NOT EXISTS IN MY TEMP TABLE
DELETE FROM USER_INFO T
WHERE NOT EXISTS (SELECT 1
FROM USER_INFO_TEMP T2
WHERE T2.USER_ID = T.USER_ID
AND T2.INFO_ID = T.INFO_ID);
-- UPDATE MY TABLE WHERE COLUMNS ARE DIFFERENT IN MY TEMP TABLE
UPDATE USER_INFO T
SET INFO1 = T2.INFO1,
INFO2 = T2.INFO2,
INFO3 = T2.INFO3,
INFO4 = T2.INFO4,
INFO5 = T2.INFO5
FROM USER_INFO_TEMP T2
WHERE T2.USER_ID = T.USER_ID
AND T2.INFO_ID = T.INFO_ID
AND (T2.INFO1 <> T.INFO1 OR
T2.INFO2 <> T.INFO2 OR
T2.INFO3 <> T.INFO3 OR
T2.INFO4 <> T.INFO4 OR
T2.INFO5 <> T.INFO5
);
-- INSERT INTO TABLE WHERE EXISTS IN TEMP AND NOT EXISTS IN TABLE
INSERT INTO USER_INFO (USER_ID, INFO_ID, INFO1, INFO2, INFO3, INFO4, INFO5)
(SELECT USER_ID, INFO_ID, INFO1, INFO2, INFO3, INFO4, INFO5
FROM USER_INFO_TEMP T2
WHERE NOT EXISTS (SELECT 1
FROM USER_INFO T3
WHERE T3.USER_ID = T2.USER_ID
AND T3.INFO_ID = T2.INFO_ID
)
);
