Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Very fast import of thousands of rows using LLBLGen
 

Pages: 1
LLBLGen Pro Runtime Framework
Very fast import of thousands of rows using LLBLGen
Page:1/1 

  Print all messages in this thread  
Poster Message
JSobell
User



Location:

Joined on:
07-Jan-2006 06:44:46
Posted:
89 posts
# Posted on: 30-Oct-2015 13:03:43.  
I've seen a few questions in here over the years re fast importing of data items, so I thought I'd post our solution. It's SQL Server only, although I'm sure similar features are available on some other platforms that might be adaptable.

We wrote a test to import 10,000 records into a holding table, and using entities it too almost exactly 100 seconds, a rate of 10ms per entity.
Experimenting with transactions and different 'batch' sizes made little difference, so I looked at alternatives.
Looking at the standard System.Data.SQLClient shows an SqlBulkCopy object, and one option takes a DataTable as input. TypedViews inherit from DataTable, so they can be passed in directly, but we need to modify and inject several fields during the data preparation stage, plus we wanted the data from a file, not a database, so we simply use the LLBLGen TypedView as a strongly typed container that matches our destination table.

In summary, the test code is shown below.
This code imports 10,000 items in 3.5 seconds on our test machine. That's a 96% improvement in performance!!
I'm simply sharing this hoping others find this method helpful, and it's a great way of enforcing type safe values during creation of the objects, as the TypedView enforces valid datatype values when creating the object.

The only downer in all of this is that the TypedView's Row object does not allow Nullables, so you have to either check the value for null and not assign a value if it is, of you can modify the templates to implement nullable properly in the underlying row.
For example, a TypedView Row property would need to look something like this:
Code:

public System.DateTime? DateOfBirth
{
get { return IsDateOfBirthNull() ? (System.DateTime)TypeDefaultValue.GetDefaultValue(typeof(System.DateTime)) : (System.DateTime)this[_parent.DateOfBirthColumn]; }
set { this[_parent.DateOfBirthColumn] = value.HasValue ? (object)value.Value : DBNull.Value; }
}


Anyway, here's the super-fast code snippet:

Code:

            using (var db = new DataAccessAdapter())
            {
                var holdingTable = new PanelMemberHoldingTypedView();

                for (int i = 0; i < 10000; i++)
                {
                    var newrow = (PanelMemberHoldingRow) holdingTable.NewRow();

                    newrow.PanelMemberHoldingId = -1;
                    newrow.Command = "";
                    newrow.PanelMemberId = 0;
                    newrow.PanelId = Guid.Empty;
                    newrow.InvitationHash = "";
:
: (another 30 fields assigned values here)
:
                    holdingTable.Rows.Add(newrow);
                }


                Stopwatch sw = new Stopwatch();
                sw.Start();

                using (SqlBulkCopy bulkCopy =
                         new SqlBulkCopy(db.ConnectionString))
                {
                    bulkCopy.DestinationTableName = "dbo.PanelMember_Holding";

                    try
                    {
                        bulkCopy.WriteToServer(holdingTable);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                }

                sw.Stop();

                Console.WriteLine($"Process ran in {sw.ElapsedMilliseconds}ms");
                Console.WriteLine("Press Enter to exit.");
                Console.ReadLine();
            }
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37870 posts
# Posted on: 30-Oct-2015 14:58:13.  
Awesome! Thanks for sharing!

About the nullables, are the fields in the typedview marked as optional (in the designer) ?


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
JSobell
User



Location:

Joined on:
07-Jan-2006 06:44:46
Posted:
89 posts
# Posted on: 30-Oct-2015 22:58:21.  
Otis wrote:

About the nullables, are the fields in the typedview marked as optional (in the designer) ?


Yes, they are, although I hadn't noticed that option before you mentioned it.

Cheers,
Jason
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.