cfquery select 400000 records

@plarts,

I want to elaborate on something BKBK said:

MS Access is not as optimized as specialist Relational Database Management Systems like MySQL and SQL Server. Its inefficiencies cost extra memory. That would explain why your system comes to a standstill after swallowng 200MB. So, after increasing the RAM, you should increase the amount of memory allocated to the JVM to a value an order of magnitude higher than 200MB. Let us say, 1024MB.

ColdFusion cannot communicate directly with databases stored in Microsoft Access .MDB format at the Java level (via JDBC drivers); instead, ColdFusion uses a JDBC-ODBC bridge to talk to an ODBC datasource on your server (or workstation).  ODBC is then using the MS Jet database engine to interact with your .MDB file.  So, lots of moving pieces.  And with MS Jet there is no database engine running as a service (like there is with MySQL, MSSQL, PostgreSQL, Oracle, or most other relational database systems).  The MS Jet engine doesn't have the ability to cache execution plans, store recordsets in cache memory, keep an effective transaction log, handle more than a few simultaneous users, or perform many other optimizations that a modern relational database system has.  Therefore, it will be inherently less efficient and performant than a modern relational database.  This is why it is generally considered poor practice to use a Microsoft Access database as the back end of a web application.  You would be better served to migrate that data into one of the previously mentioned database systems (MySQL and PostgreSQL are open-source so you don't have to buy them to use them).

Now onto other issues.  You talk about doing some looping to process batches of records (and I'm assuming some UPDATE queries are involved).  Usually there are better ways to tackle this using more efficient query design (and especially if you can utilize one of those great relational database systems mentioned previously).  Can you describe what you are doing to the records in your table, and why?  We might be able to offer some alternatives if we understand what you need to accomplish.

-Carl V.

Thanks for answer,

This is the first time I work with so many records, so I did not think about it.

Then I did as usual, I loaded the whole table.

And it worked the first time (strange), then I checked one field of all records in one loop.

And did a small process if the data of the field was not correct.

It did work. (with a correct process time)

I had to repeat again, because, I did not check the right field.

And then I had this memory error message.

Then I think about, how it works.

I understood, that it loaded the whole table (datas) in memory.

Then, I only loaded the ID and the wanted field,

And in the loop (witch i do by steps now, startrow, endrow), if the field is not correct, I do a new query and load the full record. (all fileds) to run the small process which needs all fields for a copy of them..

Seems to work,

But very very slow.

I wait, I wait, not sure it is working correctly, I hope.

i started the same job on a Windows 7 with CF9 and a 4Go RAM,

With the new way of processing it takes around 1 hour to process 10 000 records.

I do it step by step in the loop (startrow and endrow)

I wait till the end , to check the full result.

Not easy to see if it is processing well or doing nothing.

On windows 7, I can open the mdb file (dispite of the lock) and see the progress.

On windows XP, I can refresh in Windows Explorer and see the mdb file size increasing.

(XP and 7 do not work the same way)

I let you know on result in the 2 environments.

Thanks again for your attention and advise.

Pierre.