Solved

MS Access discussion...

Posted on 2001-07-03
19
261 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:EYoung
  • 6
  • 5
  • 3
  • +4
19 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6250472
Maybe, splitting that table to two or more.
0
 
LVL 7

Author Comment

by:EYoung
ID: 6250495
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.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6250760
How do you update the database?

Always try to use INSERT, UPDATE and DELETE SQL statement.
0
 
LVL 7

Author Comment

by:EYoung
ID: 6250773
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.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6250809
Do some benchmark using the INSERT, UPDATE and DELETE SQL statements.
0
 
LVL 7

Author Comment

by:EYoung
ID: 6250877
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.
0
 
LVL 33

Expert Comment

by:hongjun
ID: 6250925
>>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
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6251104
Sorry, i just did read until "Any suggestions?"
0
 
LVL 17

Accepted Solution

by:
inthedark earned 100 total points
ID: 6251192
I created a timer. It seems to be a fact that the time taken for a record update increases per field on an exponential basis.

The extra time taken for adding one field to a record with 50 fields is about 2.2 millionths of a second.

The extra time taken for adding one field to a record with 100 fields is about 2.8 millionths of a second.

This extra time is so small that I think there must be some other factors why EYoung's record is taking longer to write e.i. more fields = more data = more time and also more fields = more rules = more time.

So we have to questions like how many of the extra fields are indexed? Do these extra fields have rules e.g. Don't allow nulls?

I pasted the result for each trial into Excel.

I also discovered that:

Adding to an Access V97 DB using DAO3.6 is 30% faster than a V1.1 DB using compatibility mode.

Transactions take twice as long.

For a test with 10 fields and 100,000 records I got the following results:

OpenTable took 3.495 seconds.
OpenDynaset with dpAppendOnly took 5.468 seconds.
OpenDynaset without dpAppendOnly took 8.542 seconds.
Using SQL Insert Into took 172 seconds.

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

Private Sub Command1_Click()

' function to calculate access database speeds

Dim DB As DAO.Database
Dim TD As DAO.TableDef
Dim FLD As DAO.Field
Dim RS As DAO.Recordset

ReDim qflds(0) As DAO.Field ' quick access to the fields in the recordset

Dim sttimer As Double   ' start time
Dim ctimer As Double    ' end time

Dim FtoC As Long ' Fields to Create
Dim FtoCr As Long ' 1 less than FtoC
Dim RtoC As Long ' Records to Create
Dim FC As Long ' Fields counter
Dim RC As Long ' Records counter
Dim sql$
Dim vrec$

Dim ok
Dim M$
Dim COMMENT$
Dim Transactions As Boolean
Dim SQLInto As Boolean

FtoC = Val(InputBox("Enter number fields to create"))
RtoC = Val(InputBox("Enter number records to create"))

ok = MsgBox("Use transactions Y/N", vbYesNo + vbQuestion)


Transactions = False
If ok = vbYes Then
    Transactions = True
End If

SQLInto = False
ok = MsgBox("Use SQL Insert Into to Create Records Y/N", vbYesNo + vbQuestion)
If ok = vbYes Then
    SQLInto = True
End If


' specify database name
dbn$ = "C:\MyDataBase.MDB"

If Len(Dir$(dbn$)) > 0 Then
    Kill dbn$
    DoEvents
End If

'Create a database
'Set DB = DAO.DBEngine.CreateDatabase(dbn$, dbLangGeneral, dbVersion11)' Access V1.1
Set DB = DAO.DBEngine.CreateDatabase(dbn$, dbLangGeneral, dbVersion35) ' Access 97
'Set DB = DAO.DBEngine.CreateDatabase(dbn$, dbLangGeneral, dbVersion40) ' Access 2000


' Create a tabledef(inition)
Set TD = DB.CreateTableDef("TestTable")

' Add Fields to the tabledef
For FC = 1 To FtoC
    Set FLD = TD.CreateField("FLD" + Format$(FC, "000000"), dbLong)
    TD.Fields.Append FLD
Next FC

' Now save the tabledef in the database
DB.TableDefs.Append TD

' Now Openthe Table using a choice of methods
Set RS = DB.OpenRecordset("TestTable", dbOpenTable, dbSeeChanges)
'Set RS = DB.OpenRecordset("Select * From TestTable;", dbOpenDynaset, dbseechanges)
'Set RS = DB.OpenRecordset("Select * From TestTable;", dbOpenDynaset, dbAppendOnly)


' set fast access to the fields in the recordset
FtoCr = RS.Fields.Count - 1

ReDim qflds(FtoCr)

For FC = 0 To FtoCr
    Set qflds(FC) = RS(FC)
Next FC

DoEvents

' record start time
sttimer = CDbl(Timer)

' add rtoc records to the file
For RC = 1 To RtoC
    If Transactions Then
        Workspaces(0).BeginTrans
    End If
   
    If SQLInto Then
        ' Use insert into
        sql = "Insert Into [TestTable] ("
        vrec = ") Values ("
        For FC = 1 To FtoC
            If FC > 1 Then
                sql = sql + ", [FLD" + Format$(FC, "000000") + "]"
                vrec = vrec + ", " + CStr(RC)
            Else
                sql = sql + "[FLD" + Format$(FC, "000000") + "]"
                vrec = vrec + CStr(RC)
            End If
        Next FC
       
        sql = sql + vrec + ")"
        DB.Execute sql, dbAppendOnly
                           
    Else
        ' Use recordset addnew/updates
        RS.AddNew
           
        ' make each fields equal to the record number
        For FC = 0 To FtoCr
            qflds(FC) = RC
        Next FC
       
        RS.Update
    End If
   
    If Transactions Then
        Workspaces(0).CommitTrans
    End If
   
Next RC


RS.Close
DB.Close

' get current time
ctimer = CDbl(Timer)


' handle midnight
If ctimer < sttimer Then
    ctimer = ctimer + 86400
End If

ctimer = ctimer - sttimer ' clalculate time taken
If Transactions Then
   COMMENT$ = "Using transactions"
Else
    COMMENT$ = "Not Using transactions"
End If
If SQLin Then
   COMMENT$ = COMMENT$ + " Using SQL Insert Into"
Else
    COMMENT$ = COMMENT$ + " Using Addnew/Update"
End If

M$ = M$ + COMMENT$ + vbCrLf

M$ = ""
M$ = M$ + CStr(FtoC) + Chr$(9)
M$ = M$ + CStr(RtoC) + Chr$(9)
M$ = M$ + Format$(ctimer / FtoC, "0.000000000") + Chr(9)
M$ = M$ + Format$(ctimer / (FtoC * RtoC), "0.000000000") + Chr(9)
M$ = M$ + Format$(ctimer / RtoC, "0.000000000") + Chr(9)
M$ = M$ + Format$(ctimer, "0.000") + Chr(9)
M$ = M$ + COMMENT$ + vbCrLf
Clipboard.Clear
Clipboard.SetText M$

M$ = "Fields in record: " + CStr(FtoC) + vbCrLf
M$ = M$ + "Records in table: " + CStr(RtoC) + vbCrLf
M$ = M$ + vbCrLf
M$ = M$ + "Time taken per field for all records: " + Format$(ctimer / FtoC, "0.000000000") + vbCrLf
M$ = M$ + "Time taken per field per records: " + Format$(ctimer / (FtoC * RtoC), "0.000000000") + vbCrLf
M$ = M$ + "Time taken per record: " + Format$(ctimer / RtoC, "0.000000000") + vbCrLf
M$ = M$ + "Overall time taken: " + Format$(ctimer, "0.000") + vbCrLf
M$ = M$ + COMMENT$ + vbCrLf
M$ = M$ + COMMENT$ + vbCrLf
M$ = M$ + vbCrLf
M$ = M$ + "Notes: All times in seconds. Data has been pasted in Excel format onto the clipboard." + vbCrLf
M$ = M$ + "Please report bugs to: nyoung@vipintersoft.com" + vbCrLf

MsgBox M$, vbInformation, "Database Times"



End Sub

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6251236
interesting..
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 6252272
<Summary: OpenTable Addnew/Update is about 50 times faster than SQL Insert Into statements.>

Really strange .
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6252425
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.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6252802
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.

0
 
LVL 17

Expert Comment

by:inthedark
ID: 6253190
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.

0
 
LVL 7

Author Comment

by:EYoung
ID: 6257152
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.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6258665
It would be faster to use:

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

Open table works a lot faster than dynaset.
0
 
LVL 7

Author Comment

by:EYoung
ID: 6266230
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
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6267447
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.


0
 
LVL 7

Author Comment

by:EYoung
ID: 6270429
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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now