We help IT Professionals succeed at work.

SQL slowing from a large amount of processing

jeffcameron asked
Medium Priority
Last Modified: 2010-04-30
Watch Question

You may have a problem with free mem. so try download a program that free up some mem.

download FREEMEM form www.meikel.com/freemem/  and try to run it.


MS Access and the earlier DAO drivers were notorious memory leakers.  Software toys, really, that did not stand up to real volume database useage.  You will be better served if you have MS Access 97 installed, and VB5 with the latest patches.  Even so, you need to run a system monitor on your application, because many third party controls were also bugged by memory leaks.  Seems to me that these products weren't really "released" they "escaped!"

Couple of other hints:

If using ODBC, note that you have an option on the DSN setup to increase the available working memory

Access 95 DAO/Jet had an annoying bug of creating prepared statements that never got reused or released.  You can go through the collection and periodically relaese them in your own code.  I had to do this with earlier releases just to keep the app from dying.

As you add more records, access will slow down, particularly if it has relationships to maintain, and particularly if they aren't indexed.


the amount of available memory does not drop below two hundred megs free according to the system monitor, the computer has three hunded + total.  I have MS Access 97 installed and the VB5.0 Enterprise Edition.  Explain what the latest patches do.  I am not positive if they are installed or not
The newest patches is SP3 form microsoft.
you can find it at: http://www.microsoft.com/msdownload/vs97sp/vb.asp

After you install SP3 use the project / references to see the patches.


Service Pack 1 -- Summary of Issues
 Visual Basic
The service pack addresses issues with dbgrid32.ocx, msrdc20.ocx, and msrdo20.dll related to data-binding and other issues. Also included are fixes for the incompatibility issues with comctl32.ocx. There is an update to the VB5 Setup Kit and some of the .cab files for the ActiveX component download. These .cab files are not installed
by the Visual Studio Service Pack installation program but are included on the Service Pack CD.

Service Pack 2 Updates and Bug Fixes
General Bug Fixes: Fixes a bug preventing the download of samples with certain versions of Microsoft Internet Explorer. It also fixes a search results-related performance problem in which Developer Studio sometimes uses 100 percent of idle time.
 Visual Basic: Addresses problems with the development environment, the Printer object, the Internet Transfer control, and many of the other controls and libraries that ship with Visual Basic 5.0. This Service Pack also contains support for creating apartment-model, thread-safe ActiveX Controls and DLLs for use in IE 4.0, ActiveX DLLs and executables that show forms on multiple threads, and multithreaded components that use the UserConnection designer

Visual Studio 97 Service Pack 3

Summary of Bug Fixes
 Visual Basic
Addresses compatibility issues with the Internet Transfer, Data Bound Grid, Data Bound List, and Data
Bound Combo controls. The Service Pack also fixes a problem where Visual Basic compiles incorrect type information.

The problem may be in the use of Access as your backend.  Access was meant for small databases.  Your talking about running millions of records through Access, and Access was just not meant for that kind of volume.  It would benefit you to purchase SQL Server and use that as a backend, as it was designed for high volume.

I suggest you dump Microsoft altogether, if you are in need of a high volume DBMS.  Go with Sybase or Oracle (I'm fond of the former for ease of use and cost).

:) D Perry
I was had a similar problem when read text files into a very large Access database. I you find you hard disk is working very hard, try using something Norton NChache (or something like that), I found that speeded my App up immensely.


I am doing the same process on my PC rather than the server and it does not appear to occur.  My PC has much less memory and a slower hard disk.  The major difference is the server has WinNT and my computer has Win95.  Could this cause the problem.


I have installed and ran it on 6 machines two high end severs as desrcibed above, 2 pcs one with WinNt Workstation and one with Win95, and two lower end servers(first server:2-133 Mhz,1-233 Mhz) older machines no fancy hardware.  The software ran fine on the latter 4 but the two monster servers 10 - 15k brand new.  The program begins to slow.  
I am currently using VB5, MS access 97, jet 3.5
network specialist
Unlock this solution and get a sample of our free trial.
(No credit card required)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.