CODEWITHSUNDEEP
javasqljdbctransactionsrdbms
pavel_kazlou
pavel_kazlou

Reputation: 2046

RDBMS: what is the best way to perform several operations

I use PostgreSQL 8.4, but i think my question can be expanded to most of the RDBMS.

I need to perform data-changing operation like update or delete for those rows, where the specified column has value from the specified set. For example I want to delete those rows, where id is in (1,4,7,8).

The whole operation should either succeed or fail, so I have two options here:

DELETE FROM my_table WHERE id IN (1,4,7,8)

DELETE FROM my_table WHERE id = 1;

DELETE FROM my_table WHERE id = 4;

...

Is there any difference between these two approaches when executed as plain SQL commands? Which one is better?

The same questions when using JDBC prepared statements for these operations?

Upvotes: 1

Views: 138

Answers (2)

pavel_kazlou
pavel_kazlou

Reputation: 2046

So I checked EXPLAIN ANALYZE and PostgreSQL uses special filter for IN (...) request when traversing the table. So the major difference should be in performance: with IN (...) you traverse the table one time. With N separate requests = ? you traverse table N times. Though PostgreSQL should optimize this so it's actually faster, but still must be slower that IN (...)

Upvotes: 0

Nathan Hughes
Nathan Hughes

Reputation: 96424

You should always use transactions, in my opinion. With the several-operation approach you will have many more round trips across the network. There may also be a difference in what indexes get used, so check out the query plan.

Upvotes: 2

Related Questions