ADO "AddNew" Performance Issue 
Author Message
 ADO "AddNew" Performance Issue

I've added a fixed width import routine to an application which is
giving me performance problems.  The intent is to write only good rows
into the database, skip rows which don't meet a certain condition, and
write rows with errors into an error file.   I use Line Input to read
each line into a variable (strLine) and a collection of "Fields" to
evaluate the text as I parse it.  So, for each line I read from the text
file, I execute the following code:

    For Each objField in ImportFields
        objField.Value = Mid(strLine, objField.Start, objField.Width)
        If objField.MetConditions = False Then Goto SkipRowLabel
    Next objField

    On Error Goto BadRowLabel
    ADORcdset.AddNew
        For Each objField In ImportFields
            ADORcdset(objField.Name) = objField.Value
        Next objField
    ADORcdset.Update

I tested this on a file with 100,000 rows.  The OS is Windows NT
Workstation.

First, I used it to load a table in a Jet Database -
"Provider=Microsoft.Jet.OLEDB.4.0".
Then an identical table in a SQL7 database on the same machine -
"Provider=SQLOLEDB.1"
In both cases I opened the recordset as follows:
    ADORcdset.Open SQLStatement, ADOConnection, adOpenKeyset,
adLockOptomistic

Over the Jet connection, it took 1:40 to load all of the records.
Over the SQL7 connection it took 9:49.  Of course, this is the
connection I'll be using most of the time.
There were absolutely no differences between the two tests save the
connection string.  What could make updates over the SQL7 connection
nearly 6 times slower?



Wed, 14 Nov 2001 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. AddNew and "Edit" with ADO

2. AddNew and "Edit" with ADO

3. AddNew "Not Supported by Provider"?

4. Problem whit "Addnew" in Microsoft Interdev

5. "rolling back" on AddNew

6. *"*-.,._,.-*"* I"LL TRADE VISUAL C++ FOR VBASIC *"*-.,_,.-*"*

7. Licencing Issue "comdlg32.ocx"

8. Various issues with "Drawing Canvas"

9. FREE issue of "VB Systems Journal"

10. Newbie: Another "Always on top" issue

11. Visual Basic bugs (exsqueeze me, "issues")

12. FREE issue of "VB Systems Journal"

 

 
Powered by phpBB® Forum Software