Simple looping insert (one column) to local .mdb takes 4 min for 3k records... why?

[Windsailor]

[FONT=&quot]The following code takes approx 4 min to insert 3000 records into a local mdb database.
Using another database engine and provider the same code takes 5 seconds, why?
[/FONT][FONT=&quot]I have tried indexing and not indexing the column etc. and it still takes around 4 min to insert 3000 records, which is absolutely terrible.
I am missing something...
Is there a better way to do this?[/FONT]
[FONT=&quot]
[/FONT]

[FONT=&quot]Dim A As Double
Dim X As Double
Dim XVal As Double

Me.Txt2.Focus()
A = Val(Me.Txt2.Text) 'To set the incremental value
Me.Txt4.Focus()
X = Val(Me.Txt4.Text) 'To set the Max value

XVal = 0

cmd.CommandText = "INSERT INTO MyTableName(Column1)VALUES (@Column1)"
cmd.Connection = conn

conn.Open()

Do Until XVal >= X

If XVal < X Then

XVal = (XVal + A)

cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@Column1", XVal))
cmd.Parameters("@Column1").Value = XVal

cmd.ExecuteNonQuery()

Else
Exit Do 'Exit Loop
End If

Loop

conn.Close()
conn.Dispose()
conn = Nothing
 [/FONT]

[FONT=&quot]

Thanks

[/FONT]

 

[TechGnome]

What DBMS are you using? Access? Oracle? SQL Server? IT makes a lot of difference... the oledb class is a generic class designed to work with any OLDEB compliant database system. As such it's not optimized for any specific DB. If you are inserting into SQL Server, use the SQLClient namespace.... if you are using ORacle, use the appropriate namespace there.

-tg

 

[cjard]

[FONT=&quot]The following code takes approx 4 min to insert 3000 records into a local mdb database.[/FONT]

The code is borked.. we'll get to that

[FONT=&quot]Using another database engine and provider the same code takes 5 seconds, why?[/FONT]

Uhm.. same code? Are you sure?

[FONT=&quot]I have tried indexing and not indexing the column etc. and it still takes around 4 min to insert 3000 records, which is absolutely terrible.[/FONT]

Indexing helps selection, not insertion or updating. THink about this. If you wrote a book, wrote an index, then changed the book ,you'd have to change the index. Inserting to an indexed table is slower than a non-indexed one!

[FONT=&quot]I am missing something...[/FONT]
[FONT=&quot]Is there a better way to do this?[/FONT]

I've changed the code, see the comments

        [B]'do try to give variables more meaningful names. [/B]
[B]      'dont start variable names with caps[/B]
[B]      'dont use doubles if you dont need the floating point[/B]
[B]      'Me.Txt4 --> the Me is redundant[/B]
[B]      'Txt4 --> dont call it Txt4! use a proper name![/B]
[B]      'you dont need to focus textboxes before you get the value out of them![/B]
[B]      'dont use Val(). It's old. Use Convert.ToXXXXX()[/B]
        Dim incr As Double = Convert.ToDouble(Txt2.Text)
        Dim maxVal As Double = Convert.ToDouble(Txt4.Text)
        Dim xVal As Double
 
 
 
 
        cmd.CommandText = "INSERT INTO MyTableName(Column1)VALUES (@Column1)"
        cmd.Connection = conn
 
        [B]'do NOT put parameter additions inside a loop! if the loop runs 3000 times your[/B]
[B]      'db command will have 3000 parameters added to it by the end of the loop![/B]
[B]      'add your parameters once. ONCE. NOT IN ANY LOOP[/B]
[B]      'set the value 3000 times, sure, but dont keep adding them![/B]
[B]      'this time we also keep reference of the parameters -- see later[/B]
[B]      '(.Add() returns the param that was added)[/B]
   Dim dbp As OleDBParameter = _
          cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("@Column1", xVal))
 
        conn.Open()
 
        [B]'doing something until a numeric threshold is reached is why FOR loops exist[/B]
[B]      'your code didnt start at 0, it does an add of (XVal + A), this is why i start[/B]
[B]      'my loop at INCR[/B]
        For xVal = incr To maxVal Step incr
 
            [B]'this is why we kept the returned parameter before, so we arent[/B]
[B]          'constantly adding the extra step of looking it up with cmd.Parameters("@Column1")[/B]
            dbp.Value = xVal
            cmd.ExecuteNonQuery()
 
        Next xVal
 
        conn.Close()
        conn.Dispose()
        conn = Nothing

The biggest culprit I can see is repeatedly trying to add a parameter that already exists.. The whole idea of parameterized queries is that you set it up once, then change the values.

 

[cjard]

The only thing I didnt do, was set the type of the parameter to be a double. You should do this.

Actually you should read the DW2 link in my signature and follow the advice therein for creating a tableadapter that contains this insert command. While not 100% as targetedly efficient as this specific case, I have no doubt that it would perform on a par with the 5 seconds time

Actually, really you should be using DW2, make a datatable locally, insert your 5000 values and then just say Update() on the tableadapter:

        Dim mtndt as New MyTableNameDataTable
 
        For xVal = incr To maxVal Step incr
          mtndt.AddRow(xVal)           
        Next xVal
        Dim mtnta as New MyTableNameTableAdapter
        mtnta.Update(mtndt)

Not only is this code beautifully simple, it's guaranteed to work, fast and be well encapsulated, maintainable and sensible

 

[Windsailor]

The code is borked.. we'll get to that

Hmmm...
Is that a word?
I think I will be cautious if we ever play scrabble...
But it fits. Just no additional points.

Uhm.. same code? Are you sure?

Yep. Just different database and provider.

I understood the bit about the indexing... just threw it in there...
Here is an old thread I had on indexes when I was researching them...
http://www.access-programmers.co.uk/forums/showthread.php?t=103298

The biggest culprit I can see is repeatedly trying to add a parameter that already exists...

Ok... I'll go with that.
Thanks cj for your sample; give me some time and I will work it in and get back to you.

 

[Windsailor]

Hi tg,
Actually I was using VistaDB and had used TurboDB in the past and comparing the existing code with a .mdb file which is Access.
Originally I started with Access (local databases) and then made the jump to .net.
I was shocked by the time difference just by changing providers, but at the same time it told me that I was off on a basic concept.
Not really an easy thing to comprehend, .net and ado.net all at once, even though I have been reading several books on the side for a while...
So I am forever learning this on the side (hobby - basically when I have time).

 

[Windsailor]

cj,

Yes...

That cut the time down from 4 min to 1.4 seconds to delete and then insert 3000 records. Good lesson.

I did actually start from zero (just by adding the first row and then using ExecuteNonQuery) and then run the loop.

Is there another way to start from zero?

Very, very good point about the datatable and tableadapter.

 

[cjard]

Another way to start from 0?

Umm..

For loop basic structure:
For VARNAME As TYPE = START To END Step INCREMENT
''code
Next VARNAME


Rewritten in VB While:
Dim VARNAME As TYPE = START
While VARNAME <= END
''code
VARNAME += INCREMENT
Loop


Maybe this makes it easier to understand! I made my start = my increment because you seemed to do:

Var = 0
Var += Increment
Run query


Hence I thought you needed to start at Increment, not 0..

 

[cjard]

Buuut, bear strongly in mind that the two loops i posted there are not substitutable for each other:

If a For Loop contains a Continue For statement, you cannot swap it for the given While loop.. Continue For causes the increment to take place. Converting to Continue While causes the increment NOT to take place... Im sure you understand why!
For loops in other languages are very powerful and can be used to replace all other loops, but in VB they are a bit retarded and so have their special place and shouldnt be exchanged for another without special thought..