Hi,

I would like to create a database in memory and create tables from views from another "normal" database.

Also the tables in the database in memory need to be updated when the related view has changed. These views are for monitoring purposes and change regulary.

Has someone suggestions/ideas how this can be done?

Mike


Creating an im-memory table from one on disk is fairly simple. The same should apply to a view since they are treated as a table. The big problem is detecting when they've changed. There may be something built in but if so, since I've never needed it, I don't know.

I don't understand what you're trying to achieve but what about:

1. create the in-memory tables at startup
2. run a small script at intervals which would
a) delete any records in the in-memory table that aren't in the view
b) add any records that are in the view but aren't in the in-memory table.

As I say I'm baffled it looks as though you want to keep two copies of the same data. Why not just use the view?

Roy Lambert

Hi Roy,

Let me explain a bit more.

Refreshing the queries (VIEWS) in a web application costs to much time. Therefore I would like them auto update via web sockets (Esegece).

Unfortunately the web sockets can't see changes from data views. However according to the developer updated tables should reflect the latest changes. That is why I would like to use tables in memory.

Mike

Tim may correct me but I think you're chasing the rainbow.

>Refreshing the queries (VIEWS) in a web application costs to much time. Therefore I would like them auto update via web sockets (Esegece).
>
>Unfortunately the web sockets can't see changes from data views. However according to the developer updated tables should reflect the latest changes. That is why I would like to use tables in memory.

The actual VIEW will be refreshed server side so its traffic that's the problem. ElevateDB is pretty good at just transferring the data needed to update a display, but may still be to slow for you. I think that all in-memory tables are stored on the server but over the years I've lost track of just where they are stored (especially TEMPORARY)  but I think that you'll run into the same difficulties with data transfer.

I can, currently, think of two possibilities:

1. investigate publishing (not sure if it can be applied to views)
2. use multi-threading and a local session to keep things up to date, but this means no interaction with your other tables via SQL

No. 1 I can't help much but there are people on here who do use publishing

No. 2:

a) make sure your views have a unique id of some sort

b) create a thread and, making certain to observe isolation,
i) create a local session, in-memory database and in-memory table
ii) create a remote session, database

c) using something like TSimpleEvent with a suitable interval loop round and
i) get a LIST of the ids for the view, get a LIST of the ids for the local table,
ii) compare the two lists and delete / append as appropriate

You can probably minimise traffic by getting the server to produce a list of IDs and squirt it across, and, send a list of additions wanted back and then just squirt those over en-bloc.


You could probably write a job on the server which would do much of the above for you (say at 1 second intervals) and produce a flag which essentially says ALTERATIONS NEEDED or DO'NT BOTHER NOWTS CHANGED with a list of IDs to be deleted and a package of inserts to be streamed.

Sorry its not a magic bullet but its the best I can think of just now. Tim (or someone) will almost certainly have a better suggestion.

Roy

Thanks Roy for the suggestions.

Another possible idea which I have is to use a Websocket server which calls the queries (VIEWS) frequently and stores them in a clientdataset (FDMemTable or kbmMemTable).

The Websocket client loads the clientdataset remotely so changes are automatically updated.

Mike

>Another possible idea which I have is to use a Websocket server which calls the queries (VIEWS) frequently and stores them in a clientdataset (FDMemTable or kbmMemTable).
>
>The Websocket client loads the clientdataset remotely so changes are automatically updated.

So far I know its a web app, and that you're concerned about speed. I'm guessing that you don't want the user sitting around feeling bored when some process is being carried out. My own experience is that its rarely the back end process that's slow but lots of data being transfered (especially "pretty" pictures). If you want to use the data locally for monitoring then at some point its going to have to be transfered. The best way to prevent user dissatisfaction is to do it in a background thread so the UI doesn't appear to stutter or freeze.

Its a long time since I used kbmMemTable so can't remember how the table size compares to ElevateDB in-memory table but that will be important.

How many clients are going to access this simultaneously, and what size tables are we talking about?

Another option might be a server side proc which would grab the current status, load into a stream, zip it and squirt to a client where its unzipped and loaded to the table.

Roy

Hi,

About 5 clients will access this simultaneously and size tables is about 300 records with 8 fields.

Mike

>About 5 clients will access this simultaneously and size tables is about 300 records with 8 fields.


and those fields are?

There's a tremendous difference between say a BOOLEAN and a CLOB.

The more I think about it the more I think a solution based on publishing and threads to do the update would be the best. Can someone who has experience in using ElevateDB's publishing feature chip in please

Roy

Mike,

<< I would like to create a database in memory and create tables from views from another "normal" database.

Also the tables in the database in memory need to be updated when the related view has changed. These views are for monitoring purposes and change regulary. >>

I'm with Roy on this one - I think you're making it too complicated, for very little, if any, payoff.

What in particular about the views are slow ? The fact that you're using a web application confuses me when you talk about refreshing, etc., because a web application typically has to do a complete set-up/tear-down for sessions, or use a session pool for keeping around sessions for servicing web requests.  If you *are* using a session pool, then simply refreshing a view will only result in the view being re-executed if the underlying tables referenced in the view have changed.

Tim Young
Elevate Software
www.elevatesoft.com

Mike


I'm as guilty as everyone else. There's a balance between rabbiting  on and providing necessary information. Here we still don't really know the problem or what is to be achieved.

"Slow" and "monitoring" are not really sufficient.


My guess is that "slow" is referring to transferring data once generated rather than the speed of the query, but I may be wrong. "Monitoring" may be anything from controlling a nuclear power plant to allowing the user to see how many jellybeans they've bought.

Basically what I'm saying is people here are willing to help but, apart from simple stuff,  we need to know enough to make a decent response. If this is wen based is there a url we can have a look at (with fake data) to see the problem?

Roy Lambert