Insert into Table got really slow after having millions of records

Question

I have a table structure which basically looks like this:

UserItems <= Users => UserTransactions

The UserItems and USerTransactions tables contain UserId FK in both of them which are indexed.

The two of these tables both contain millions and millions of recods ( last time I checked UserTransactions it had around 14 million records).

I'm facing a huge performace issue and slowdown when trying to perform an insert into either of these tables.

My code for insert looks like this:

using (var ctx = new mydbEntities())
{
   ctx.Configuration.AutoDetectChangesEnabled = false;
   ctx.Configuration.ValidateOnSaveEnabled = false;
   _listOfUserTransactions; //  List<UserTransactions>
   ctx.BulkInsert(_listOfUserTransactions);
   ctx.SaveChanges(); 
}

Let's suppose that _listOfUserTransactions contians 100k entities in itself. Before the tables had smaller amounts of records, the insert was performing blazingly fast... Now it's become very very slow and inserting 100k records with BulkInsert now sometimes takes several hours...

I have no idea anymore what to do to improve the insert performance...

Can you guys help me out ??

My first guess is to check what indices you have on these tables and reduce this as much as possible. Second, if the primary key on the table is clustered and something other than an identity column, make it non-clustered as clustering would require table re-organization on insert. — MikeS, Feb 01 '17 at 14:49
@MikeS you mean to add a nonclustered index to UserId FK in UserItems? — User987, Feb 01 '17 at 14:56
@MikeS Can you elaborate your answer a bit more, I'm not exactly sure what you mean? If you could reply to me this as an answer so that I can accept it :) — User987, Feb 01 '17 at 14:57
Add more information about the `UserTransactions` table. Especially interesting is the clustered index, and other indexes. As Mike wrote, if you insert a lot of row that don't end up *at the end* of the clustered index, that might explain the problem. Also add tag for DBMS. — user1429080, Feb 01 '17 at 15:15

score 0 · Answer 1 · answered Feb 01 '17 at 15:34

As mentioned in my comment:

My first guess is to check what indices you have on these tables and reduce this as much as possible. Second, if the primary key on the table is clustered and something other than an identity column, make it non-clustered as clustering would require table re-organization on insert.

If you can add the schemas (including indices) to your question for the user transactions table, that would help a lot in figuring out what is going on. Any index that is clustered will require reorganization of the table data physically on disk, so selecting the clustered index on a table requires being careful that the fields selected are going to be in an order that minimizes this reorganization.

Insert into Table got really slow after having millions of records

1 Answers1