It depends. It depends on your requirements. Only the requirements.

So ask yourself a few questions:

• How large will the typical CSV file be?
• Under realistic assumptions, how large could the largest CSV file be?
• Will you always process files, or do you also need to read from a stream?
• What are the memory constraints of the machine your tool runs on?
• What are the processing constraints of the machine your tool runs on?
• And so on.

Most importantly: what do you need now?

In this context, “now” could mean this week, this month, or this year. It depends on the environment your program runs in.

There are various factors which can affect whether one or the other is more efficient, and it's difficult to say without taking measurements.

Ways writing all the records at once can be more efficient:

• The CPU does not have to switch between executing code to read CSVs and executing code to write SQLite, potentially making better use of both instruction cache and data cache.
• SQLite may be more efficient at writing multiple records in a single transaction. (I don't know if this is true).

Ways writing the records one at a time can be more efficient:

• Whenever an item is added to a vector exceeding its capacity, the vector has to grow — copying the data into a new memory allocation. If you write the records one at a time, you don't need to do this copying.
• The process takes less memory overall, so the operating system does not have to displace other potential uses of the memory (e.g. disk cache or other processes’ memory).

A frequently useful compromise between these modes of operation is to pick a number of records to be your buffer size, and read only up to that many records before switching to writing. However, before considering complicating things this way, you should measure the performance of both of the basic strategies, on large files and small files.

1 Like

It is true. Depending on whether Write-Ahead Logging is enabled, the difference can be enormous.