Out of Memory searching an Access table

[JeffPalmer]

I have a Delphi 6 application that connects to a Microsoft Access 2002/2003 database and adds about 1100 rows a day. The program was running fine until there were around 975k rows in the Access table. I now get an error that the Microsoft Access driver is unknown. While running the app inside Delphi, I found that I was getting an out of memory exception (which probably stepped on the Access driver).

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

When locate is called and it searches the database for the value in ColumnData[4], the program runs out of memory near the end of the database, which is currently around 975k rows. If I delete, say 50k rows, the program runs fine again. I have not approached the limit of the Access table (the mdb file is under 400megs with the limit for Access a little under 2G and should handle at least 5-6 million rows).

Is there either a compiler directive or other parameter that can increase the available memory to solve this issue?

If not, is there something else I can use to search the database for that value without using so much memory?

 

[whosrdaddy]

what cursorlocation are you using? is it clUseClient or clUseServer?
in case of client, delphi cache the table locally before attempting the locate.

I don't know what your code is trying to achieve, but you could get better performance if you write a Query to do the task (use TADOquery for this purpose).

I would never use access files THAT large, my n1 rule is that when the db exceeds 100k rows, another more performant solution is needed (access tends to corrupt large databases).
have a look at MySQL server or MS SQL server (express version), both are free alternatives.

my 2ç

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[roo0047]

... n1 rule is that when [Access] db exceeds 100k rows, another more performant solution is needed ...

Second that!

Roo
Delphi Rules!

 

[JeffPalmer]

I just have it connecting to the mdb file via TDataSource and TADOTable, and as you can see from the code, it just calls locate to find the ORD_ID number. If this is causing Delphi to cache the entire table or database for the search, it explains why I run out of memory.

The TADOTable cursor location is set for clUseClient.

What the program does is takes a text file output, does a little crunching on it and then populates an Access table. The locate function is just to see if the unique ORD_ID number is already in the database (and then decide if this is an update or not) or add the row if it doesn't exist.

At this point, I am more concerned with getting the program operational again and worry about performance tuning later. I'll try setting the Cursor Location to clUseServer and test that, but any further helpful hints are appreciated!

Yes, Access is an ugly alternative, but it's supposed to be just a temporary solution as a real database solution is due in about 18 months. If you think Access is a poor choice, they were using Excel to hold the data (what little of it they could) before I came along!

 

[JeffPalmer]

I guess I was wrong about performance. Changing the CursorLocation to clUseServer did solve the memory problem. Unfortunately, now it only processes about 3 records a minute (before the database grew too big, it was running around 500 records a minute)!

The rest of the code runs at an acceptable speed, it's just the initial full table scan for the ORD_ID to determine if it exists or not. ORD_ID is set as a key field in the Access table and is unique. Using locate places the cursor at the found record, or the end if not found, which worked well up to now. If I use TADOQuery, do I need to create a full SQL statement? If so, will it leave the cursor at the found record, or will I need to change all the code to SQL queries to add/update records?

 

[whosrdaddy]

Access is not a poor choice, it is just not up to the task for managing a database that size.

If I were you I would implement the MS SQL server solution NOW, from a code point of view, the ONLY thing that needs to be changed is the TADOConnection.Connectionstring property, the rest of your code will remain the same.

You can use the upsize wizard from ms access to move your tables to the MS SQL server.

see if that gives better performance (your mileage may vary).

if performance really stays bad, then yes, rewrite the application using queries only (that's what I always do), but this involves a lot more work.

It's up to you...


/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[JeffPalmer]

Due to multiple constraints, implementing any other solution is not possible. As I mentioned, this was a stop-gap to get them off of Excel in the interim of getting their client/server based system in about 18 months.

Is there nothing I can do to improve performance with what I have now? I don't mind if I've lost some speed, but 250x slower is unacceptable.

Help me, Obi-wan Kenobi -er Daddy. You're my only hope.

 

[whosrdaddy]

do you really need the 1 million rows that are in that table?

if not, you can maybe consider a logic where you move the older records to an "archive" mdb file to keep performance up.

this all really depends on what you are doing:

- do you have gui components that show table contents?
- is it only used for updating?
- do you have reporting?

try to give me a more complete picture and maybe I can give a more sensible answer...

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[JeffPalmer]

They need to have historical data that goes back to 2004 to track about 2500 clients. I can probably convince them to go back to just 2006 (what currently is in the database) which is about 1.3 million records. The DB itself isn't that big, less than 300meg.

What the program does is take a generated daily report (that has the last 3 months of data, which may or may not include updates) that is saved as a text file. The Delphi program reads the text file, pulls some fields, does a little processing and then just dumps them into an Access database. There is a unique ORD_ID number which must be checked against the new data. If it does not exist, the row is just added to the database (about 1100 a day). If the ORD_ID already exists in the Access DB, then a second date field is compared to see if this is an update or not. If the date is the same or older, the "new" record is skipped. If the date is newer, the row is updated. Since the report consists of data for the last 90 days, there are a lot of entries already in the DB and can be skipped. The daily file runs around 100,000 items, but only around 1100 are actual updates or adds. The reason for the 90 day window is that some data is incomplete and not fully updated for sometimes a month or more, which is why the date has to be checked if the ORD_ID already exists.

The table is NOT displayed during processing. This program is only designed to import the text data into the DB. They they write their own Excel macros to read the Access data to produce some graphs and reports. BTW, this is all under Office 2003. And yes, they are aware of the limitations in Excel.

The problem originally started whey the database grew past about 950,000 rows or so. At that point the system just plain ran out of memory to hold the entire DB. Setting the clUseServer solved the problem, but instead of processing 500 rows of new data a minute, it's doing 3.

The actual problem is the search for the ORD_ID number. It is stored as a text field (usually, the number is only 8 characters), but when a search is done, it scans the column through the entire DB using locate. Again, the code looks like this:

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

I mis-calculated on the original speed. It was actually doing over 2000 row checks a minute (yes, it did slow down a bit as the DB got larger). I don't need to have that kind of speed, but I need to at least break 800. As I mentioned, using locate with clUseServer is yielding 3 (I timed it). At that rate, it would take around 50 hours to process that 110,000 import file. I have to have it under 2 hours.

The funny thing is that Access (2003) is able to load the DB with 1.3 million lines in just a few seconds, display the whole thing with no problem scrolling up and down, and can search for an ORD_ID in a blink.

Let me know if you need more info.

And you can't imagine how thankful I am to you for looking at this!

 

[whosrdaddy]

Ok, I will try to simulate your case!


but need some infos :
- can you take a screenshot of the table design? (upload it to imageshack or something like that)
- can you show the connectionstring property of your TADOConnection? (look in the .dfm file)

/Daddy



-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[JeffPalmer]

I created a .UDL file for the connection information to be read from. The connection string in the .UDL file is:

DBQ=C:\Cedars\Procedure Tracking.mdb;DefaultDir=C:\Cedars;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=D:\Program Files\Common Files\ODBC\Data Sources\Procedure Tracking.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

As for the table design, it is extremely simple:

14 columns total - all simple text
1 index column (ORD_ID), no dups and not automatic

Otherwise, all field sizes are the default of 50, columns are allowed zero length, Unicode compression YES, IME Mode is NO CONTROL, IME Sentence mode is NONE.

Let me know if you still need a screen shot, but that is really the entire table design.

 

[JeffPalmer]

After stepping through the code, the issue is definitely the locate function.

After some additional research, I found that using seek instead of locate may solve the issue, but when I try that, I get an error that it is unsupported.

As mentioned, the column I'm searching through is a key index column in the Access table. Does anyone have any further suggestions on speeding a search up?

 

[fajac]

You might try to use a TADOQuery instead of a TADOTable. Try a SQL-string like
'SELECT * FROM [TableName] WHERE ORD_ID=...' and see if the query returns a record.

 

[JeffPalmer]

I'm missing something with the ADOQuery. I keep getting a parameter missing error when I activate the query.

ADOQuery1.sql.Clear;
ADOQuery1.sql.Add('Select * from Procedures where ORD_ID="6904409"');
ADOQuery1.active := true;

I do not get an error if I leave off the where clause.

Should something be placed in the ADOQuery1.parameters property?

As I mentioned, I tried to use SEEK with the TADODataSet, but I get an error: current provider does not support the necessary interfaces for sorting or filtering

If I set the CursorLocation to clUseClient, I can use SEEK, but I run out of memory trying to cache the entire DB, but clUseServer gives me the not supported error.

 

[roo0047]

http://delphi.about.com/od/database/l/aa050101a.htm

Roo
Delphi Rules!

 

[JeffPalmer]

I want to thank everyone for your invaluable help!

Once I understood how to pass parameters to the SQL statement, it was easy to change the code references to TADOQuery from TADOTable (Roo, that link was definitely helpful! ).

THANK YOU THANK YOU THANK YOU ALL!