Link to home
Start Free TrialLog in
Avatar of EYoung
EYoungFlag for United States of America

asked on

MS Access discussion...

Basics:  VB6 sp5, Windows NT 4.0

Question:  I have a VB app that uses Microsoft Access to store data.  When I have a table with just 10 fields, the read/write to the table is fairly quick.  But when I increase the number of fields to 20, the speed drops down significantly.  When the number goes to 30 or more, Access really slows.  Ideally I would like to have a table with about 65 - 70 fields in it and that would be so slow when I add 30,000 records.

The number of records in the table does not seem to matter much nor does the definition of the fields.  The only thing that seems to really matter is the number of fields in the table.  This reminds me of Paradox, at least versions 5 and 6 from the DOS world.  Borland's suggestion was to get a faster computer.

Any suggestions?  I can't break up the table into smaller tables with fewer fields.
Avatar of Richie_Simonetti
Richie_Simonetti
Flag of Argentina image

Maybe, splitting that table to two or more.
Avatar of EYoung

ASKER

As my question stated:  "I can't break up the table into smaller tables with fewer fields."

Are there any techniques that could help improve the speed or does Access just slow down when the number of fields increases?

Appreciate everyone's help.
How do you update the database?

Always try to use INSERT, UPDATE and DELETE SQL statement.
Avatar of EYoung

ASKER

I use the rs.Update statement to change information in an existing record.

However, when I am adding new records, I use the rs.AddNew statement followed by the rs.Update statement.
Do some benchmark using the INSERT, UPDATE and DELETE SQL statements.
Avatar of EYoung

ASKER

OK.  I need to leave now and won't be able to test this until I return next Monday the 9th.  I'll let you know.
>>How do you update the database (by emoreau)
That might be a crucial factor. Using SQL statements INSERT, UPDATE and DELETE might help a bit.

Furthermore do use the connection.Execute method to do the editions.

hongjun
Sorry, i just did read until "Any suggestions?"
ASKER CERTIFIED SOLUTION
Avatar of inthedark
inthedark
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
interesting..
<Summary: OpenTable Addnew/Update is about 50 times faster than SQL Insert Into statements.>

Really strange .
Summary: OpenTable Addnew/Update is about 50 times faster than SQL Insert Into statements.>

Really strange .

yep that is backwards from anything I have ever seen before???  I am much more familiar with sql server though.  using sql statements is no comparison to doing addnew update.  It is many many times faster.
jrspano & emoreau I was as surprised as you.

If you want to make an independent test:

1 - Create a new project (standard exe)

2 - setup a project reference to MS DAO v 3.x

3 - create a command button.

4 - paste the code that was posted previously

5 - run the example.

You may wish to edit the create database method and openrecord set code options.

I may make a modification to trial ADO in the same manner.

jrspano & emoreau I was as surprised as you.

If you want to make an independent test:

1 - Create a new project (standard exe)

2 - setup a project reference to MS DAO v 3.x

3 - create a command button.

4 - paste the code that was posted previously

5 - run the example.

You may wish to edit the create database method and openrecord set code options.

I may make a modification to trial ADO in the same manner.

Avatar of EYoung

ASKER

Thank you all for the comments.  I will try the "Set RS = DB.OpenRecordset("Select * From TestTable;", dbOpenDynaset, dbAppendOnly)" command when I get back to the client next Monday the 9th.
It would be faster to use:

Set RS = DB.OpenRecordset("Select * From TestTable;",
dbOpenTable, dbAppendOnly

Open table works a lot faster than dynaset.
Avatar of EYoung

ASKER

My project uses ADO and not DAO.  Can the above ideas be applied to ADO?

Below is a test of what I am trying to do.  In the test I am just adding 100 records to TestTable using a counter.  TestTable is an Access 2000 table with 30 fields in it.  There are no indexes or rules for the table.  The test below takes 10 seconds to add the 100 records.  10 seconds is an incredibly long time to add just 100 records.  

How can I make the code below run faster?  I have increased the points to 100.  My thanks to everyone for the suggestions.


Option Explicit
Dim cnAccess As ADODB.Connection
Dim rsTestTable As ADODB.Recordset
Dim mCounter As Long

Private Sub Form_Load()
    Set cnAccess = New ADODB.Connection
    cnAccess.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Sales Management System\SMS.mdb;Persist Security Info=False"
    cnAccess.Open
End Sub

Private Sub cmdStart_Click()
    mCounter = 0
    lblStatus.Caption = mCounter
    lblStatus.Refresh

    Do Until mCounter >= 100
        Set rsTestTable = New ADODB.Recordset
        rsTestTable.Source = "Select * from TestTable" & _
            " Where Cust_Id = 'New ID'"
        Set rsTestTable.ActiveConnection = cnAccess
        rsTestTable.CursorType = adOpenDynamic
        rsTestTable.LockType = adLockOptimistic
        rsTestTable.Open

        rsTestTable.AddNew
        mCounter = mCounter + 1
        rsTestTable.Fields("Cust_Id") = CStr(mCounter)
        rsTestTable.Update
       
        lblStatus.Caption = mCounter
        lblStatus.Refresh
    Loop
   
    rsTestTable.Close
    Set rsTestTable = Nothing
End Sub

Private Sub cmdExit_Click()
    End
End Sub
ADO is much slower than DAO.

It is possible to open an ADO recordset for AppendOnly although the documentation that explains the syntax is quite hard to find.

You have to create a connection, then create a recordset but don;t opem it.  Then set the recpordset properties "APPEND_ONLY" = True.

I think it is a bit like this:

set RS=New ADODB.Recordset
RS.Properties("Append-only RowSet")=True

then open the recordset.


Avatar of EYoung

ASKER

Thanks for the help.  My thanks to everyone for the suggestions.  I think the best thing to do will be to import from Unix to SQL Server during the night.  Then the user can import from SQL Server to Access during the day which should be faster - hopefully.

The more I use Access the more I see that it is not a corporate solution nor is it a multi-user solution.