Why Bulk Import Is Faster Than Bunch of Inserts

Why Bulk Import is faster than bunch of INSERTs?

BULK INSERT can be a minimally logged operation (depending on various
parameters like indexes, constraints on the tables, recovery model of
the database etc). Minimally logged operations only log allocations
and deallocations. In case of BULK INSERT, only extent allocations are
logged instead of the actual data being inserted. This will provide
much better performance than INSERT.

Compare Bulk Insert vs Insert

The actual advantage, is to reduce the amount of data being logged in the transaction log.

In case of BULK LOGGED or SIMPLE recovery model the advantage is significant.

Optimizing BULK Import Performance

You should also consider reading this answer : Insert into table select * from table vs bulk insert

By the way, there are factors that will influence the BULK INSERT performance :

Whether the table has constraints or triggers, or both.

The recovery model used by the database.

Whether the table into which data is copied is empty.

Whether the table has indexes.

Whether TABLOCK is being specified.

Whether the data is being copied from a single client or copied in
parallel from multiple clients.

Whether the data is to be copied between two computers on which SQL
Server is running.

Why would you use bulk insert over insert?

Where did you look for the answer?

https://www.google.com.au/#q=sql+bulk+insert+vs+insert

First result has timings to back up the theory, other links are equally informative particularly some from MSDN.

There is also a bunch of helpful results from SO.

In short, Bulk insert is faster. You can use bulk insert to insert millions of rows from a csv or xml or other files in a very short time however if you only have 3 or 4 rows to insert it's quick enough to just throw it in using insert statements.

Which is faster: multiple single INSERTs or one multiple-row INSERT?

https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

• Connecting: (3)
• Sending query to server: (2)
• Parsing query: (2)
• Inserting row: (1 × size of row)
• Inserting indexes: (1 × number of indexes)
• Closing: (1)

From this it should be obvious, that sending one large statement will save you an overhead of 7 per insert statement, which in further reading the text also says:

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.

SQL Server import faster than bulk import

If you are inserting to an existing table, drop all indexes prior to import and re-create them after the import.

If you are using SSIS, you can tweak the batch and commit sizes.

Verify there is adequate memory on the server for such a large data load.

Perform the loading operation on the local server (copy file locally, don't load over the network).

Configure your destination database and transaction log auto-growth options to a reasonable value, such as a few hundred MB chunks at a time (default is typically growth by 1MB for the master data file .mdf). Growth operations are slow/expensive so you want to minimize these.

Make sure your data and log files are on fast disks, preferably on separate LUNs. Ideally you want your log file on a mirrored separate LUN from your log file (you may need to talk to your storage admin or hosting provider for options).

How does COPY work and why is it so much faster than INSERT?

There are a number of factors at work here:

• Network latency and round-trip delays
• Per-statement overheads in PostgreSQL
• Context switches and scheduler delays
• COMMIT costs, if for people doing one commit per insert (you aren't)
• COPY-specific optimisations for bulk loading

Network latency

If the server is remote, you might be "paying" a per-statement fixed time "price" of, say, 50ms (1/20th of a second). Or much more for some cloud hosted DBs. Since the next insert cannot begin until the last one completes successfully, this means your maximum rate of inserts is 1000/round-trip-latency-in-ms rows per second. At a latency of 50ms ("ping time"), that's 20 rows/second. Even on a local server, this delay is nonzero. Wheras COPY just fills the TCP send and receive windows, and streams rows as fast as the DB can write them and the network can transfer them. It isn't affected by latency much, and might be inserting thousands of rows per second on the same network link.

Per-statement costs in PostgreSQL

There are also costs to parsing, planning and executing a statement in PostgreSQL. It must take locks, open relation files, look up indexes, etc. COPY tries to do all of this once, at the start, then just focus on loading rows as fast as possible.

Task/context switching costs

There are further time costs paid due to the operating system having to switch between postgres waiting for a row while your app prepares and sends it, and then your app waiting for postgres's response while postgres processes the row. Every time you switch from one to the other, you waste a little time. More time is potentially wasted suspending and resuming various low level kernel state when processes enter and leave wait states.

Missing out on COPY optimisations

On top of all that, COPY has some optimisations it can use for some kinds of loads. If there's no generated key and any default values are constants for example, it can pre-calculate them and bypass the executor completely, fast-loading data into the table at a lower level that skips part of PostgreSQL's normal work entirely. If you CREATE TABLE or TRUNCATE in the same transaction you COPY, it can do even more tricks for making the load faster by bypassing the normal transaction book-keeping needed in a multi-client database.

Despite this, PostgreSQL's COPY could still do a lot more to speed things up, things that it doesn't yet know how to do. It could automatically skip index updates then rebuild indexes if you're changing more than a certain proportion of the table. It could do index updates in batches. Lots more.

Commit costs

One final thing to consider is commit costs. It's probably not a problem for you because psycopg2 defaults to opening a transaction and not committing until you tell it to. Unless you told it to use autocommit. But for many DB drivers autocommit is the default. In such cases you'd be doing one commit for every INSERT. That means one disk flush, where the server makes sure it writes out all data in memory onto disk and tells the disks to write their own caches out to persistent storage. This can take a long time, and varies a lot based on the hardware. My SSD-based NVMe BTRFS laptop can do only 200 fsyncs/second, vs 300,000 non-synced writes/second. So it'll only load 200 rows/second! Some servers can only do 50 fsyncs/second. Some can do 20,000. So if you have to commit regularly, try to load and commit in batches, do multi-row inserts, etc. Because COPY only does one commit at the end, commit costs are negligible. But this also means COPY can't recover from errors partway through the data; it undoes the whole bulk load.

Which is faster to load CSV file or multiple INSERT commands in MySQL?

LOAD DATA INFILE is the fastest.

hello, so you are dumping table by your own program. if loading speed is important. please consider belows:

• ensure that multiple INSERT INTO .. VALUES (...), (...)
• Disable INDEX before loading, enable after loading. This is faster.

• LOAD DATA INFILE is super faster than multiple INSERT but, has trade-off. maintance and handling escaping.

• BTW, I thing mysqldump is better than others.

how long takes to load 45,000 rows?

Can I get BULK INSERT-like speeds when inserting from Java into SQL Server?

While BULK INSERT is the fastest way of doing bulk insert, SQL Server supports remote (client-driven) bulk insert operations both through the native driver and ODBC. From version 4.2 onwards of the JDBC driver, this functionality is exposed through the SQLServerBulkCopy class, which does not directly read from files but does support reading from a RowSet, ResultSet or a custom implementation of ISQLServerBulkRecord for generated data. This functionality is equivalent to the .NET SqlBulkCopy class, with largely the same interface, and should be the fastest way of performing bulk operations short of a server-based BULK INSERT.

EDIT: Example by OP

Below you can find an example use-case that could be used to test the performance of SQLServerBulkCSVFileRecord, a method similar to SQLServerBulkCopy except that it reads from a text file. In my test case, test.txt contained a million rows with "X tab
100"

CREATE TABLE TestTable (Col1 varchar(50), Col2 int);

The table should not have any indexes enabled.

In JAVA

// Make sure to use version 4.2, as SQLServerBulkCSVFileRecord is not included in version 4.1
import com.microsoft.sqlserver.jdbc.*;

long startTime = System.currentTimeMillis();
SQLServerBulkCSVFileRecord fileRecord = null;  

fileRecord = new SQLServerBulkCSVFileRecord("C:\\temp\\test.txt", true);   
fileRecord.addColumnMetadata(1, null, java.sql.Types.NVARCHAR, 50, 0);  
fileRecord.addColumnMetadata(2, null, java.sql.Types.INTEGER, 0, 0);  
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
Connection destinationConnection = DriverManager.getConnection("jdbc:sqlserver://Server\\\\Instance:1433", "user", "pass");
SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();  

// Depending on the size of the data being uploaded, and the amount of RAM, an optimum can be found here. Play around with this to improve performance.
copyOptions.setBatchSize(300000); 

// This is crucial to get good performance
copyOptions.setTableLock(true);  

SQLServerBulkCopy bulkCopy =  new SQLServerBulkCopy(destinationConnection);
bulkCopy.setBulkCopyOptions(copyOptions);  
bulkCopy.setDestinationTableName("TestTable");
bulkCopy.writeToServer(fileRecord);

long endTime   = System.currentTimeMillis();
long totalTime = endTime - startTime;
System.out.println(totalTime + "ms");

Using this example, I was able to get insert speeds of up to 30000 rows per second.

Faster data upload without BULK INSERT or OPENROWSET

Absolutely. You're adding each row separately. Provide multiple rows in one insert to increase speed substantially. I'm sure there's a limit on how big the insert could be... Around 100 or 1000 rows at once should be a decent starting point of they aren't unusually large rows.

To do it you'll want to accumulate the values list in an array, then once the array is at the size limit flush out the data with an insert.

Make sure to do the flush again after all errors are read, as the csv probably doesn't have a number of rows equally divisible by the size you choose.

Mysql fastest technique for insert, replace, on duplicate of mass records

#1 (single-row inserts) -- Slow. A variant is INSERT IGNORE -- beware: it burns AUTO_INCREMENT ids.

#2 (batch insert) -- Faster than #1 by a factor of 10. But do the inserts in batches of no more than 1000. (After that, you are into "diminishing returns" and may conflict with other activities.

#3 REPLACE -- Bad. It is essentially a DELETE plus an INSERT. Once IODKU was added to MySQL, I don't think there is any use for REPLACE. All the old AUTO_INCREMENT ids will be tossed and new ones created.

#4 IODKU (Upsert) -- [If you need to test before Insert.] It can be batched, but not the way you presented it. (There is no need to repeat the b and c values.)

INSERT INTO (
INSERT INTO TABLE (a, b, c)
     VALUES(1,2,3),(4,5,6),(7,8,9),....(30001,30002,30003)
     ON DUPLICATE KEY UPDATE
         b = VALUES(b),
         c = VALUES(c);

Or, in MySQL 8.0, the last 2 lines are:

         b = NEW.b,
         c = NEW.c;

IODKU also burns ids.

MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE discusses a 2-step process of LOAD + IODKU. Depending on how complex the "updates" are, 2+ steps may be your best answer.

#5 LOAD DATA -- as Bill mentions, this is a good way if the data comes from a file. (I am dubious about its speed if you also have to write the data to a file first.) Be aware of the usefulness of @variables to make minor tweaks as you do the load. (Eg, STR_TO_DATE(..) to fix a DATE format.)

#6 INSERT ... SELECT ...; -- If the data is already in some other table(s), you may as well combine the Insert and Select. This works for IODKU, too.

As a side note, if you need to get AUTO_INCREMENT ids of each batched row, I recommend some variant on the following. It is aimed at batch-normalization of id-name pairs that might already exist in the mapping table. Normalization

---

Related Topics