[RESOLVED] VS 2012 - Add Records to SQL Faster

[circuits2]

I have a routine that reads in data from a text file, sorts it, and then uploads anything new to the database. Here is my Insert code:

' SQL COMMAND TO ADD ITEM
        Dim sqlCmd2 As New SqlCommand
        sqlCmd2.CommandText = "INSERT INTO Tickets_tbl VALUES(NEWID(), @num, " _
                            & "(SELECT Loc_id FROM Locations_tbl WHERE AccountNum_nvc = @acct AND Cust_id = " _
                            & "(SELECT Cust_id FROM Customers_tbl WHERE CuNumber_nvc = @cnum)), " _
                            & "@line, @bag, @man, @route, @date, " _
                            & "(SELECT Item_id FROM Inventory_tbl WHERE ItemNum_nvc = @item), " _
                            & "@qty, @gen, 0, @cu1, @cu2, @cu3, @cu4, @cu5, @cu6, @cu7, @cu8, @cu9)"
        sqlCmd2.Parameters.Add("@num", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@acct", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@cnum", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@line", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@bag", SqlDbType.SmallInt)
        sqlCmd2.Parameters.Add("@man", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@route", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@date", SqlDbType.Date)
        sqlCmd2.Parameters.Add("@item", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@qty", SqlDbType.Int)
        sqlCmd2.Parameters.Add("@gen", SqlDbType.Bit)
        sqlCmd2.Parameters.Add("@cu1", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@cu2", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@cu3", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@cu4", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@cu5", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@cu6", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@cu7", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@cu8", SqlDbType.NVarChar)
        sqlCmd2.Parameters.Add("@cu9", SqlDbType.NVarChar)


If aTicksDT.Rows.Count > 0 Then
                For Each ar As DataRow In aTicksDT.Rows()
                    ' ADD TICKET
                    sqlCmd2.Parameters("@num").Value = Trim(ar.Item("TicketNumber").ToString())
                    sqlCmd2.Parameters("@acct").Value = Trim(ar.Item("AccountNumber").ToString())
                    sqlCmd2.Parameters("@cnum").Value = Trim(ar.Item("CustomerNumber").ToString())
                    sqlCmd2.Parameters("@line").Value = Trim(ar.Item("LineNumber").ToString())
                    If Not Integer.TryParse(ar.Item("BagNumber").ToString(), sqlCmd2.Parameters("@bag").Value) Then
                        sqlCmd2.Parameters("@bag").Value = 0
                    End If
                    sqlCmd2.Parameters("@man").Value = Trim(ar.Item("ManualID").ToString())
                    sqlCmd2.Parameters("@route").Value = Trim(ar.Item("Route").ToString())
                    If Not Date.TryParse(ar.Item("DeliveryDate").ToString(), sqlCmd2.Parameters("@date").Value) Then
                        sqlCmd2.Parameters("@date").Value = Date.Today()
                    End If
                    sqlCmd2.Parameters("@item").Value = Trim(ar.Item("ItemNumber").ToString())
                    If Not Integer.TryParse(ar.Item("Qty").ToString(), sqlCmd2.Parameters("@qty").Value) Then
                        sqlCmd2.Parameters("@qty").Value = 0
                    End If
                    sqlCmd2.Parameters("@gen").Value = ar.Item("Generic")
                    sqlCmd2.Parameters("@cu1").Value = Trim(ar.Item("CustomDecimal1").ToString())
                    sqlCmd2.Parameters("@cu2").Value = Trim(ar.Item("CustomDecimal2").ToString())
                    sqlCmd2.Parameters("@cu3").Value = Trim(ar.Item("CustomDecimal3").ToString())
                    sqlCmd2.Parameters("@cu4").Value = Trim(ar.Item("CustomInteger1").ToString())
                    sqlCmd2.Parameters("@cu5").Value = Trim(ar.Item("CustomInteger2").ToString())
                    sqlCmd2.Parameters("@cu6").Value = Trim(ar.Item("CustomInteger3").ToString())
                    sqlCmd2.Parameters("@cu7").Value = Trim(ar.Item("CustomText1").ToString())
                    sqlCmd2.Parameters("@cu8").Value = Trim(ar.Item("CustomText2").ToString())
                    sqlCmd2.Parameters("@cu9").Value = Trim(ar.Item("CustomText3").ToString())

                    SetData.NonQuery(sqlCmd2)
                Next
            End If

The SQL Command is then sent to this procedure to actually interface with the database:

Public Function NonQuery(ByVal sqlCmd As SqlCommand) As Boolean

        Dim sqlConn As SqlConnection = New SqlConnection(GlobalProps.HostConString.ToString())
        sqlCmd.Connection = sqlConn

        Try

            ' OPEN THE CONNECTION
            sqlConn.Open()

            ' EXECUTE THE COMMAND
            sqlCmd.ExecuteNonQuery()

            ' CLOSE THE CONNECTION
            sqlConn.Close()

        Catch ex As Exception
            Debug.WriteLine(ex.Message.ToString(), "SetData.NonQuery Error")
            Return False
        Finally
            If (sqlConn.State = ConnectionState.Open) Then
                sqlConn.Close()
            End If
            sqlCmd.Dispose()
            sqlConn.Dispose()
        End Try

        Return True

    End Function

With a text file that reads in 15000 lines, this whole procedure takes 8-10 minutes. Seems like adding 15000 items to SQL shouldn't take that long.

Any ideas on how I can speed this up?

[Shaggy Hiker]

What's costing you the time? You appear to assume that it is the insert itself, but it may not be. You are doing a fair amount of string manipulation on that input. That may be entirely necessary, but string operations are not all that fast, so it may be entirely the preparation that is costing you, rather than the inserts.

You already have the data in a datatable, so why not use a Dataadapter.Update call to perform all the INSERT in one call. Technically, that doesn't really do anything different than what you are doing, but it would separate the INSERT call from all the manipulation you are currently doing prior to each individual INSERT, which would allow you to figure out where the slowdown is really happening. You'd have to supply an INSERT command for the DA, but you already have that, so it wouldn't exactly be difficult. You may also be able to remove ALL of the .ToString and Trimming, which is quite possibly the real problem.

If you really don't trust that the dates are dates or that those integers are integers (the ones you test with TryParse), then you will almost certainly have to clean the data in the datatable prior to calling .Update, but that still allows you to separate the data prep from the data upload.

[circuits2]

I removed all instances of .ToString() and ran a test import. It clocked at 7:04, so I saved a little over a minute. I'm leary of removing the TRIM operations because I don't want to introduce blank spaces into the database unless they are necessary.

[szlamany]

You could pinpoint a bit by doing things like commenting out the actual sql - so you can see how the loop/string logic times out.

Then you could change it so that it's putting the "same data" 15000 times - so you can more or less see the SQL time by itself.

If you need true speed use BULK INSERT (that would be a local file to the server) - there is some way to accomplish this via the sql client object so the remote app can do it without disk access to the server- I've seen posts here about it.

But also - your SQL process could be better. SQL is meant to be SET based - meaning you kind of do things in stages. How about creating a staging table to load the more "raw" data into - without any trim or cleanup or date check or conversion.

Once you get this staging table in the DB - the rest is done with single UPDATE's to get rid of SPACES (on the columns in SQL already) and then a final INSERT INTO ... SELECT * FROM... so you can fill you production table.

You would do those SUB-QUERIES in that final INSERT/SELECT FROM.

Basically more of an assembly line process here - does that make sense? Build your SET OF DATA - ADJUST THAT SET - WORK IT UP - FINAL STEP MANIPULATE and MOVE INTO PRODUCTION.

[circuits2]

That makes perfect sense. I haven't done a BULK INSERT before. I'm wondering if I should do all of my formatting in the table that I already have in memory before performing my updates. That should be just as fast as creating the staging table in SQL that you talked about. I think I will give that a try.

[szlamany]

Careful - datatables in memory are still SINGLE INSERT LOADED into the DB.

15000 rows is past the amount that you would want to use something as "low end" as that.

You can already see that doing it piecemeal as you are can take something that you want to run in seconds and make it take minutes.

So you start with an ACTUAL BULK INSERT - testing the speed of direct .TXT file to DB imports. So that's is your benchmark. In moment you can find a base timing that all future attempts are scored against.

You might end up with some kind of INSERT-from-client-one-row-at-a-time - but that has to be wicked fast. Even loading a SINGLE COLUMN staging table - with the whole row of "TEXT" - is better then the INSERT's you are doing now with 15000 rows in the consideration

Remember that 15000 rows is a lot to insert in a DB in a single moment regardless.

[Shawn N]

I'd create a Debug log showing displaying time since the last command executed & hone in on what's taking the most time.

[Shaggy Hiker]

I think the first thing to do is comment out the ExecuteNonQuery line and see how the timing goes. That completely removes the actual DB work (other than the continual opening and closing of the connection that you are doing, though that may not add much anyways). The next thing to time would be commenting out the whole call to the method that does the update. That would remove the INSERT and all that opening and closing of the connection. If, once you have tested those things, you find that the code is suddenly very fast, then you know whether a BULK anything will really help. You will have determined the cost of opening and closing the connection that many times, and you'll have determined the cost of the actual INSERT. All remaining time is the preprocessing, and you've already seen that the string stuff was costly.

When you call something like Trim, you are allocating a new string of the new size, copying over what needs to be copied, then throwing out the old string. There's no getting around that if you have to Trim, but you can see how it will add up pretty badly, so you may well find that even if you take the DB out of the calculation entirely the code will still be slow.

[szlamany]

If you are opening and closing the connection for each loop here - 15000 times - then you have key problems in this logic now.

[Shaggy Hiker]

It's getting pooled, so it isn't as bad as utterly destroying the connection, but it may well be a significant problem.

[szlamany]

It is an expensive method regardless...

[circuits2]

Thanks guys! Here is what I found:

I removed all of the TRIM and ToString() calls and replaced them all with a single TRIM call when the field is initially read in from the text file. I know it is still performed the same number of times but this didn't seem to slow down my initial import by a noticeable amount.

Next, I commented out the SetData.NonQuery(sqlCmd) line that actually sends the command and parameters up to the database liason. This made a HUGE impact! The entire process ran in less than 20 seconds.

I now know that I needed to combine all of my TRIM statements AND I need to use BULK INSERT.

The file I am reading in will always be dynamic. The end user can select from a number of fields to import by giving the field name, starting index, and length.

Since the file is dynamic, I created an "Assembly" table in my SQL Database that contains one column for each possible field. I then followed the example from this post to do a BULK INSERT:

http://www.dotnetcurry.com/ShowArticle.aspx?ID=323

I then used SQL Statements to modify the data in the Assembly table and upload any new records.

Now the whole process takes less than a minute!

Thanks again!