Slow Bulk Insert for Table with Many Indexes

Slow bulk insert for table with many indexes

You can disable and enable the indexes. Note that disabling them can have unwanted side-effects (such as having duplicate primary keys or unique indices etc.) which will only be found when re-enabling the indexes.

--Disable Index
ALTER INDEX [IXYourIndex] ON YourTable DISABLE
GO

--Enable Index
ALTER INDEX [IXYourIndex] ON YourTable REBUILD
GO

bulk insert with or without index

As with Joel I will echo the statement that yes it can be true. I've found that the key to identifying the scenario that he mentioned is all in the distribution of data, and the size of the index(es) that you have on the specific table.

In an application that I used to support that did a regular bulk import of 1.8 million rows, with 4 indexes on the table, 1 with 11 columns, and a total of 90 columns in the table. The import with indexes took over 20 hours to complete. Dropping the indexes, inserting, and re-creating the indexes only took 1 hour and 25 minutes.

So it can be a big help, but a lot of it comes down to your data, the indexes, and the distribution of data values.

PostgreSQL: performance of large inserts without index, temp table

With INSERT performance should not depend on the size of the batch.

The important thing is that you don't run each INSERT in its own transaction.

Additionally, it hurts if each tiny little INSERT has to be parsed separately, so use a prepared statement.

If you do that, it should be faster to insert the data directly rather than using an intermediary table.

SQL Server INSERT into huge table is slow

OK, here's what I would do:

  1. Check to see if you need both indexes [IX_ACCOUNT_ID_POINTS_CODE] and [IX_ACCOUNT_ID] as they may be redundant.

  2. Before you do the INSERT, Disable the Trigger and drop the Foreign Keys.

  3. Do the INSERT setting the fields normally set by the Trigger, and insuring that the FK Column's values are valid.

  4. Re-Enable the trigger, and re-create the Foreign Keys WITH NOCHECK.

I would leave the indexes on as you are inserting less than 0.2% of the total row count so it's probably faster to update them in-place rather than to drop and rebuild them.

How to improve performance of Bulk Inserts in MYSQL

By adding following lines in my.ini. I am able to achieve it.

innodb_autoinc_lock_mode =2
sync_binlog=1
bulk_insert_buffer_size=512M
key_buffer_size=512M
read_buffer = 50M

and innodb_flush_log_at_trx_commit=2, i have seen in another link where it said that it increase speed to 160x.
Output performance :more than 24hr to 2 hrs

MYSQL bulk INSERT slow

If this is MyISAM table than you can try locking it before insert.
Also, if you have keys on this table, disabling them can be helpful too:

ALTER TABLE polim_inventory_transactions DISABLE KEYS;

In general, you can see more tips here: Bulk Data Loading for MyISAM Tables

MySQL InnoDB: Best practice bulk insert

This manual page also suggests changing the innodb_autoinc_lock_mode.

If you do not need the feature, disable binary logging.

Increasing the size of some InnoDB buffers could help too (the innodb_buffer_pool_size in particular).

I believe using a transaction is not desirable in this case. If a small number of consecutive changes need to be applied within the same transaction, these changes may be optimized by being consolidated in one single write. In your case, I believe you will only load your redo log uselessly.

This leads me to another suggestion: try inserting a smaller number of rows at a time, like this:

INSERT INTO destination
SELECT * FROM source LIMIT 0, 10000;

INSERT INTO destination
SELECT * FROM source LIMIT 10000, 10000; -- and so on

Finally, if you have a lot of memory available, you may want to manually load the whole data into a temporary memory table, then insert into your destination from this memory table (possibly in small batches):

CREATE TEMPORARY TABLE destination_tmp LIKE source;
ALTER destination_tmp ENGIN=MEMORY;
INSERT INTO destination_tmp SELECT * FROM source;
INSERT INTO destination SELECT * FROM destination_tmp;

Make sure to have a large enough value for max_heap_table_size.



Related Topics



Leave a reply



Submit