Batch Insert or one by one

Question

I am building a system, where the data will be added by user every 30 secs or so. In this case, should I go for a batch insert(insert after every 2 mins) or do I insert every time the user enters the data. The system is to be built on c# 3.5 and Sql server.

Answer 1

It really depends on your requirements, are you able to give more information?

For example:

• Are you running a web app?
• Is the data time sensitive (to be used by other users)
• Do you have to worry about concurrent usage of the data?
• What volume of transactions are you looking at?

For small amounts of data I would simply insert the rows as the user requires, the gain of caching will likely be minimal and it simplifies your implementation. If the usage is expected to be quite high come back and look at optimising the design. Comes back to the general rule of avoiding premature optimisations :)

Answer 2

Once every 30 seconds is no significant stress. By the KISS principle, I prefer one-by-one in this case.

Answer 3

If it is every 30 seconds I would go for insert immediately if the insert is as quick as it should be (<2 seconds for large data).

If you see potential future growth to see more frequent transactions then consider bulking the inserts.

Answer 4

Start with normal inserts. You're nowhere near having to optimize this.

If performance becomes a problem, or it would be obvious that it may be a concern, only then do you need to look at optimizing -- and even then, it may not be an issue with inserts! Use a profiler to determine the bottleneck.