OLEDB Command Builder. Speed issue

[BodwadUK]

Hello peeps. I have recently been learning to program in .net and seem to be having speed issues when writing to databases. The code below takes ages to go through 59792 records and write them.
Now it all seems to slow down on the update method on the dataadaptor object. Is there anything I can do to speed it up? It seems to get slower and slower as it chugs through all the records to write so I dont know if its trying to update all the details at the same time. I only need it to write one at a time though.

          
        Dim Adaptor As New OleDbDataAdapter
        Dim Command As New OleDbCommand
        Dim Records As New DataSet
        Dim Builder As OleDbCommandBuilder
        Dim I As Integer
        Dim Row As DataRow
        Dim Update As Integer

        'Prepare command object
        Command.CommandText = "SELECT * FROM [Music]"
        Command.Connection = DB

        'Setup the adaptor
        Adaptor.SelectCommand = Command

        Builder = New OleDbCommandBuilder(Adaptor)
        Builder.QuotePrefix = "["
        Builder.QuoteSuffix = "]"

        Adaptor.Fill(Records, "Music")

        PB.Value = 0
        PB.Maximum = 100

        For I = 1 To UBound(Music)
            Row = Records.Tables("Music").NewRow()

            With Music(I)
                Row("TrackId") = .ID
                Row("Filename") = .Filename
                Row("Bitmap") = .Bitmap
                Row("Artist") = .Artist
                Row("Title") = .Title
                Row("Year") = .Year
               'I stripped a load out to protect my data from the forum. There are about another '10 entries missing here
            End With


            Records.Tables("Music").Rows.Add(Row) 

            'Update
            Adaptor.Update(Records, "Music")'It slows down on every call here

            If I > Update Then 'This is an attempt to speed it up by reducing the doevents calls
                'Update The progress bar
                PB.Value = (100 / UBound(Music)) * I
                Application.DoEvents()
                Update = Update + 100
            End If

        Next
        
       
        Adaptor = Nothing
        Command = Nothing
        Records = Nothing
        Builder = Nothing

    End Sub

Any advice would be appreciated

[kleinma]

The update command does batch updates, so you should be able to move it outside your loop and only call it once and it should update for all affected records in the dataset that need updating.

Documentation States:

Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the DataSet with the specified DataTable name

[BodwadUK]

ok thanks will see if its any faster. Problem with batch stuff is you cant do a progress bar as it locks up the program for the duration of the call

Thanks

[kleinma]

use a background worker and pawn all the database stuff off on that. Then you can keep the GUI responsive while doing this DB operation.

You could still use a progressbar while in your loop (by reporting progress to the UI thread), and then perhaps change the processbar style to marquee while its doing the actual update to the database since you won't know the exact time it will take for the batch update.

[jmcilhinney]

Another reason it was so slow is that each time you call Update it will implicitly open the connection, save the data and then close the connection.

Basically, if you want to save your data row by row you shouldn't be using a DataAdapter at all. You should be using just a Command. You create the command and add the parameters, then each row you set the parameter values and execute. You would open the connection explicitly at the start, then close it explicitly at the end.

That will still be slower than using a DataAdapter once to save the contents of a DataTable but the difference may not be too great. It will certainly be a lot faster than what you're doing now.

[BodwadUK]

ok thanks

I am now looking at using the command object and the "INSERT" command in sql. When I tried to move the .update to the end of my loop in the example given it just locked up the program indefinetely . It obviously doesnt like too many records to write lol.

Thanks again

[jmcilhinney]

Inserting 60000 records will take some time but I couldn't really say how much. Maybe you should try it with fewer record to begin with to make sure your code's working. You will then also have an idea of how long the whole lot should take, so you can increase the CommandTimeout from the default 30 seconds if need be.

[Jenner]

Though you may not have much of a choice in the matter, I see you're using OLEDB to manipulate your database. ODBC and OLEDB are kinda slow when run under .NET compared to a native ADO.NET assembly which, when working with a LOT of records at once like that can be much faster. What database system are you using?

You could also, using the INSERT command of SQL, do something like this:

INSERT INTO Music VALUES (track1, filename1, bitmap1, artist1, title1, year1), (track2, filename2, bitmap2, artist2, title2, year2),(track3, .....

So, writing something like:

'Single command insert

        Dim i As Integer = 0
        Dim strHolding(Music.GetUpperBound(0)) As String

        For Each msRow As MusicStructure In Music
            With msRow
               strHolding(i) = String.Format("('{0}','{1}','{2}','{3}','{4}','{5}')", _
               .Track, .FileName, .Bitmap, .Artist, .Title, .Year)
            End With
            i += 1
        Next
        Dim strSQL As String = String.Format("INSERT INTO Music VALUES {0}; ", Join(strHolding, ","))
        Dim cmd As New OleDb.OleDbCommand(strSQL, DB)
        cmd.ExecuteNonQuery()

        cmd.Dispose()

May be a little faster. Better yet, thread it. Do this:

'Multithreaded insert

    Private Sub StartInsert()
        Dim t As New Threading.Thread(AddressOf InsertJunk)
        t.IsBackground = True
        t.Name = "DB Insert Thread"
        t.Start()
    End Sub

    Private Sub InsertJunk()
        Dim i As Integer = 0
        Dim strHolding(Music.GetUpperBound(0)) As String

        SyncLock Music
            For Each msRow As MusicStructure In Music
                With msRow
                   strHolding(i) = String.Format("('{0}','{1}','{2}','{3}','{4}','{5}')", _
                   .Track, .FileName, .Bitmap, .Artist, .Title, .Year)
                End With

                i += 1
            Next
        End SyncLock

        Dim strSQL As String = String.Format("INSERT INTO Music VALUES {0}; ", Join(strHolding, ","))
        Dim cmd As New OleDb.OleDbCommand(strSQL, DB)
        cmd.ExecuteNonQuery()

        cmd.Dispose()
    End Sub

[kleinma]

Though you may not have much of a choice in the matter, I see you're using OLEDB to manipulate your database. ODBC and OLEDB are kinda slow when run under .NET compared to a native ADO.NET assembly which, when working with a LOT of records at once like that can be much faster.

Why do people keep saying OLEDB is not part of ADO.NET???

This is right from Microsoft's ADO.NET main page

ADO.NET provides consistent access to data sources such as Microsoft SQL Server and XML, as well as to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update the data that they contain.

The classes are in the System.Data.OleDb namespace, they are managed .NET framework classes, and they are most certainly part of ADO.NET

Now if you are saying something like "If you are using SQL Server it is better to use the System.Data.SQLClient classes", then fine, that makes sense.

[Jenner]

Why do people keep saying OLEDB is not part of ADO.NET???

He has a speed issue. OLEDB is a generic connector method. It's inefficient. A natively written connector for his specific database type would most likely speed things up.

In my trials between using OLEDB and a native Pervasive .NET connector, the native connector ran about FIVE TIMES faster than OLEDB. I was manipulating well over a million records where I had to read each table row and using data from that row, update other fields in that row same row.

I think my keyword in the previous comment was "Native" ADO.NET assembly.

[kleinma]

There are only 4 main providers, OLE, ODBC, SQL, and Oracle that ship with the framework. So unless its a SQL Server or Oracle database, what is he supposed to use?

[Jenner]

Well, there are many free native providers....

MySQL has one here.

Pervasive has one here.

Here's one for SQLite if that's what he's using.

I'm willing to bet they're all faster than OLEDB since they bypass all the layers OLEDB has to go through and talk to the database system more natively.

I also believe I said "Though you may not have much of a choice in the matter" since there are some database systems out there (*cough* MS Access) where a native provider isn't available or only available commercially, and he has to use OLEDB; in which case, this isn't an option. I can't tell from his code what he's using though.