2

I'm using libpq (but I am potentially be ready to switch to other library)

I have a bunch of similar INSERT queries I want to make, they are different only in values between them.

I'd like to use prepared statements to avoid issues with escaping fields. I also want to send them in one batch so that I don't spend roundtrip for each query.

I know only this options:

Use PQExec() with multiple insert, divided by semicolon: This doesn't allow prepared statements, so I need to create query dynamically

Use PQexecParams() for each query separately: This has problems of multiple roundtrips spent

Use PQprepare() + PQExecPrepared() for each query separately: Same problem as previous

Use PQexecParams() for single query with several inserted lines e.g INSERT INTO tablename (a,b,c) VALUES ("A", "B", "C"), ("aa", "bb", "cc"): It's a bit better than the first option, because it allow not to escape arguments, but still requires building query dynamically (and the worst part you'll have to increment number of arguments ($1, $2, $3) for first line but ($4,$5,$6) for the second, etc)

So, how to avoid these drawbacks?

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
RiaD
  • 42,649
  • 10
  • 67
  • 110

1 Answers1

1

You want to use your last option, one INSERT statement to insert multiple rows. However adding more and more rows will eventually not give you an additional speed increase. This happens when the performance is mainly determined by the database and no longer by the network or other factors. Let's say this happens around 20 rows.

You can create one prepared statement to insert 20 rows. Perform this for as many times as you need.

For any remaining rows, you will have to create a new prepared statement to insert the remaining rows.

You can use the same approach without prepared statements, but then you have to escape all values, as you already mentioned. However if this is a very specific case, this might not be an issue.

See also this answer, and the links contained therein, for more advice on how to speed up bulk inserts.

rveerd
  • 2,498
  • 1
  • 9
  • 22