JeffPalmer

I wrote a Delphi (2009) application that reads a text file, processes some information and then saves it out to an Access 2003 mdb file. The program ran fine until the database grew over about 950k rows. I then started getting a strange error that the Microsoft Access Database Driver was unknown. After some digging, I found that the application had been caching the entire database into memory, and that the real issue was that the database could no longer be cached due to it's size.

I changed the cursorlocation from clUseClient to clUseServer. That solved the memory issue and the program functions again, but at an incredible performance hit. It had been processing as many as 2500 items per minute. Now it's 3! At that rate, it would take around 50 hours to process the daily text file.

I narrowed the problem to a single line of code:

with ADOTable1 do
begin
{Check the DB for the ORD_ID and see if it is already there}
if locate('ORD_ID',ColumnData[4],[])
then

The locate function is the problem. With nearly a million rows in the table, it takes close to 30 seconds to search that column for a value. Some of the data in the input text file is duplicate and some is updated. The program looks for the ORD_ID number to see if this is a duplicate entry or not. The ORD_ID column is a key index column in the Access table (the only one). I did some digging and found that using seek instead of locate may solve my problem, but when I try to use seek, I get an error that the function is unsupported in the database.

The Access table needs to eventually hold a little under 3 million rows. Before you ask, yes, I understand I am pushing Access, but without going into detail, it is required because it solves an unrelated problem, and is going to be replaced in about 18 months with a real client/server database system.

The bottom line is that I need to get the program to process the text file (which contains around 110,000 rows of data, but only about 1,100 of actual new data and less than 10% of the rest may be an update to an existing row) in under 2 hours. The actual Access MDB file is currently under 300megs for 1.2 million rows; far below the Access table limit of 2GB.

I really could use some help on this one! Any help is greatly appreciated!

BitFarmer

You just need to create an index in the database using this row (don't know how to do it in Access, but should be difficult), so, looking for a particular value can drop from 30s to may be 0.01s

If this column value is unique among lines (not 2 lines have the same value), then you can add the UNIQUE flag when creating the index, it will make it even faster and avoid adding a second line qith a used value.

JeffPalmer

Thanks for the reply. I had already flagged the column as unique and as the primary index in Access when I designed the table.

The issue turned out to be that I was using TADOTable to access the database. This caused Delphi to cache the entire DB into memory. This wasn't a problem until I ran out of actual ram and started hitting swap memory, which ground the thing down to a very slow crawl.

The solution was to use TADOQuery instead. It didn't have quite the performance using TADOTable did when the table was small, but very acceptable performance, regardless of the size.

BitFarmer

I see, I am actually suffering from something similar but even worst: In FreeIB -we still use it, a "enhaced" version of our own- is the same: Tables are cached, queries are not, but with 2 differences:

1) Cache is made in contigous memory for all the table, so never reach the memory limit (at about 45MB it crashes).

2) Queries use a slighty different TColumn class, so replacing tables with queries is not straight forward.

Some day we will have to change components, but our app is so so big it scare us a bit!