How to quickly insert data to the database?

Topics: Core, General
Feb 14, 2012 at 9:16 AM

Hello!

Now I'm adding 10 000 records to the database in 9 minutes by this code:

var data = _workContextAccessor.GetContext).Resolve<IRepository<SomeTableRecord>>();
var curData = new SomeTableRecord(); 
//setting fields value ... 
data.Create(curData);
How I can do optimization on inserting data?I want to reduce inserting time.Any help appreciated!
Coordinator
Feb 14, 2012 at 4:37 PM

You could generate a SQL script and execute it in bulk from the command line.

If it needs to run from Orchard, then you could use ContentManager.Flush() and ContentManager.Clear() in your loop, every 100 items for instance. But it's only available for 1.x.

Feb 15, 2012 at 9:18 AM

My orchard's version is 1.3 and I need to run from Orchard.

How I can use ContentManager.Flush() and ContentManager.Clear()?

I tried like this:

var repHsCodes = _workContextAccessor.GetContext().Resolve<IRepository<HsCodesRecord>>();

var counter = 0;
foreach(var curData in allData)
{
    counter++;
                var curHsCodes = new HsCodesRecord();
                curHsCodes.Code = curData.Code;
                // Setting other columns value
                ... 
 
   repHsCodes.Create(curHsCodes);

                if (counter == 100) {
                    _orchardServices.ContentManager.Flush();
                    counter = 0;
                }
}
However, no effect.Inserting time about 9 minutes.I'm using correctly the ContentManager.Flush()?
Mar 26, 2012 at 9:15 AM
Edited Mar 26, 2012 at 7:11 PM
akubich wrote:

My orchard's version is 1.3 and I need to run from Orchard.

How I can use ContentManager.Flush() and ContentManager.Clear()?

I tried like this:

var repHsCodes = _workContextAccessor.GetContext().Resolve<IRepository<HsCodesRecord>>();

var counter = 0;
foreach(var curData in allData)
{
    counter++;
                var curHsCodes = new HsCodesRecord();
                curHsCodes.Code = curData.Code;
                // Setting other columns value
                ... 
 
   repHsCodes.Create(curHsCodes);

                if (counter == 100) {
                    _orchardServices.ContentManager.Flush();
                    counter = 0;
                }
}
However, no effect.Inserting time about 9 minutes.I'm using correctly the ContentManager.Flush()?

Err bit late, but where is your Clear ?

Edit: Also, today I improved our migration code and now I can move 60k+ users (including all their data from other tables), and 30k+ other records in around 15minutes with a max memory usage of 180MB. Previous version it took >1hour and took 1GB+ of memory.

Jan 31, 2013 at 3:05 PM
i tried that, the records are inserted successfully but the records are not inserted in order wise, my records around 50000
Jan 31, 2013 at 3:17 PM
Edited Jan 31, 2013 at 3:19 PM
I made a custom import process for some of my tables using SQL bulk copy. I used powershell to export the data into .csv files from SQL, and a custom csv parser + SqlBulkCopy along with the NHibernate ISession to import the data. It's very fast. Over a million rows, and the flat files in .csv format are ~380MB in total. It takes less than 30 seconds to load as one of my migration steps. The one problem with this method is it's not portable because it's using SqlBulkCopy, it ties us to using SQL Server (not sure if it would work with SQL CE, or Sql Azure, but I think it would).

EDIT: As part of the process we don't create indexes and PK's until after the data is inserted. I didn't measure with and without this method, but in my experience this provides a big performance boost to bulk import processes.