Speed up ADO.NET

[Answer]

Here is my code im using for testing purposes.

        Try

            Dim sConnectionString As String

            sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\DATA;Extended Properties=dBase III"
            Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
            objConn.Open()

            Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from catxprod", objConn)

            Dim ds As New DataSet()

            da.Fill(ds, "catxprod")
            Dim dt As DataTable = ds.Tables("catxprod")
            Dim dr As DataRow = dt.Rows(1)

            MsgBox(dt.Rows(1).Item(5))
            Dim x As Integer, row As DataRow
            Dim strCommand = ("INSERT INTO products( [ID], [catcount] ,[agrpcount] ," & _
            "[pgrpcount] ,[order], [code], [name], [thumbnail], [image], [price]" & _
            ", [cost], [desc], [weight], [taxable], [active]) VALUES (ID)")



            Dim strCommand = "INSERT INTO catxprod([cat_id], [product_id], [order]) VALUES (@cat_id, @product_id, @order)"
            da.InsertCommand = New OleDb.OleDbCommand(strCommand, objConn)
            Dim param As OleDbParameter
            @ID Parameter
            param = da.InsertCommand.Parameters.Add(New OleDbParameter("@cat_id", OleDb.OleDbType.Integer))
            param.SourceColumn = "cat_id"
            param = da.InsertCommand.Parameters.Add(New OleDbParameter("@product_id", OleDb.OleDbType.Integer))
            param.SourceColumn = "product_id"
            param = da.InsertCommand.Parameters.Add(New OleDbParameter("@order", OleDb.OleDbType.Integer))
            param.SourceColumn = "order"

            Dim autogen = New OleDb.OleDbCommandBuilder(da)
            MsgBox(autogen.GetInsertCommand.CommandText)
            PB1.Maximum = 5000
            PB1.Minimum = 1
            Dim x As Integer
            For x = 1 To 5000
                row = dt.NewRow()
                row(0) = 444
                row(1) = 345
                row(2) = 111111
                dt.Rows.Add(row)
                da.Update(ds, "catxprod")
                PB1.Value = x
            Next

The code above takes forever to complete, i had the same problem with regular ADO and i somehow fixed it. Can anyone help optimize this code so it only takes say .3 seconds like the regualr ADO did?

btw...i have seen a lot of people rave about how ADO.NET is better, so far if you ask me, its a pain in the arse, it takes 5 times the amount of code for me to do the same thing that i could in ADO!!

Thanks!

 

[Derek Stone]

dt.Rows.Add(row)
da.Update(ds, "catxprod")

Theres your problem. You call .Add 5000 times and .Update another 5000 times. Try to create more than one row at a time using SQL.

 

[Answer]

Try to create more than one row at a time using SQL.

Im sorry, could you provide me an example?

i understand taht calling update 5000 is slow, but i already tried calling it from outside the loop and its still WAY slower then ADO.

 

[Answer]

Derek, okay, i tried using a Command Object. I ahve posted the code below, using the command object was much faster then the previous way, however it still cannot touch regular ADO. Could it be becuase im accessing a DBASE III database and not a SQL database?? I have never used the Command object before so i dont really know what im doing, but this is what i came up with.

        Try
            Dim strConnectionString As String
            strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=C:\DATA;Extended Properties=dBase III"
            Dim objConn As New System.Data.OleDb.OleDbConnection(strConnectionString)
            objConn.Open()

            Dim dbCommand As New Data.OleDb.OleDbCommand()
            dbCommand.Connection = objConn
            dbCommand.CommandType = CommandType.Text
            dbCommand.CommandText = "INSERT INTO products( [ID], [catcount] ,[agrpcount] ," & _
            "[pgrpcount] ,[order], [code], [name], [thumbnail], [image], [price]" & _
            ", [cost], [desc], [weight], [taxable], [active]) VALUES (33,33,33,33,33,33,33,33,33,33,33,33,33,33,33)"
            Dim x As Integer
            PB1.Maximum = 5000
            PB1.Minimum = 0
            For x = 0 To 5000
                dbCommand.ExecuteNonQuery()
                PB1.Value = x
            Next




        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

[edit]Added [vb][/code] tags[/edit]

 

[Derek Stone]

Try more than one INSERT at a time.

dbCommand.CommandText = "INSERT INTO products( [ID], [catcount] ,[agrpcount] ," & _
            "[pgrpcount] ,[order], [code], [name], [thumbnail], [image], [price]" & _
            ", [cost], [desc], [weight], [taxable], [active]) VALUES (33,33,33,33,33,33,33,33,33,33,33,33,33,33,33); " & _
"INSERT INTO products( [ID], [catcount] ,[agrpcount] ," & _
            "[pgrpcount] ,[order], [code], [name], [thumbnail], [image], [price]" & _
            ", [cost], [desc], [weight], [taxable], [active]) VALUES (33,33,33,33,33,33,33,33,33,33,33,33,33,33,33); " & _
"INSERT INTO products( [ID], [catcount] ,[agrpcount] ," & _
            "[pgrpcount] ,[order], [code], [name], [thumbnail], [image], [price]" & _
            ", [cost], [desc], [weight], [taxable], [active]) VALUES (33,33,33,33,33,33,33,33,33,33,33,33,33,33,33); " & _
"INSERT INTO products( [ID], [catcount] ,[agrpcount] ," & _
            "[pgrpcount] ,[order], [code], [name], [thumbnail], [image], [price]" & _
            ", [cost], [desc], [weight], [taxable], [active]) VALUES (33,33,33,33,33,33,33,33,33,33,33,33,33,33,33)"

 

[Answer]

I tried the code you posted and it wont work, says "Characters found after SQL statement"

I have tried modifing it but i am unable to correct the problem. You have any ideas?


Thanks for your Help Derek!