StrataFrame Forum

Bulk Saving?

http://forum.strataframe.net/Topic24437.aspx

By Edhy Rijo - 9/1/2009

Hi guys,



I have an import process of CSV files with more than 300 thousand records, I have to read each one and compare against my records and then insert them in another table for future reference with other imports.



My approach is to loop through the imported records collection and then add them to the BO using BO.NewRow(), so far so good, the problem comes when I need to save them using BO.Save(), it takes a long time to actually insert those 300+K records.



I am using the DDT for all my data modeling, and this table is setup to use Store Procedures for all Insert/Update/Delete and also is using an AutoIncrement PK.



In the BO I have turn off the Update/Delete ConcurrencyType and there is no value in the RowVersionOrTimestampColumn.



Is there anything else I may need to do to save those records faster?
By Keith Chisarik - 9/1/2009

SQLBULKCOPY

Somthing like this: (pass in the currentdatatable fo your BO)

Private Sub bulkLoadFromMultipleTables(ByVal dtToInsert As DataTable)

Dim x As DateTime = DateTime.Now

Using destinationConnection As SqlConnection = _

New SqlConnection(MicroFour.StrataFrame.Data.DataLayer.DataSources("sql").ConnectionString)

destinationConnection.Open()

Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationConnection)

bulkCopy.DestinationTableName = _sqlTable.Trim

Try

'bulkCopy.NotifyAfter = 10000

'bulkCopy.SqlRowsCopied += New SqlRowsCopiedEventHandler(s_SqlRowsCopied)

bulkCopy.BatchSize = 1000

'should already match due to the join process and field aliasing done

For Each row_conversionrule As BUSINESS.bo_mapfields In Me.Bo_mapfields.GetEnumerable()

bulkCopy.ColumnMappings.Add(row_conversionrule.mapf_sqlfield, row_conversionrule.mapf_sqlfield)

Next

bulkCopy.WriteToServer(dtToInsert)

bulkCopy.Close()

Dim y As DateTime = DateTime.Now

Dim diff As TimeSpan = y - x

MsgBox("seconds: " & diff.Seconds & ":" & diff.Milliseconds)

Catch ex As Exception

MsgBox(ex.ToString)

Finally

' Close the SqlDataReader. The SqlBulkCopy

' object is automatically closed at the end

' of the Using block.

dtToInsert.Dispose()

End Try

End Using

End Using

End Sub

' of the Using block.

 
By Keith Chisarik - 9/1/2009

if your SQL table matches the structure of your BO, you can skip the mapping line, ohterwise us it to map your datatable fields to your SQL table fields before the copy.

Optional: bulkCopy.ColumnMappings()

By Edhy Rijo - 9/1/2009

Hi Keith,



Thanks a lot for the code sharing. I was just reading an article about SqlBulkCopy here and then saw your post.



Yes, my BO match the SQL data structure. I will try to implement this one.
By Keith Chisarik - 9/1/2009

This saved my butt way back when I was using DB2 (uhggg) , I use it a decent amount with SQL and it has treated me good Smile

Enjoy!

By Edhy Rijo - 9/1/2009

Hi Keith,



I am very close to make this work. I only having some problem with the bulkCopy.ColumnMappings.Add()



For Each row_conversionrule As BUSINESS.bo_mapfields In Me.Bo_mapfields.GetEnumerable()



bulkCopy.ColumnMappings.Add(row_conversionrule.mapf_sqlfield, row_conversionrule.mapf_sqlfield)



Next




This code is not very clear to me, I guess I need to get all the field names from the BO to be mapped in the bulkCopy. Would you please post the For...Each code for a real BO sample?
By Edhy Rijo - 9/1/2009

Never mind, I found it:

For Each fieldName As String In Me.BizTransactionItemsImport1.AllFieldsList()

bulkCopy.ColumnMappings.Add(fieldName, fieldName)

Next





Thanks Keith, it works the first run, I will now do a test with couple of thousand of records.
By Keith Chisarik - 9/1/2009

ahh yes I was pulling from a fields mapping table because my BO structure does not match my SQL tables structure.

Looks like you got it. FYI, if they ,match you dont need to do the column mapping step and it will 'just work' Smile

By Edhy Rijo - 9/1/2009

Keith Chisarik (09/01/2009)
ahh yes I was pulling from a fields mapping table because my BO structure does not match my SQL tables structure.



Looks like you got it. FYI, if they ,match you dont need to do the column mapping step and it will 'just work' Smile




Yes they match, but it did not work without the mapping, so I figured to be in the safe side to just do the Field Name loop and it will not cost anything, I am still gaining a lot of time now, I will do the test in a couple of minutes with 365K records which took almost 10 minutes to save before, will post the results later.
By Keith Chisarik - 9/1/2009

My bet is ... 6 seconds.
By Edhy Rijo - 9/1/2009

Keith Chisarik (09/01/2009)
My bet is ... 6 seconds.




It took 33 seconds and I am very, very, very happy. Tongue



Thanks again.
By Keith Chisarik - 9/1/2009

you can get that better with some tweaking Smile

Congrats, I remember well going from 15 minutes to 15 seconds and how good it felt.

By Trent L. Taylor - 9/2/2009

Bulk copies....can't live without'em! BigGrin
By Edhy Rijo - 9/2/2009

BTW, is there a way to simply bulk update the records instead of inserting them? Another process is to loop through the inserted records a 2nd or 3th time to re-process them, and after that, the modified ones should be save back to the server.
By Edhy Rijo - 9/18/2009

Trent,



Talking about concurrency, in this same process, when I need to made some modifications to the imported data, I am using an SF BO with UpdateConcurrencyType and DeleteConcurrencyType set to OFF, but when I try to save any modifications I will get the SF Concurrency Manager form but without showing any value to be selected.



In this case I don't want or need any type of concurrency checking, what or where else do I need to check to make sure this SF Concurrency Manager form does not show up and my records are simply just saved?
By Trent L. Taylor - 9/20/2009

Yes. There are a number of ways to make sure that there is not any concurrency exception popping up, for example, you can handle the ConcurrencyException event and manage this yourself (which you would just ignore any collisions).



Most likely you are doing this on an SF dialog. If so, you can set the AutoHandleCollisions property to false and it should prevent that dialog from appearing.
By Edhy Rijo - 9/20/2009

Thanks for the info.



I believe the AutoHandleCollisions property will handle All or Nothing action, is there a way to control which BO to exclude from the AutoHandleCollisions action?
By Trent L. Taylor - 9/20/2009

That is the only place that the dialog is fired from. So if you want to handle this on a BO by BO basis, then you will have to handle the ConcurrencyException event.
By ChanKK - 4/17/2010

Hi

Any plan to add this feature to BO by SF team?
By Dustin Taylor - 4/20/2010

Hello Smile,

Which feature are you referring to? The ConcurrencyException options Trent mentioned are already available, and have been for quite a while. The SQLBulkCopy is a function of SQL server, and is very handy.