Evil Washing Machine Evil Washing Machine - 2 years ago 110
SQL Question

SQL Transaction vs. multiple statements

I am inserting some data into 2 different tables. I'm currently using 2 different SQL statements but I've read from this site that it's possible to do this in a transaction.

Which method is better, with regards to the size of the data? Currently I'm just inserting 2 seperate rows at the same time into 2 different tables, unordered, so I think 2 statements is better.

EDIT: Sorry I wasn't very clear: the database I'm working on is trivial, I just wanted to know the performance difference of a transaction vs. 2 seperate statements, and the performance difference as you do more statements.

Answer Source

If the data are correlated, which means that the data stored by the second sql depend, are linked in some way with the data which are stored by the first query and/or vice-versa, then you should consider to make a transaction. In such this way, you could rollback if one of the queries encounter a problem. You can implement this in a try-catch statement. If the queries are totally unrelated each-other, you can go with two separate SQL statements.

However I think that, as the data are the same for the two tables, it would be better to do a transaction. So, if you'll have to make operations like joins, deletes, etc. in the future, you have a consistent database yet.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download