I am using Self Servicing pattern in my app. I need to insert a large amount of rows, around 100k. Basically my app alows a user to import an XML file with datasets. I am looking for a faster way to insert the records using the same Self Servicing pattern. I've read a couple of posts, including http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3415, however, I can't seem to get my import below 7 mins. Here is what I have

Dim datasets As List(Of DataSet) = ...load my datasets from an XML file

For Each ds As DataSet In datasets

  Using stagingRows As New StagingCollection()
     For Each row As DataRow In ds.Tables(0).Rows
        Dim stagingRow As New StagingEntity
          With stagingRow
           ...add my properties e.g.
           .Prod_Name = row("prod_name")    
           End With
           stagingRows.Add(stagingRow)
     Next
          stagingRows.SaveMulti(False)
  End Using

Next

That's basically what I have going on. I would perfer to keep the SelfServicing and not go to SQLBulkCopy at the moment.

Much as I hate to say it, there are some tasks which LLBLGen is not suited for (not many... ) and bulk inserts is one of them. There is only so much that can be done to optimise inserting 100K rows one at a time, you'd probably find that doing it using a plain DataSet would not be a whole lot faster.

I'm a pragmatist, and I tend to use the correct tool for the job, and as much as I love LLBLGen, SQLBulkCopy, or bcp are the correct tools for doing bulk inserts. (The clue is in the name...!)

If you are determined to carry on with LLBLGen, have you tried batching your inserts, and experimenting with the batch size ?

Matt

What do you mean by "batching your inserts"?...

MTrinder wrote:

Much as I hate to say it, there are some tasks which LLBLGen is not suited for (not many... ) and bulk inserts is one of them. There is only so much that can be done to optimise inserting 100K rows one at a time, you'd probably find that doing it using a plain DataSet would not be a whole lot faster.

I'm a pragmatist, and I tend to use the correct tool for the job, and as much as I love LLBLGen, SQLBulkCopy, or bcp are the correct tools for doing bulk inserts. (The clue is in the name...!)

If you are determined to carry on with LLBLGen, have you tried batching your inserts, and experimenting with the batch size ?

Matt

Adding them to the collection that you are saving in batches of say 100, saving the collection, and then starting again with the next batch of 100.

It can improve things somewhat, but you still have the issue of the volume of data.

Matt