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!

Edited November 17, 2002 by divil

[Derek Stone]

dt.Rows.Add(row)

da.Update(ds, "catxprod")

There's 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]

Edited November 17, 2002 by divil

[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!