Large MDB Crawling Now. HELP!!!

1) If possible copy the application MDB to your users local harddrives. This save a lot of network traffic.
2) Compact and repair your MDBs both data and application on a regular basis. This rebuilds indexes and removes temporary objects and "lost" records.
3) Check for any anti-virus program that might be running on the server or on your users machines. Some of these can slow down the performance big time.
4) If you user Outlook, make sure that it does not log your opening and closing of MDBs. This is time consuming and makes a lot of log...
5) Compile and save all modules every time you make changes to the application.
6) Consider making indexes on fields that are used for grouping and filtering purposes in your form and reports. This slows down input a tiny bit, but saves execution time a lot.
7) Make sure that there plenty of free space on the servers harddrive (as on your users harddrives) so that swapfiles have a nice large "playground".
8) Consider upgrading your server. I would at least add more RAM. 64MB is little for a NT Server. It is little for a NT client too. The CPU could also do with an upgrade but RAM is the no.1 target. (If the HD is big enough)
9) Consider splitting your data MDB into several smaller files.

There are probably more good suggestions too. These were the ones I could find "on the top of my head"

Hi james.
To improve perfomance you need to optimize you databases (i.e. redesign) or optimize MS Jet. I don't think, that you want to redesign you DB's, so for optimization MS Jet, try
http://www.microsoft.com/accessdev/articles/movs303.htm
Here you'll find Kevin Kollins's article "Jet Perfomance Tuning and Optimization".
Hope it will be usefull,
Dedushka

Dear Trygve

Thanks your valuable input. Most of these we seemed to have done and the slow performance is still a problem. Regarding your comments:

1) The application MDE is now in all the client machines, operating by linked tables concept to the database in the server.

2)We compact the MDB everyday, but so far so "bad".

3)Only Norton Anti-Virus is installed and runs only during startup at stations. No loaded "shield" is running in the background.

4)No MS Outlook is installed.

5)Compile and save all modules is done everytime we make changes to the application.

6)Yes, indexes were added to many places.

7)Spare disk space in my hard drive = 1.9GB so swap file requirement don't seem the cause of the problem.

8)I'll upgrade the server RAM to 192MB and see if it helps.
 
9)My problem in splitting the MDB are the relationships... so splitting is not really an option.

B/regards
James

9) All you tables are linked using relationships? Well, I guess that does depend on the application at hand.

Dear Trygve

Thanks your valuable input. Most of these we seemed to have done and the slow performance is still a problem. Regarding your comments:

1) The application MDE is now in all the client machines, operating by linked tables concept to the database in the server.

2)We compact the MDB everyday, but so far so "bad".

3)Only Norton Anti-Virus is installed and runs only during startup at stations. No loaded "shield" is running in the background.

4)No MS Outlook is installed.

5)Compile and save all modules is done everytime we make changes to the application.

6)Yes, indexes were added to many places.

7)Spare disk space in my hard drive = 1.9GB so swap file requirement don't seem the cause of the problem.

8)I'll upgrade the server RAM to 192MB and see if it helps.
 
9)My problem in splitting the MDB are the relationships... so splitting is not really an option.

B/regards
James

Another performance issue : If you have large tables (e.g. 3000 companies) and you use comboboxes, opening this forms can take forever. Try changing to lookupforms (I can send you an example if wanted).

Do you reference a CurrentDB() a lot in code ? If so, replace this by DBEngine(0)(0). It does the same, except for refreshing all collections in your mdb. So on large mdb's it can speed up thing significantly...

What you can do, mail me a small 'very slow' portion of your application, and I'll see what I can do to speed it up (id@thinknology.be)

This is only a suggestion that work for me.

To resolve your problem split you mde in fiew database  (module) where you group commun information in one module (database).  Create a module Main Menu that you launch from the icon on the PC. After a user select a module (database) open the database with a code like this.

Private Sub btAccess_Click()
On Error GoTo Err_btAccess_Click

Dim X As Integer


UpdateVersion 'check and Update the file is request
DoCmd.Echo False, "Login " & Me![lstDatabase]


'Procedure use to open an other database

    Dim stAppName As String

    If IsNull(Me![lstDatabase]) Then
        MsgBox "Select database"
        Exit Sub
    End If

    DoCmd.Hourglass True

    stAppName = """" & MsAccess & """ " & WorkingDir & Forms![frmSelectApplication]![lstDatabase].Column(6) & "\" & Forms![frmSelectApplication]![lstDatabase].Column(1)   'Database
    stAppName = stAppName & " /user """ & CurrentUser() & """" ' UserName
    stAppName = stAppName & " /pwd """ & DLookup("[Password]", "qryUserList", "[badge] = currentuser()") & """"
    stAppName = stAppName & IIf(Me![lstDatabase].Column(2), " /ro", "")
 ' remove tempory until have tool kit stAppName = stAppName & "/runtime"
    stAppName = stAppName & "/wrkgrp " & wrkgAccess
    stAppName = stAppName & " /NoStartup"
   
  'MsgBox stAppName
  Call Shell(stAppName, 1)
  DoCmd.Quit

Exit_btAccess_Click:
    DoCmd.Hourglass False
    Exit Sub

Err_btAccess_Click:
    MsgBox Err.Description
    Resume Exit_btAccess_Click
   
End Sub


If you read the code you see I quit the main Menu to have only one Ms Access open.  When I close the module, I open the main menu with this procedure on close of the form I use for the menu of the current module

Public Function fctOpen_MainMenuClick(stDatabaseName As String)
On Error GoTo Err_fctOpen_MainMenuClick

'Owner :
'Date  :    3 may 1999

'Parameter:
'   stDatabaseName: Name of the database you want open

'Description:   Use to close the current database an open another database = stDatabaseName
'Need:  You need to attache the table UsysUser from the MainMenu
'need: to create a query qryUserList and make the query run by owner
'This module will be include in all database application

Dim X As Integer
Dim stAppName As String

DoCmd.Hourglass True

    If IsNull(stDatabaseName) Then
        MsgBox "Select database"
        Exit Function
    End If

    stAppName = """" & MsAccess & """ " & stDatabaseName   'Database
    stAppName = stAppName & " /user """ & CurrentUser() & """" ' UserName
    stAppName = stAppName & " /pwd """ & DLookup("[Password]", "qryUserList", "[badge] = currentuser()") & """"
 ' remove tempory until have tool kit  stAppName = stAppName & "/runtime"
    stAppName = stAppName & "/wrkgrp " & wrkgAccess
    stAppName = stAppName & " /NoStartup"
   

  Call Shell(stAppName, 1)
  DoCmd.Quit

Exit_fctOpen_MainMenuClick:
    DoCmd.Hourglass False
    Exit Function

Err_fctOpen_MainMenuClick:
    MsgBox Err.Description
    Resume Exit_fctOpen_MainMenuClick
   
End Function



The code I send to you will not work with out some modification but it can give you new idea.

With lots of users entering lots of data your indexes can actually slow things down.  If you have any indexes in your tables that you can live without, try taking them out.

Wes

You haven't told exactly what is going bad. When you have problems?
a. When you open the forms?
b. When you save the data into database
c When you execute queries and create reports?
d etc.
The solutions presented here are excelent. But, there are not universal solutions. Take the example of using indexes: if you have problems when you execute queries, they are good (Trygve is right), but if you have 20 indexes for one table, you will have problems when a lots of users entering data (wesleystewart is also right!). So, if you can be more specific, the answers will be also more acurate.
cvm  

Hi Experts

Thanks so far for all the inputs, and I haven't found the right combination that will fix my problem. I do have a lot of combo boxes and some tables have at leat 15 indexes. I'm trying out wesleysteward & IvanD's suggestions now. And also reviewing some of the queries within my app.

The problem is when the app runs with 3 users logging in, the process of saving a Sales Order transaction (updates to 4 different tables) takes about 10-15 seconds. When I try to search for an existing record thru Filter, it returns a result only after 20 seconds. And when the report is being called, some of them could take up to 2-3 minutes before I see the preview screen.

B/regards
James

Just curious, but how many records in each table? A 45 Meg MDB with 50 tables in it must have some very large tables in it. Perhaps it's something that could be solved with more judicious selection of data being accessed at any given time... I mean, if the average table is .9 meg in size, that will take time to load over a network, regardless of jet optimization.... right?

Hi again ,

just in addition ...another tip for speeding up DB :

if you have CODE TABLES ( tables used as source tables for combo boxes ) try to keep them in separate .MDB ( f.e. CODE.MDB) and at the moment when users attempt to start their .MDE's COPY CODE.MDB on their machines!!!
It will work if your database design doesn't allow users to change code tables frequently !!!!

This comment is related to your info about numbers of combo boxes on your forms .

Regards , andrejaa

james2000,
I have worked for a very similar application (mdb of date=80 Mb and 5 users). I have the same problems. There are the solutions that I have used. Many of them were already decribed. And maybe you can't to use some of them, because of the request of your application (time of developpement, for example):
1. I have removed the most part of combo-boxes (it is a must for the combo with more that 500 lines and for the combo in sub-formes). I used text box and a combination of
keys for opening a generator form (a query form for generating the value).
2. I have made a local copy for all the table that are not changing frequently and I have used these tables. The user can load these table when opens the application or when he/she wants (I put that in the options of my applications). If you use refrerential integrity there is no danger. For example, if one record is deleted from the data mdb, but you have not yet load the local copy, an error will be generated (you are trying to use a foreign key that doesn't exist).All you have to do is to cover this error.And this is not happend frequently.
3. I remove some on-line verifications. For example, in my first version, when I saved an invoice I used to make a lot of checks. I kept only the most important. In fact, I removed the very complicated verifications. At the end of month, I genereted a report of all invoice that broke the complicated rules.
4.I used some combo-boxes, but I used just one form for a type of combo-box. I load this form at the starting of the application an I hide this form. Then, in all place I used text boxes. When the user press F2, I make visible this hidden form and I transfere the value from combo into the main form. I close this form just at the ending of the application. The form with combo has a Refresh button, for refreshing the content of combo.
5. For all my form lists (for example list of invoice), I used a default filter (for example invoice for this month). The user can apply other criterias, but the default filter restrict very much the contens of list. The user has to remove explicity the default filter.
6. I create several mdb of code. In fact I have 7 distinct modules (mdb files of code), each has between 3 and 7 Mo.
The modules are separeted according the client request (the management module, the salary module, etc).
7. 15 indexes for an table is to much!!! Remove some of indexes. Keep only the field where you are doing selection usually (in clause Where, etc). Keep the foreign key that are usaualy used in joins. AND REMOVE ALL THE INDEXES FOR THE NON-SELECTIVE FIELDS (no matters if it is used usually). If an field has 5 distinct values for 1000 enreg., it is not good for index!!
You can make a lot of test for adding and removing the indexes, without modifing the structure. Go for it.

And finally, keep in mind that. Your application has two parts;On-line and off-line. On-line=enetring the date. Off-line=analising the date (all the report, etc).
The on-line part must be fast. For the off-line, the user can wait. (For a report that is made for each month, 2 min. is not very much).  

I hope you can use a part of my advices.
cvm        

When the database takes a long time to save a record to tables there's a good chance you have an index issue.  Obviously that is only one part of a well-tuned database but If you have 15+ indexes on multiple tables your database is working hard to update all those indexes with each record save.

Wes

Thanks Experts.... for all the help provided to solve this problem of ours. After evaluating the various answers, we felt that the best options came from andrejaa and cvmilitaru.

Therefore I'll award the points for these 2 experts... 200 points each. Thanks a million!

GREETINGS!

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange