Delphi 7 using RAM for database

Question

I have an existing database app written in D7 with apollo databases.

The client has given me a fast desktop with 24gb ram

Can I somehow load the database files into ram to speed up processing?

With millions of records some procedures take 1/2 hour or more, mostly due to disk read/write

• FLICKER: Possibility of loosing data in case of power outage or system crash is not a concern?
• Jacek Cz: Most DBF/Clipper libraries prefer low local buffering policy because of power security and sharing/locking (local copy isn't political correct). Maybe tune it to more aggressive buffering?

Answer 1

Probably the easiest way is to use a "RAM Disk". There are various bits of software around.

http://www.softperfect.com/products/ramdisk/

Of course, loading large amounts of data into RAM will itself take some time. Fitting the computer with an SSD would give some performance boost without having to load the data into RAM.

-EDIT

As an aside, you don't say much about what processing you are doing on the data which can have a major effect. If, for instance, you are reading each record doing a calculation and writing the data back, then loading the data into RAM may actually slow things down as you will load into ram, load record from ram, process record, save to ram, save to disk instead of simply load a record to RAM, process record, save to disk.

• user127566: I have downloaded the ram disk software and am giving it a go. If it works then it is a very simple solution, with only a few directory names to be changed in the program.

Answer 2

Too bad that Delphi 7 is able to produce 32-bit binaries only. That leaves a good part of the 24 GB unused.

Options:

• RAM disk (as Jadee proposed).
• Dedicated caching system, like Memcached or, more recommendable, Redis
• Have more than one instance of your program running and "shard" the data between them
• AWE could be an option on a 64 bit OS

Comments:

• Both RAM disk and external caches can be easily used by other processes. Altough it does not affect the solution to the problem, this may be an important benefit to have.
• For Memcached and Redis and other systems, there are working Delphi adapters available for the Memcached protocol and STOMP as OSS. I personaly would prefer Redis, as it is available as pre-built package for Windows. Building Memcached on Windows is a real PITA, Redis is up and running within minutes.
• AWE requires a non-standard privilege, and the allocated physical(!) memory is reserved to the calling process. Thus, I would consider that option at last.
• Using sharding over multiple instances requires some clear partitioning logic. If you can't do that, you need a coordinator which will have an performance impact, so in that case it would be better to use the caching system approach instead.