[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

In memory database, shared memory, etc

We have an application that is real-time and must update every second.   Sadly, we are using MS Access as are database.

The issue is the front end is handling drawing all the information on the screen which it gets from the database, and we have another service that uses a socket to retrieve info from a unix system and put this info into the MS Access DB.

I'm really worried about speed and having the socket program write all the time, it failing etc.

Isn't there a way i can do shared memory between applications in some sort of DLL?   Or is there a better mechanism between sharing high speed data between two apps (one running vb 6.0 right now, soon to be C#), other application is C#.

I'm just a little paranoid about a real-time application having two programs trying to retrieve info from a  Database every second!


1 Solution
have a look at Berkley DB, which supposedly supplies fast (optionally) in RAM databases... URL : http://www.sleepycat.com/

Btw, you could always replace the Access database with a strong opensource DB (PostgreSQL might be a good choice)...
One or two database accesses per second shouldn't be too much of a performance problem.  Perhaps your newly designed application could use a messaging strategy instead of a polling strategy - that is, when something changes, a signal is sent to your GUI which then updates its display.

If you're looking for a small, high-performance database I would personally recommend not to try PGSQL and take a look at something like firebird (http://sourceforge.net/projects/firebird).  Postgre has great features but performance is not one of them.
rknealAuthor Commented:
can you send an external event to a program from another program???

Lets say i'm in C# code that is doing the interface, could my vb 6.0 program send an event to it? Or are you saying we might want to look at something like a socket program between the two
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

A socket would certainly be one way to do it.  Your C# program could listen on port XXX, and the vb program could send messages to let it know that the data has changed.  This has the added "bonus" of letting you run the processes on separate machines or across the globe if you ever needed to, but that's probably not desirable anyways for a real-time application.
If you must avoid the overhead of TCP/IP based communication, then it is possible to have windows programs communicate via shared memory using the Win32 API. Look up CreateFileMapping, MapViewOfFile and OpenFileMapping. The process that creates the shared memory would use CreateFileMapping and the process wishing to use the shared memory would use OpenFileMapping.

Hope this helps.
How much information is being moved from the Unix database to MSAccess?  You should only move as little information as possible, and move it mostly when other things are idle, if possible.  If the Unix source is fast enough, consider writing a web service to access the Unix source directly, and fetch only the necessary info for the task at hand.  If there is no way around the Unix -> Access shuffle that you're doing now, I would definitely suggest MSDE, Interbase, Firebird or MySQL.  Any of them would be faster than Access.  MSDE supports bulk-inserts which will handle HUGE inserts from the Unix source (sometimes 20,000 rows per second), but inserts ONLY- not updates.  MySQL is generally one of the fastest databases around (and is fairly cheap), but doesn't support all the features that the others do.  Interbase (v6.0) and Firebird are open-source and are embeddable.

There are several ways that you can share data between applications.  Sockets are one of the fastest for transmitting data and there are a host of libraries to help make the job easy.  C# comes with some swell objects in the FCL right off the bat.  I would also recommend the signalling methodology mentioned by barryfandango.  It will make updates faster and minimize traffic.  For simple notifications, Mailslots are easier than sockets, but are one-way and not guaranteed to be delivered (though I've NEVER seen them fail).

Memory-mapped files are the method mentioned by KeithWatson.  it works, but requires the data to already be there and the programs STILL have to communicate which parts are of this huge data store new and which parts are not, or they have to reload the whole data store every time they update.  It serves the same purpose as two applications using a database, which is usually better anyway.

My suggestion is to use MSDE with bulk-inserts to absorb updates from the Unix system, or a webservice to query the UNIX system directly.  If there is not much data coming in from the Unix system (even if it is frequntly), this should not be a problem.  I would still recommend another database over MSAccess, though.
rknealAuthor Commented:
well, it is not a lot of data, but often. It's only basically 138 rows, need to read/write from them them every second.

I just hate MS Access, it's such a dog.  We have some experience at MySQL, so will try that or that berkely DB that can do RAM database.


Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now