CODEWITHSUNDEEP
javapostgresql
Kenner Dev
Kenner Dev

Reputation: 327

POSTGRES Function taking long time after executing it few times

I am calling an simple upsert function in postgres using java.

DROP FUNCTION IF EXISTS UPSERT
CREATE FUNCTION UPSERT(integer,real,real,real,int,real,int,int,int,int,int,real,int,character varying,character varying,int,character varying, timestamp without time zone,real,int,int,int,int,boolean) RETURNS text AS $$

DECLARE
       reccount integer := 0;
       BEGIN


SELECT COUNT(*) INTO reccount FROM mytbl  WHERE id =  $1;    

IF reccount = 0 THEN 

    EXECUTE  ' INSERT INTO mytbl  
 VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, ST_SetSRID(ST_MakePoint($3, $2), 4326), $18 )'
 USING  $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22 , $23;              

ELSE 

 EXECUTE  'UPDATE mytbl  SET ..... WHERE id =  $1' USING $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19,
$20, $21, $22 , $23;    

END IF ;

RETURN 'V' || reccount ;    

END;
    $$ LANGUAGE plpgsql;

I am trying to write 2500 records.After writing 250+ records, it is slowing down the process a lot .After writing a few records, same operation is taking a long time.It was initially taking few milliseconds after writing 250+ records it slowly increase to 30 or 40 sec. However if I remove index and just inserts or updates with out doing a select first everything is working fine.It is select statement that is slowing down after a while.I am calling the above function again and again using a java for loop.

What could be happening ?

Below is the java code.

 CallableStatement upperProc = _conn.prepareCall("{ call UPSERT( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) }");
           //upperProc.registerOutParameter(1, Types.VARCHAR);

           upperProc.setInt(1, message.getsi());
           upperProc.setFloat(2, ..);
           ....
                   ....
           ....
                   upperProc.setInt(23, ...);
           upperProc.setBoolean(24,inCache );

Upvotes: 0

Views: 576

Answers (1)

RealSkeptic
RealSkeptic

Reputation: 34618

COUNT expects to use more than one row, and is very likely to take more time the more records there are in the table.

It's preferred to use an EXISTS query, which is always going to be looking for just a single row that matches the condition.

So we can dispose of the reccount variable, and write a condition like:

IF EXISTS (SELECT 1 FROM mytbl WHERE id = $1 ) THEN

    EXECUTE 'UPDATE mytbl ...';

ELSE

    EXECUTE 'INSERT INTO mytbl ...';

END IF;

Notes

  • If the insert and update statements are not built dynamically (they always have the same structure, just the values differ), it would be more efficient to use just INSERT INTO and UPDATE directly instead of EXECUTE which is used for dynamically-built queries.

  • Another option is to attempt to update the record, and if no record was updated, to do the insert. In this case, keep the variable reccount:

    EXECUTE 'UPDATE mytbl ...';

GET DIAGNOSTICS reccount = ROW_COUNT;

IF reccount = 0 THEN

     EXECUTE 'INSERT INTO mytbl ...';

END IF;

    If you take my first advice about not using EXECUTE, then you can use the FOUND variable instead:

    UPDATE mytbl SET field1 = $1, field2 = $2, ....;

IF NOT FOUND THEN

     INSERT INTO mytbl (field1, field2, ....) VALUES ($1, $2, ...);

END IF;

    GET DIAGNOSTICS and FOUND are documented in section 40.5.5 of the PostgreSQL documentation.

Upvotes: 1

Related Questions