# Iterate between dates and INSERT values in a performant way

I have created a query that populates a datapoint with random values. The logic is simple: Iterate between the START and END dates and INSERT random values.

I want from this query to be very perfomant. For example populate every second of a year with values (what with this code will last ages). I am new to SQL statements of this complexity and I dont know the pitfalls and of it.

Are there some hidden areas in my code that can be improved? If I replace the random function with just a hardcoded value will it cause much boost? Is a loop with a lot of INSERT INTO time consuming; Is there a better way to Insert (some kind of batch insert)?

DO $$DECLARE --Variables NODE_ID bigint := 11; -- The node id of the datapoint TIMESTAMP_START TIMESTAMP := '2018-12-06 22:00:00'; TIMESTAMP_END TIMESTAMP := '2018-12-10 00:00:00'; TS_STEP INTERVAL := '30 minute'; MAX_VALUE integer := 100; BEGIN LOOP EXIT WHEN TIMESTAMP_START > TIMESTAMP_END; INSERT INTO datapoint_values (dp_id, ts, datatype, source, int_value, float_value) VALUES (NODE_ID, TIMESTAMP_START, 2, 0, floor(random()*(MAX_VALUE+1)), 0); TIMESTAMP_START := TIMESTAMP_START + TS_STEP; END LOOP; END$$;

• Do you care about the quality of the randomness, or do you just want some arbitrary values? – 200_success Dec 11 '18 at 9:33
• @200_success If I had I would choose performance, so quality of randomness is a background feature. I want maximum perfomance that coulbe be pressed out of this code. – Predicate Dec 11 '18 at 9:38

Instead of a loop which inserts one row after the other you should use generate_series to create all timestamps at once.
DO $$DECLARE --Variables NODE_ID bigint := 11; -- The node id of the datapoint TIMESTAMP_START TIMESTAMP := '2018-12-06 22:00:00'; TIMESTAMP_END TIMESTAMP := '2018-12-10 00:00:00'; TS_STEP INTERVAL := '30 minute'; MAX_VALUE integer := 100; BEGIN INSERT INTO datapoint_values (dp_id, ts, datatype, source, int_value, float_value) SELECT (NODE_ID, x, 2, 0, floor(random()*(MAX_VALUE+1)), 0) FROM generate_series(TIMESTAMP_START, TIMESTAMP_END, TS_STEP) t(x); END$$;