> Hi Guys

> I have a largish Access 2000 database with some tables around 50000 records
> that are causing me great concern as far as update times go.  Currently I am
> using Access as the Database but with VB6 sp3 and ADO 2.1 as my method of
> access. All accessing of data is done through code using SQL.
> I import a text file weekly into a weeklyImport table and then cycle through
> the type of data with "A" for new lines, "D" for deletions and "C" for
> changes - the "C" are the majority.
> Typically with 2000 lines I would have about 80 new, 60 Delete and the rest
> changes against a product table of 50,000 records.

> I am currently using SQL to query the database and return subsets, so I
> would return all the ADD records and then process them, followed by creating
> a subset for delete records and then process those.

> My problem is the time it is taking to perform these updates seems long at
> nearly a second each for the INSERTS into my products table.
> and the DELETE process is only flagging each record with the word DELETE
> into a field but that is still processing at only about 2-3 records a second
> being modified.

> My basic approach is to use a SELECT * WHERE Type = "A" from my weekly
> import table to create the subsets (code below) and then to use a while loop
> to traverse the recordset and perform the INSERT after a SELECT from product
> to see if it exists and validation for each member of the recordset.
> With the DELETES I use a SELECT followed by a SELECT form products for the
> one affected and then an UPDATE command to modify the record.

> I have a largish Access 2000 database with some tables around 50000
records
> that are causing me great concern as far as update times go.  Currently I
am
> using Access as the Database but with VB6 sp3 and ADO 2.1 as my method of
> access. All accessing of data is done through code using SQL.
> I import a text file weekly into a weeklyImport table and then cycle
through
> the type of data with "A" for new lines, "D" for deletions and "C" for
> changes - the "C" are the majority.
> Typically with 2000 lines I would have about 80 new, 60 Delete and the
rest
> changes against a product table of 50,000 records.

> I am currently using SQL to query the database and return subsets, so I
> would return all the ADD records and then process them, followed by
creating
> a subset for delete records and then process those.

> My problem is the time it is taking to perform these updates seems long at
> nearly a second each for the INSERTS into my products table.
> and the DELETE process is only flagging each record with the word DELETE
> into a field but that is still processing at only about 2-3 records a
second
> being modified.

> My basic approach is to use a SELECT * WHERE Type = "A" from my weekly
> import table to create the subsets (code below) and then to use a while
loop
> to traverse the recordset and perform the INSERT after a SELECT from
product
> to see if it exists and validation for each member of the recordset.
> With the DELETES I use a SELECT followed by a SELECT form products for the
> one affected and then an UPDATE command to modify the record.

> Could someone please look at my code and see if they think my approach is
OK
> or can you suggest some other appraoch to achieve better performance..

> TIA

> Duncan Laing

> please reply to newsgroup

> ' a global connection declared and then initialized in my sub main
> Global cn As New ADODB.Connection

> Sub main()
>     '
>     ' do set up routines splash etc
>     '
>     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; _
>         DataSource=c:\POS\DATA\DATABASE.MDB; _
>         Persist Security Info=False"
>     cn.Open
>     '
>     ' hide splash etc
>     '
>     MDIForm1.Show ' contains all my work forms
> End Sub

> in declarations of my masterfile form

>     Public rsWeeklyImport As New ADODB.Recordset
>     Public rsProducts As New ADODB.Recordset

> Then in form load

>     rsWeeklyImport.ActiveConnection = cn
>     rsWeeklyImport.CursorType = adOpenKeyset
>     rsWeeklyImport.LockType = adLockOptimistic

>     rsProducts.ActiveConnection = cn
>     rsProducts.CursorType = adOpenKeyset

> Private Sub plProcessNewLines()
>     Dim slSQLString As String

>     If rsWeeklyImport.State Then rsWeeklyImport.Close

>     slSQLString = "SELECT * from WeeklyImport WHERE Type = 'A' " 'AND
> Activated = 0
>     rsWeeklyImport.Source = slSQLString
>     rsWeeklyImport.Open

>     With rsWeeklyImport
>         If .RecordCount > 0 Then
>             .MoveFirst
>             'loop through the new line recordset
>             While Not .EOF
>                 If (flValidateWeeklyImport) Then 'validate checks numeric
> fields against limits

>                     plUpdateProducts
>                     ' creates product record that can now be manipulated
> across the module

>                     ' found product & weeklyImport records at this point

>                     ' next lines sets my processed flag to true
>                     slSQLString = "UPDATE WeeklyImport Set Activated = -1
> WHERE ProductID = " & !ProductID
>                     cn.Execute slSQLString, , adCmdText
>                 'Else
>                     'plWriteReject
>                 End If
>                 .MoveNext
>             Wend
>         End If
>     End With ' rsWeeklyImport

>     txtImpNewLines = llNumRecords

> End Sub

> Private Sub plUpdateProducts()
>     Dim slSQLString As String

>     With rsWeeklyImport  'rsWeeklyImport is the recordset I am traversing

>         'check if product exists

>         If rsProducts.State Then rsProducts.Close

>         slSQLString = "SELECT * FROM Products WHERE ProductID = " &
> !ProductID
>         rsProducts.Source = slSQLString
>         rsProducts.Open

>  'there is little validation on the SQL generation because the data was
> fixed as it was stored into WeeklyImports
>         If rsProducts.RecordCount > 0 Then
>             ' if product record exists then update the order code field
>             slSQLString = "UPDATE Products SET OrderCode = '" & !OrderCode
&
> "' WHERE productID = " & !ProductID

>             cn.Execute slSQLString, , adCmdText

>             ' and then change the import record from new line to a change
> detail record this will then check promo pricing as well

>             If rsProducts!Aisle > 0 Then
>                 slSQLString = "UPDATE WeeklyImport SET Type = 'C' WHERE
> productID = " & !ProductID
>                 cn.Execute slSQLString, , adCmdText
>             End If
>         Else
>      ' I think this is my slow one
>             slSQLString = "INSERT INTO Products

>             cn.Execute slSQLString, , adCmdText
>         End If

>     End With 'rsWeeklyImport
> End Sub

> Private Sub plProcessDeletedLines()
>     Dim slSQLString As String

>     If rsWeeklyImport.State Then rsWeeklyImport.Close

>     slSQLString = "Select * from WeeklyImport WHERE Type = 'D' AND
Activated
> = 0"
>     rsWeeklyImport.Source = slSQLString
>     rsWeeklyImport.Open

>     With rsWeeklyImport
>         If .RecordCount > 0 Then
>             .MoveFirst
>             'loop through the new line recordset
>             While Not .EOF

>                 If rsProducts.State Then rsProducts.Close

>                 slSQLString = "SELECT * FROM Products WHERE ProductID = "
&
> !ProductID 'the passed in  record ID
>                 rsProducts.Source = slSQLString
>                 rsProducts.Open

>                 If rsProducts.RecordCount > 0 Then
>                     ' if product record exists then update the order code
> field
>                     slSQLString = "UPDATE Products SET OrderCode =
> 'DELETE',"
>                     slSQLString = slSQLString + "LastModified = '" &
> Format(Now(), "dd/mm/yyyy") & "' WHERE productID = " & !ProductID
>                     cn.Execute slSQLString, , adCmdText

>                     slSQLString = "UPDATE WeeklyImport Set Activated = -1
> WHERE ProductID = " & !ProductID
>                     cn.Execute slSQLString, , adCmdText
>                 Else
>                     msErrorMessage = "Product to be Deleted was not on
File
> APN = " & !ProductID
>                     plWriteReject
>                 End If
>                 .CancelUpdate
>                 .MoveNext
>             Wend
>         End If
>     End With ' rsWeeklyImport The recordsets are closed in form unload
> End Sub

>My experience is that deleting and editing work great as SQL but I have
>achieved
>better performance but using .AddNew instead of INSERT INTO.
>This may help some speed, also check your indexes.

>Jason

>> Hi Guys

>> I have a largish Access 2000 database with some tables around 50000
>records
>> that are causing me great concern as far as update times go.  Currently I
>am
>> using Access as the Database but with VB6 sp3 and ADO 2.1 as my method of
>> access. All accessing of data is done through code using SQL.
>> I import a text file weekly into a weeklyImport table and then cycle
>through
>> the type of data with "A" for new lines, "D" for deletions and "C" for
>> changes - the "C" are the majority.
>> Typically with 2000 lines I would have about 80 new, 60 Delete and the
>rest
>> changes against a product table of 50,000 records.

>> I am currently using SQL to query the database and return subsets, so I
>> would return all the ADD records and then process them, followed by
>creating
>> a subset for delete records and then process those.

>> My problem is the time it is taking to perform these updates seems long at
>> nearly a second each for the INSERTS into my products table.
>> and the DELETE process is only flagging each record with the word DELETE
>> into a field but that is still processing at only about 2-3 records a
>second
>> being modified.

>> My basic approach is to use a SELECT * WHERE Type = "A" from my weekly
>> import table to create the subsets (code below) and then to use a while
>loop
>> to traverse the recordset and perform the INSERT after a SELECT from
>product
>> to see if it exists and validation for each member of the recordset.
>> With the DELETES I use a SELECT followed by a SELECT form products for the
>> one affected and then an UPDATE command to modify the record.

>> Could someone please look at my code and see if they think my approach is
>OK
>> or can you suggest some other appraoch to achieve better performance..

>> TIA

>> Duncan Laing

>> please reply to newsgroup

>> ' a global connection declared and then initialized in my sub main
>> Global cn As New ADODB.Connection

>> Sub main()
>>     '
>>     ' do set up routines splash etc
>>     '
>>     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; _
>>         DataSource=c:\POS\DATA\DATABASE.MDB; _
>>         Persist Security Info=False"
>>     cn.Open
>>     '
>>     ' hide splash etc
>>     '
>>     MDIForm1.Show ' contains all my work forms
>> End Sub

>> in declarations of my masterfile form

>>     Public rsWeeklyImport As New ADODB.Recordset
>>     Public rsProducts As New ADODB.Recordset

>> Then in form load

>>     rsWeeklyImport.ActiveConnection = cn
>>     rsWeeklyImport.CursorType = adOpenKeyset
>>     rsWeeklyImport.LockType = adLockOptimistic

>>     rsProducts.ActiveConnection = cn
>>     rsProducts.CursorType = adOpenKeyset

>> Private Sub plProcessNewLines()
>>     Dim slSQLString As String

>>     If rsWeeklyImport.State Then rsWeeklyImport.Close

>>     slSQLString = "SELECT * from WeeklyImport WHERE Type = 'A' " 'AND
>> Activated = 0
>>     rsWeeklyImport.Source = slSQLString
>>     rsWeeklyImport.Open

>>     With rsWeeklyImport
>>         If .RecordCount > 0 Then
>>             .MoveFirst
>>             'loop through the new line recordset
>>             While Not .EOF
>>                 If (flValidateWeeklyImport) Then 'validate checks numeric
>> fields against limits

>>                     plUpdateProducts
>>                     ' creates product record that can now be manipulated
>> across the module

>>                     ' found product & weeklyImport records at this point

>>                     ' next lines sets my processed flag to true
>>                     slSQLString = "UPDATE WeeklyImport Set Activated = -1
>> WHERE ProductID = " & !ProductID
>>                     cn.Execute slSQLString, , adCmdText
>>                 'Else
>>                     'plWriteReject
>>                 End If
>>                 .MoveNext
>>             Wend
>>         End If
>>     End With ' rsWeeklyImport

>>     txtImpNewLines = llNumRecords

>> End Sub

>> Private Sub plUpdateProducts()
>>     Dim slSQLString As String

>>     With rsWeeklyImport  'rsWeeklyImport is the recordset I am traversing

>>         'check if product exists

>>         If rsProducts.State Then rsProducts.Close

>>         slSQLString = "SELECT * FROM Products WHERE ProductID = " &
>> !ProductID
>>         rsProducts.Source = slSQLString
>>         rsProducts.Open

>>  'there is little validation on the SQL generation because the data was
>> fixed as it was stored into WeeklyImports
>>         If rsProducts.RecordCount > 0 Then
>>             ' if product record exists then update the order code field
>>             slSQLString = "UPDATE Products SET OrderCode = '" & !OrderCode
>&
>> "' WHERE productID = " & !ProductID

>>             cn.Execute slSQLString, , adCmdText

>>             ' and then change the import record from new line to a change
>> detail record this will then check promo pricing as well

>>             If rsProducts!Aisle > 0 Then
>>                 slSQLString = "UPDATE WeeklyImport SET Type = 'C' WHERE
>> productID = " & !ProductID
>>                 cn.Execute slSQLString, , adCmdText
>>             End If
>>         Else
>>      ' I think this is my slow one
>>             slSQLString = "INSERT INTO Products

>(ProductID,SubDeptID,Description,ShortDescription,OrderCode,UnitCost,DavidsC
>> ost,"
>>             slSQLString = slSQLString &

>"RetailPrice,CompRetail,DiscRetail,SuperRetail,ServRetail,ConvRetail,PackSiz
>> e,SupplierID,"
>>             slSQLString = slSQLString &
>> "NumberOfLabels,Aisle,Bay,TaxCode,TaxRate,DavidsClassificationCode) VALUES
>> ("
>>             slSQLString = slSQLString & !ProductID & ","
>>             slSQLString = slSQLString & !SubDepartment & ","
>>             slSQLString = slSQLString & "'" & !Description & "',"
>>             slSQLString = slSQLString & "'" & !ShortDescription & "',"
>>             slSQLString = slSQLString & "'" & !OrderCode & "',"
>>             slSQLString = slSQLString & !UnitCost & ","
>>             slSQLString = slSQLString & !UnitCost & ","
>>             slSQLString = slSQLString & !SuperRetail & ","
>>             slSQLString = slSQLString & !CompRetail & ","
>>             slSQLString = slSQLString & !DiscRetail & ","
>>             slSQLString = slSQLString & !SuperRetail & ","
>>             slSQLString = slSQLString & !ServRetail & ","
>>             slSQLString = slSQLString & !ConvRetail & ","
>>             slSQLString = slSQLString & !packSize & ","
>>             slSQLString = slSQLString & "1,"     'this is Supplier ID and
>> must be set to 1
>>             slSQLString = slSQLString & "1,"      ' default number of
>labels
>>             slSQLString = slSQLString & "0,"      ' aisle = 0 till added
>to
>> store
>>             slSQLString = slSQLString & "0,"      ' bay = zero
>>             slSQLString = slSQLString & "'" & !TaxCode & "',"
>>             slSQLString = slSQLString & "0,"        ' Tax Rate
>>             slSQLString = slSQLString & "'" & !RetailClassificationCode &
>> "');"

>>             cn.Execute slSQLString, , adCmdText
>>         End If

>>     End With 'rsWeeklyImport
>> End Sub

>> Private Sub plProcessDeletedLines()
>>     Dim slSQLString As String

>>     If rsWeeklyImport.State Then rsWeeklyImport.Close

>>     slSQLString = "Select * from WeeklyImport WHERE Type = 'D' AND
>Activated
>> = 0"
>>     rsWeeklyImport.Source = slSQLString
>>     rsWeeklyImport.Open

>>     With rsWeeklyImport
>>         If .RecordCount > 0 Then
>>             .MoveFirst
>>             'loop through the new line recordset
>>             While Not .EOF

>>                 If rsProducts.State Then rsProducts.Close

>>                 slSQLString = "SELECT * FROM Products WHERE ProductID = "
>&
>> !ProductID 'the passed in  record ID
>>                 rsProducts.Source = slSQLString
>>                 rsProducts.Open

>>                 If rsProducts.RecordCount > 0 Then
>>                     ' if product record exists then update the order code
>> field
>>                     slSQLString = "UPDATE Products SET OrderCode =
>> 'DELETE',"
>>                     slSQLString = slSQLString + "LastModified = '" &
>> Format(Now(), "dd/mm/yyyy") & "' WHERE productID = " & !ProductID
>>                     cn.Execute slSQLString, , adCmdText

>>                     slSQLString = "UPDATE WeeklyImport Set Activated = -1
>> WHERE ProductID = " & !ProductID
>>                     cn.Execute slSQLString, , adCmdText
>>                 Else
>>                     msErrorMessage = "Product to be Deleted was not on
>File
>> APN = " & !ProductID
>>                     plWriteReject
>>                 End If
>>                 .CancelUpdate
>>                 .MoveNext
>>             Wend
>>         End If
>>     End With ' rsWeeklyImport The recordsets are closed in form unload
>> End Sub