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.
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;”
[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.
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
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.
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.
[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)
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).
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=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.