Are two INSERT commands slower than one?

Databases
1

I have a PostgreSQL 9.1 database where I am requiring to send either one or two insert commands copied from data already in the database.

Does it make much of a difference (in execution time) to have Xojo send two commands like this:

[code]sql = “INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID = 123;”
myDbase.SQLExecute(sql)

sql = “INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID = 456;”
myDbase.SQLExecute(sql)[/code]

as compared to this:

sql = "INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID = 123;" sql = sql + "INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID = 456;" myDbase.SQLExecute(sql)

Just wondering.

2

I can’t say for sure, but I’d assume it would be at the very least marginally (but not noticeably) faster.

Even better would be combining both into one

sql = “INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID = 123 OR ID = 456;”

This way, you’re only pulling data from the db once

3

it will be really faster on a remote database. you send only one thing, versus two things to the remote server.
on a local database, it will be roughly the same.

4

Great idea, but the two commands will be slightly different so it would have to be sent as two queries. Only one record is being pulled from the customers database. Does it make a difference to put LIMIT 1 after the command, such as:

sql = “INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID = 123 LIMIT 1;”

5

[quote=287242:@Jean-Yves Pochez]it will be really faster on a remote database. you send only one thing, versus two things to the remote server.
on a local database, it will be roughly the same.[/quote]
Yes, it is a remote database.

6

wrap it in a TRANSACTION, this way the remote database gets it all at once, instead of the overhead of transmitting one command at a time, plus it provides the ability to rollback the entire set if necessary

7

Why not:

sql =INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID IN (123,456);"
That way you don’t need a transaction.

[quote]Does it make a difference to put LIMIT 1 after the command, such as:

sql = “INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID = 123 LIMIT 1;”[/quote]

Nope, as there will be only one customer record with the ID 123 anyway (unless your data isn’t hopelessly screwed). The limit clause would just be noise in your statement.

8

LIMIT and OFFSET are mainly for SELECT command. I don’t see where else it can be used ?

9

If you are talking truly just TWO statements, then you won’t notice a difference, but what I read was you were going to generete two statements for multiple input records, and then it could make a big difference.

10

[quote]LIMIT and OFFSET are mainly for SELECT command. I don’t see where else it can be used ?
[/quote]

David is using the Insert into …select - syntax. Using limit here is legal though pointless.

11

[quote=287440:@Maximilian Tyrtania]David is using the Insert into …select - syntax. Using limit here is legal though pointless.

[/quote]
Thank you.

I didn’t know if, using LIMIT 1, the query would be faster if once it hit one record that satisfied the query, it would stop instead of continue to scan the entire table. I’m guessing not.

So, this is the preferred method?

sql = "BEGIN TRANSACTION;"
sql = sql + "INSERT INTO policies (id, field1, field2, field3)  SELECT id, field1, field3, field3 FROM customers WHERE ID = 123;"
sql = sql + "INSERT INTO policies (id, field1, field2, field3)  SELECT id, field1, field3, field3 FROM customers WHERE ID = 456;"
sql = sql + "END TRANSACTION;"
myDbase.SQLExecute(sql)
12

No. Beginning and ending the transaction in the same query is no different than not having a transaction at all. I believe Dave was assuming you were doing many of these at a time (and hence the question - if you were doing just one set of inserts, the difference would be negligible).

13

So it would be more like this:

myDbase.SQLExecute("BEGIN TRANSACTION")

sql = "INSERT INTO policies (id, field1, field2, field3)  SELECT id, field1, field3, field3 FROM customers WHERE ID = 123;"
myDbase.SQLExecute(sql)

sql = "INSERT INTO policies (id, field1, field2, field3)  SELECT id, field1, field3, field3 FROM customers WHERE ID = 456;"
myDbase.SQLExecute(sql)

myDbase.SQLExecute("END TRANSACTION")
14

Yes, checking db.Error after every Execute. If you get an error, you can roll the entire thing back and preserve the integrity of your data.

15

Actually if you concatenate commands like this:

SQL=“Do this; Do that; Do something else”; Postgres wraps them in a transaction for you - you don’t need to do that yourself. If “Do that” fails neither “Do this” nor “Do something else” will be executed.

And as I’ve mentioned you have the option of
sql =“INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID IN (123,456);”

16

[quote=287564:@Maximilian Tyrtania]Actually if you concatenate commands like this:

SQL=“Do this; Do that; Do something else”; Postgres wraps them in a transaction for you - you don’t need to do that yourself. If “Do that” fails neither “Do this” nor “Do something else” will be executed.

And as I’ve mentioned you have the option of
sql =“INSERT INTO policies (id, field1, field2, field3) SELECT id, field1, field3, field3 FROM customers WHERE ID IN (123,456);”[/quote]
Thank you very much.