Solved

Help ! App. slow with MSJet in Network

Posted on 1998-04-09
15
220 Views
Last Modified: 2008-02-01
Hello !

I have a VB3 App. running in a network and mostley the users have to restart the workstations and sometimes the server to make the App. to run again.
The App. is developed in VB3 and I use Access 2.0 as database.
The App. and database is located on the server and the clients just have a shortcut to the exe and nessesary files in there \System-folder.
When I access a table i always close it and Set it to Nothing so even with the form´s when I unload them.
Sometimes it can run so slow that it takes some seconds to make a letter appear in a textbox after pressing a key.
I can´t see why my App. shuld make the system getting slow, but my customer thinks it´s something wrong whith it.

What can be the problem ???

/Janne SWEDEN
0
Comment
Question by:jannea
  • 8
  • 6
15 Comments
 
LVL 1

Expert Comment

by:ggnabasik
Comment Utility
Is your App stored on the network ?  If so, loading all the DLL's from the network will be slow.  What kind of computer are the clients ?  What is the server ?  Memory/processor/etc.  What kind of network ? Is there any code on the textbox which is taking a while to show the keystroke ?  Are you using BOUND controls ?  

We run a very similar app with no problems.  
0
 
LVL 7

Accepted Solution

by:
kamall earned 100 total points
Comment Utility
Please provide me answers to the following questions:
1. What type on network do they have? Microsoft, Novell,...
2. What is the server OS and the clients OS?
3. What machines are used? CPU, RAM, speed,...
4. How many clients are ther on the net?
5. How do you access your database? Are you opening an entire table or just queries?
6. Does your application makes continuous (very frequent) hits to the database? (if you can figure it out)
Regards
0
 
LVL 7

Expert Comment

by:kamall
Comment Utility
Sorry ggnabasik....
Your comment wasn't there when I edited my answer.
We almost made the same questions to jannea.
0
 
LVL 1

Author Comment

by:jannea
Comment Utility
ggnabasik...

Is your App stored on the network ?
/On the server.  
If so, loading all the DLL's from the network will be slow.
/The DLL´s are on the clients.  
What kind of computer are the clients.....
/See Kamall´s msg. 
Is there any code on the textbox which is taking a while to show the keystroke ?
/No  
Are you using BOUND controls ? 
/No
0
 
LVL 1

Author Comment

by:jannea
Comment Utility
kamall...

1. Microsoft
2. Server NT4 and the clients WIN95.
3. P90-P200, 16-64 Mb
4. Clients 5 to 15.
5. Just open tables.
6. Yes, sometimes to add edit records.
0
 
LVL 7

Expert Comment

by:kamall
Comment Utility
jannea,

1. From your comment I can see an efficient and powerful network. I assume that the NT server has 64MB of RAM, otherwise, you will have a significantly lower performance at the server side (even with 32MB RAM).

2. The number of clients on this network is logical (for acceptable performance) unless there exist a heavy traffic most of the time.

3. What I can figure out is that the problem is caused by two things:
   a. You have both the application and the Jet Database Engine installed on the server. This significantly degrades the performance. It is strongly recommended that you install your application and the Jet Engine on the clients.
   b. You are working with Table-type recordsets rather than Dynaset-type recordsets. Dynasets are much more efficient and faster than Table-type ones. Table-type recordsets use temporary disk files when opened while Dynaset-type recordsets don't. See the following (from the VB help):
"Dynaset-, snapshot-, and forward-only type Recordset objects are stored in local memory. If there isn't enough space in local memory to store the data, the Microsoft Jet database engine saves the additional data to TEMP disk space. If this space is exhausted, a trappable error occurs."

First, I would recommend that you don't make any modifications to your code, just install your application on the clients and see the difference.

I have almost the same network at my company and even with some 486@40MHz clients .I build several multiuser databases, some have up to 30 tables with average of around 50 fields in each table including large text fields. Most of the time, one database  is accessed from all the clients and I don't have any performance problems. My applications were written in either VB3 or VB4. The applications are installed on the clients.

Regards.
0
 
LVL 1

Author Comment

by:jannea
Comment Utility
Thanks Kamall,

I can try to put my exe on each client, but why shold it get so slow just coz of clients charing the exe on the server ? I have other App´s where all of my stuff are on the server withoute notice this kind of problem, but not in this network.
What do you mean with moving Jet Engine on the clients ?
My DB.mdb have to be on the server to get access for all users. And the clients have the Jet-files in there local \System dir.

And I work with Table, Dynaset Snapshot-type and in large changes I use the DB.Execute (SQL). But I can´t see why all this should make the system slow after a while. Can it be possible that I alocate memory on the clients, and if how to realise the memory from this stuff ? Do I have to close the DB and set it to Nothing ?
I guess the problem could be in there own network, what do you think ?
And I havent get any a trappable error like 'Out of memory'.

/Janne
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Expert Comment

by:kamall
Comment Utility
jannea,
What I think is that the Jet Engine is running in multiple instances on the server and is oveloading there.
Installing the entire application on the clients including the Jet Engine will take resources only from the specific client.
This doesn't mean that your DB should be located on the clients. It will be on the server as it is now. You will just need to give its correct path to the EXE as a mapped network drive or as a network path.
Regarding "I have other App´s where all of my stuff are on the
server withoute notice this kind of problem", it depends on your application. Generally, database applictions are of the most 'heavy' applications.
Also I don't understand what you mean by "And I work with Table, Dynaset Snapshot-type". You don't need to work with all these. You can do all your work just with Dynasets.
Regarding your question about allocating memory, as far as I know, you can't.
And of course, you must close your DB from the client when your program terminates there.
What you have reffered to about that the problem could be on their network, it may be only if the network is overloaded due to heavy traffic most of the time, something which I suspect, but it may be the case as I have noticed in some networks where they very frequently copy, move, open, or work with large files or they run other applications that request data from the server most of the time. You may better check this yourself if possible.
About "And I havent get any a trappable error like 'Out of memory'", you will not get such errors because even when the entire server RAM get consumed, the server will swap to disk.
Regards.
0
 
LVL 7

Expert Comment

by:kamall
Comment Utility
jannea,
Also please check if their server has 64MB of RAM. I have noticed a significant difference in performance on my network after increasing the server RAM from 32 to 64MB.
0
 
LVL 1

Author Comment

by:jannea
Comment Utility
Thanks Kamall,

1.) OK, I will try to move the exe´s to the clients this comming week.
2.) With my other App´s i ment simular app´s with Access 2.0 as database.
3.) I just ment that I work with all typs of recordset in my DB-App.
4.) I close my DB when the app. terminates, what I ment was in code after accessing a table.
5.) I think their server has 64MB of RAM, but I´ll will check that to.

I´ll be back when i have try this...
0
 
LVL 7

Expert Comment

by:kamall
Comment Utility
jannea,
Don't move the EXE to the clients. Just re-install your application including the Jet Database Engine on each client using the setup utility. This way you can be certain that the Jet Engine is registered on the client will run on it.
Hope to hear good news.
Good luck.
0
 
LVL 7

Expert Comment

by:kamall
Comment Utility
jannea,
I also found these important information:

=====================================================================
Part 5: Minimizing Keyset Overhead When Working with Large Recordsets
=====================================================================

When a query selects a large number of records from the database, Visual
Basic only fetches the first row of that Dynaset or Snapshot and places the
key to refetch that row in memory.  Once a record is fetched or visited, it
becomes a member of the recordset.  As you "visit" additional rows of the
recordset, the keys are stored in workstation memory (in a temporary table),
and in the case of Snapshots, so is the data.  If you move back to previously
fetched rows, Visual Basic refetches the rows using the old key fetched from
the temporary key table.  

- If the database record is no longer there, you will get a trappable error.  
- If the record has changed, the new information is fetched from the
  database.

As you move further and further into the recordset, more and more memory is
taken up storing the keys.  Eventually, Visual Basic will begin saving the
keyset on disk.  If this happens, space is used on disk in the directory
specified by your \TEMP environment variable.  Generally, you won't see a
performance degradation until Visual Basic has to swap the keyset temporary
table to disk.  If you run out of disk space because Visual Basic has
exhausted the space in your \TEMP directory, you will get a trappable error.

Moving to the end of the Dynaset or Snapshot does at least two things:
First, it forces Visual Basic to visit all of the records in your recordset.
Hence, all keys will be saved on the workstation.  If this is a few hundred
rows, this may not take long or take up more space than the workstation can
handle.  

However, for larger recordsets, a MoveLast operation may be far more than
the workstation can save.  When working with Snapshots, not only are the
keys fetched, but the data for all records is also brought into local
memory. Generally, you should avoid operations that fetch more rows than
your user or workstation can deal with.  Operations that must touch each
record in a recordset may best be performed with an action query that
consumes less system resources.  In any case, your performance will not be
severely degraded, either as you move forward until you have to swap, or
hardly at all if you move backwards in the recordset -- even to the first
record.

Note
----
The Dynaset or Snapshot membership is not set until the record is actually
fetched for the first time.  Since this can take from seconds to days
depending on how fast you fetch the records (moving down through the
recordset with MoveNext or with MoveLast), no Dynaset or Snapshot is really a
frozen subset of the data at a point in time.  The only way to ensure that
no changes are made while the recordset is built is to get exclusive access
to the table or database before fetching -- which essentially locks out all
other users until the recordset (or database) is closed.

=====================================================
Part 6: Performance tips for Visual Basic Data Access  
=====================================================

The following tips are suggested for operations involving more than just a
few records to increase the overall performance of your system.

1) When working with large recordsets (Dynasets or Snapshots), do not use
   the MoveLast method unless absolutely necessary.

   Moving to the end of a recordset requires Visual Basic to load all keys
   for the recordset into memory. In the case of Snapshots, not only are the
   keys loaded into memory, but the data is also brought into workstation
   memory.  If temporary memory space is exhausted, Visual Basic may be
   forced to swap this temporary cache to disk.  In this case, Visual Basic
   will use space as addressed by the \TEMP environment variable.  Once this
   space is exhausted, your application will trigger a trappable error.

2) When you want to access external tables fast, attach the table to your
   database instead of using the IN clause in a SQL statement or addressing
   the table directly.  
   
   When Visual Basic needs to access your external table, all linkage
   information is resolved when the database is opened and does not have to
   be re-established and initialized each time the data is accessed (for  
   example, with non-attached tables.

3) For reasonably small recordsets, especially where you do not intend
   to write to the recordset, use Snapshots instead of Dynasets.

   If possible, set the READONLY flag on the data control or DB_READONLY
   option when opening databases.  This will permit Visual Basic to bypass
   significant logic to handle multi-user read-write access to your tables.

4) In cases where you are working with external ODBC databases, you will
   achieve maximum possible speed if you use SQL Passthrough instead of
   attaching or direct access that involves the Visual Basic database engine.

(Note: These info. can be found in the file PERFORM.TXT under your VB3 directory.)

Regards
0
 
LVL 1

Author Comment

by:jannea
Comment Utility
Sorry Kamall,

I haven´t been abled to do the test with the exe´s on clients this week coz of some other people are running other tests to see if they have somthing wrong in their network.
Aboute your comments:

C1) Don't move the EXE to the clients. Just re-install your application including the Jet Database Engine on each client using the setup utility. This way you can be certain that the Jet Engine is registered on the client will run on it.

1.) "Registered" ? I have a 16-bit application, and as far as I know I don´t need to register any components in a 16-bit app. What do you meen whith that ?

C2) What I think is that the Jet Engine is running in multiple instances on the server and is oveloading there.

2.) How could this be possible ? I don´t have any dll´s or other files for Jet on the server. All the needed files to make the application run is in the clients \System directory. So why should I re-install the app. on the clients this shouldent make any diffres, OR ?

I did find a posible reason to my problem in 'VB Programmer's Journal (Nov 96, page 50):
*********************************
ACCESS 1.X/2.0 LOSES SYSTEM MEMOY

Problem:
Access 1.x and 2.0 when you add records to them. A leak of aboute 600 bytes occures after adding aboute 50 to 60 records. An application using Access 1.x or 2.0 will eventually leak all the memory, causing Windows to crash.

Workaround/Solution:
Windows recovers the memory when an application terminates normaly. You need to make your program end and then restart about once a day to recover this lost of memory.
*********************************

This is what my app does some times a day, when the user end and restart the app the slow behaiviour is gone. But in this case I guess I can´t be the only programmer to have this problem. And how to solv this in a more proper way than force the user to restart the App. several times a day ? Any Ideas ?
 
I´ll be back

/Janne

0
 
LVL 7

Expert Comment

by:kamall
Comment Utility
Hi jannea,
Sorry for being away for sometime.
Do you have any news?
Regards
0
 
LVL 1

Author Comment

by:jannea
Comment Utility
Not more than my last Q´s before yuor last comment.

I have also been gone fore a wile and haven´t test thes yet...
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

763 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

11 Experts available now in Live!

Get 1:1 Help Now