Data output performance 1

[Anesthaesia]

Hi All,

I am outputting about 30,000 lines of text (all lines approx 50-70 chrs long) to an Access database. This database has 1 table, 1 field and is stored locally (on same PC)

To save these records from VB takes approx 80secs. However, if I send the exact same text to a TXT file, it takes 3 seconds...

Is this performance normal for a database? Is there anything I need to change/optimise on the DB to improve this?

I am using DB.EXECUTE "INSERT INTO" to save these records. There are also no other checks/queries being performed - this is using a blank table.

Any help would be appreciated.

Cheers,
D

 

[JerryKlmns]

Am I guessing that you are appending records one by one?
I would create a recordset like

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
With rst
    .ActiveConnection = SafeCnn
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic 
    .Source="YourTableNameHere"
    .Open
    Do While [i]condition[/i]
      .AddNew
      .Field(0)= Something
    Loop
    .UpdateBatch
    .Close
End With
Set rst = Nothing

Until the code executes the UpdateBatch method everything is proccessed in memory. That UpdateBatch line talks back to database.

Would this improve performance!?!

 

[Anesthaesia]

Hi Jerry,

This is considerably faster - creating the recordset takes the same time as outputting to the TXT file (3secs) but writing to the DB takes a bit longer - total time 30sec.

This should be fine for now - obviously this performance would change based on HD/CPU speed etc, but is much better than 1m20

Thanks for the help.

Cheers,
D

 

[chiph]

Databases typically write the data to disk twice -- once to the transaction log, and when the data is committed, then to the table where they'll be stored. This allows them to fulfill the ACID contract.

http://en.wikipedia.org/wiki/ACID

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[JerryKlmns]

chiph

Thank you very much for that informative link!

 

[dilettante]

Have you tried this with the database open in exclusive mode? There are some locking optimizations that may speed the process for you a bit more.

Either set the Mode property on the (ADO) Connection to adModeShareExclusive (i.e. 12) or perhaps add ";Mode=Shared Exclusive" to your connection string.

 

[Anesthaesia]

Thanks for the replies and informative links guys...

I don't think any further changes will improve the performance drastically - chiph probably answered my question best, as this is obviously due to the design of databases.

I had never compared writing to a text file vs a database before, and was concerned that my code wasn't optimised (which is where Jerry helped me too)

Thanks for the help,
D