3
\$\begingroup\$

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 $$;
\$\endgroup\$
  • \$\begingroup\$ Do you care about the quality of the randomness, or do you just want some arbitrary values? \$\endgroup\$ – 200_success Dec 11 '18 at 9:33
  • \$\begingroup\$ @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. \$\endgroup\$ – Predicate Dec 11 '18 at 9:38
4
\$\begingroup\$

Instead of a loop which inserts one row after the other you should use generate_series to create all timestamps at once.

Untested:

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 $$;
\$\endgroup\$
  • \$\begingroup\$ Would be great if it worked, but im getting errors \$\endgroup\$ – Predicate Jan 8 at 10:12
  • 1
    \$\begingroup\$ getting errors is not very helpful. The Select itself works fine, see dbfiddle.uk/… I just noticed that I used the start timestamp in both places, fixed. \$\endgroup\$ – dnoeth Jan 8 at 10:26
  • \$\begingroup\$ I didn't know about generate_series. Nice solution. \$\endgroup\$ – Carra Jan 8 at 10:37

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.