Sql insert query performance

Question

I want to insert n records into a single table. There may be many concurrent users and they may insert/update/select data from this table. What is better way to insert in a such table say 1000 records:

1. Send a single sql query to a database with multiple inserts. This saves server to database calls, but (I not sure) locks the table until the insert is finished and all other queries to this table will wait.
2. Divide 1000 records in some chunks and send them in multiple sql queries. This lets the other queries to be executed on the table, but spend time on server to database calls.

Is this depends on something, or there is a single way that is always the optimal one? Is this depends whether transactions are used or not, while inserting data? Is there other better ways to perform a such insert?

The database I use is MS SQL, but it's interesting how it works in other DB like Oracle.

Answer 1

Set an isolation (concurrency) level that allows reads of the old data during a transaction - e.g. SQL Server 2005+ has READ SNAPSHOT.

Answer 2

This totally depends on what RDBMS you are using.

In Oracle, writes never block reads, that's why you can safely put your data all at once. Note, though, that this will degrade performance, since the concurrent queries will need to fetch the data out of UNDO tablespace that will require extra reads.

In SQL Server writes do block reads on affected rows / pages / tables (depending on lock escalation issues), unless you set TRANSACTION ISOLATION LEVEL to SNAPSHOT.

In all transactional engines that allow concurrent writing and reading, the engine needs to store both old and new data somewhere for it to be available at the same time.

In Oracle, old data is copied into the UNDO tablespace.

In SQL Server, it gets copied into tempdb (only when SNAPSHOT isolation is enabled, otherwise it's just locked).

This always requires some resources (memory or disk), and you can run out of these resources if your UPDATE query affects lots of rows.

Answer 3

I blogged about this a while ago- I think the post answers some of your questions. CK's advise is sound (and so is Quassnoi- he has some good points about Oracle) if you are worried about readers blocking writers.

Answer 4

The general rule is that you should let the database figure out how to do the work. That works better the more you tell it to do in one statement, i.e. you should insert the 1000 records in one go. This will work best for most database servers and most servers do not block reads for writes.

There are exceptions: if the insertion is slow because the server is slow or the table big or complicated it may be better to split the query into groups of small inserts.

An in between method would be to send multiple insert commands for say 10 or 100 rows with commits in between in one big script to the server.

Added: Most database servers do not block reading as in Oracle, IBM DB/2 and MySQl using InnoDB tables. SQL Server on the other hand manages to lock tables even for reads and is not among them.