• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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.
0
EYoung
Asked:
EYoung
  • 6
  • 5
  • 3
  • +4
1 Solution
 
Richie_SimonettiIT OperationsCommented:
Maybe, splitting that table to two or more.
0
 
EYoungAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
How do you update the database?

Always try to use INSERT, UPDATE and DELETE SQL statement.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
EYoungAuthor Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
Do some benchmark using the INSERT, UPDATE and DELETE SQL statements.
0
 
EYoungAuthor Commented:
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
 
hongjunCommented:
>>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
 
Richie_SimonettiIT OperationsCommented:
Sorry, i just did read until "Any suggestions?"
0
 
inthedarkCommented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
interesting..
0
 
Éric MoreauSenior .Net ConsultantCommented:
<Summary: OpenTable Addnew/Update is about 50 times faster than SQL Insert Into statements.>

Really strange .
0
 
jrspanoCommented:
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
 
inthedarkCommented:
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
 
inthedarkCommented:
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
 
EYoungAuthor Commented:
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
 
inthedarkCommented:
It would be faster to use:

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

Open table works a lot faster than dynaset.
0
 
EYoungAuthor Commented:
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
 
inthedarkCommented:
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
 
EYoungAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 5
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now