Tech Support Forum - View Single Post

JeffPalmer · 06-17-2009, 11:31 AM

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!

06-17-2009, 11:31 AM	#1 (permalink)
JeffPalmer Registered User Join Date: Jun 2009 Location: Los Angeles Posts: 2 OS: XP	Delphi/Access Out of Memory 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!