Updating DBF records via ADO is slow

From: Colin McLelland (colinj_mc_at_hotmail.com)
Date: 03/28/04

  • Next message: rbutch: "connecting VB dot Net to Oracle 8i" 
    Date: Sun, 28 Mar 2004 04:26:06 -0800

    I have a frustrating database update performance problem that I really hope someone can help me with. It should be so simple and I hope it is me being incredibly thick rather than a problem with ADO.

    I am using VB with ADO to connect to a Foxpro .DBF database in a standalone application so catering for other users accessing the data is not an issue. The test database has 86000 records with 59 fields, however I am only interested in 5 of them, one being used for searching, 3 to update and one as a unique ref to identify any problem records.

    Because virtually every record is being changed I am creating a recordset of the entire dbf file, with just the fields of interest, ready to filter as necessary.

    I have a second dbf file with 500 records acting as a lookup table containing the changes to be made to the main database. I step through each record of the lookup table getting the search code and new data for the three items to be changed. Using the search code I create a filtered recordset of all the items in the main database with the search code. I then step through that 'subset' replacing the value of three fields with the new and then the problems start. It is so, so, so slow to the point where it would be quicker to do it manually!

    Using an adUseClient cursor location, adLockOptimistic Lock Type and AdOpenStatic Cursor Type so that each record is updated as I MoveNext through the recordset I reckon would take about 22hrs to complete (based on some trials I carried out).

    Using an adUseClient cursor location, adLockBatchOptimistic Lock Type and AdOpenStatic Cursor Type in conjuction with UpdateBatch once I have MovedNext through the filtered recordset has so far taken 16hrs to deal with 60000 records.

    I am using a DSN-Less connection and my connection string includes..

    sConStr = "Provider=MSDASQL.1;Persist Security Info=False"
    sConStr = sConStr & ";Driver={Microsoft Visual FoxPro Driver};UID=;SourceType=DBF"
    ...plus more

    I am running all this on Dell Inspiron 8100 notebook (PIII 664Mhz - 256KRAM)

    Because I am running standalone I assume that creating a disconnected recordset is not going to make any difference, I will still have the performance hit when it comes to BatchUpdate on reconnect.

    I have tried using SQL statements to build the recordset but that was slower at getting the data than using the filter approach so I didn't pursue that line to update the data.

    I have also tried using a Server Side approach but in trailing it the time taken was even longer that using Client Side.

    I hope I'm missing something really fundamental and I'd be more than happy for someone to draw it to my attention in no uncertain terms and point me in the right direction!
     
    Many thanks in anticipation
    Colin McLelland

  • Next message: rbutch: "connecting VB dot Net to Oracle 8i"