Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Large MDB Crawling Now. HELP!!!

Posted on 1999-07-12
17
Medium Priority
?
400 Views
Last Modified: 2013-12-05
Hi Experts

We have this Contact Management application running in our office (captures sales, product details sold, quotations, orders, customer analyses, inventory onhand etc etc...)

It runs on an NT Server 10 users (specs are 64MB, UDMA-8GB HDD, Pentium 266 CPU, 100Mbs hub/cables). The front end on all our Win 95 & 98 clients is a 19-MB sized MDE file (with about 85 forms, 300 queries and tons of module-codes) and links to the server which contains a 45MB MDB database (just tables only - about 50 of them).

The system now crawls when only 3 users are logging on. Is there anything I can do to improve performance? Or is this method of setting up wrong? Any better suggestions so we can contine using the existing setup?

Thanks a million!
0
Comment
Question by:james2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +7
17 Comments
 
LVL 12

Expert Comment

by:Trygve
ID: 1999751
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"

0
 
LVL 7

Expert Comment

by:Dedushka
ID: 1999752
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
0
 

Author Comment

by:james2000
ID: 1999753
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

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 12

Expert Comment

by:Trygve
ID: 1999754
9) All you tables are linked using relationships? Well, I guess that does depend on the application at hand.
0
 

Author Comment

by:james2000
ID: 1999755
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

0
 
LVL 3

Expert Comment

by:IvanD
ID: 1999756
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)
0
 
LVL 1

Expert Comment

by:Ghis68
ID: 1999757
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.
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 1999758
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
0
 
LVL 1

Expert Comment

by:cvmilitaru
ID: 1999759
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  
0
 

Author Comment

by:james2000
ID: 1999760
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
0
 
LVL 1

Expert Comment

by:dclary
ID: 1999761
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?


0
 
LVL 1

Accepted Solution

by:
andrejaa earned 800 total points
ID: 1999762
Hello James ,

I faced long time ago with same problem . Part of my DB was too slow when 3 or more users attempt to add new record or edit existing record.

I solved that problem by alocating / copying records for editing onto local MDE.

You have to try following :

Your original table is : MyTable
Make on MDE one/more Local Table : LMyTable ( which is identical to MyTable)

In LMyTable I add one field (I called it ActionType) to control what kind of action users will take , f.e.:

N - new record
E - edit record
D - delete record

Then , I have to make 3 types of queries for ADDING DATA / EDITING DATA / DELETING DATA

And , all work going like this :

EDITING EXISTING RECORD
1. Empty all records from LMyTable
2. Copy into LMyTable from MyTable row which you wanna edit
3. Edit data , or not, but into field ActionType enter value E (or what ever you want)
4. When you closing your form , activate query for UPDATE same row in MyTable with data from LMyTable

ADDING RECORD
1. Empty all records from LMyTable
2. ADD NEW data , into field ActionType enter value N (or what ever you want)
3. When you closing your form , activate query for ADD new row in MyTable with data from LMyTable

Also , you can write procedure for controling action type ( hmm , not only for edit/delete/add ..maybe for archive some data ??)

I apply same method on group of tables (25 tables) and it work fine because ALL work will be done on USER MACHINE .....only updates from local machine will increase network trafic.

If you need more details , dont hesitate to Email me .
0
 
LVL 1

Expert Comment

by:andrejaa
ID: 1999763
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

0
 
LVL 1

Expert Comment

by:cvmilitaru
ID: 1999764
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        
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 1999765
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
0
 

Author Comment

by:james2000
ID: 1999766
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!

0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6873053
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.

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

715 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