Solved

SQL slowing from a large amount of processing

Posted on 1998-08-17
11
165 Views
Last Modified: 2010-04-30
null
0
Comment
Question by:jeffcameron
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 3

Expert Comment

by:a111a111a111
ID: 1429305
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.

BACKUP YOUR DATA!!!

0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1429306
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!"
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1429307
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.

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 1

Author Comment

by:jeffcameron
ID: 1429308
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
0
 
LVL 3

Expert Comment

by:a111a111a111
ID: 1429309
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.

Info:

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.

0
 
LVL 3

Expert Comment

by:jjmartin
ID: 1429310
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.
0
 
LVL 3

Expert Comment

by:dapperry
ID: 1429311
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
0
 
LVL 1

Expert Comment

by:raygibbins
ID: 1429312
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.
0
 
LVL 1

Author Comment

by:jeffcameron
ID: 1429313
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.
thanks
0
 
LVL 1

Author Comment

by:jeffcameron
ID: 1429314
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
0
 
LVL 6

Accepted Solution

by:
joopv earned 200 total points
ID: 1429315
I see you have placed a post also at this forum.

Check the following registry entry :

Local machine/system/Controlset001/control/session manager/memory manager/LargeSystemCache

If it is not there, create it.  It has a default value of 1.  Set it to 0.

Installation of SP3 sets it back to 1, so recheck the value afterwards.

joopv
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…

791 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