Hello.

I have a query that I am considering putting into a memory table as I can get better results that way, however I am concerned that if 40+ people have this query open, each one will have its own memory table and use up more memory on the server than in really needed.  This query can remain open for hours per user.

I had a though that if the memory table is canned, it will create a temporary table and I was hoping that I would then be able to drop the memory table and use the temp table to feed the client.  I tried this and it didn't work, I kind of already knew this but was hoping I might have been using them wrong for some time.

Is there a way to use a memory table like this.  Create memory table to generate result set then put into a temporary table and then allow the memory table to be dropped?

This is client/server just to be clear.

Thanks
David.

On 7/22/2021 4:18 PM, David wrote:
>
> Is there a way to use a memory table like this.  Create memory table to generate result set then put into a temporary table and then allow the memory table to be dropped?
>
> This is client/server just to be clear.
>

Easier is to simply select into a new disk table that in case of C/S is
created on the server.

You do need to handle its lifetime - remove it when you're done since it
stays around. The other aspect is that you need to also add indexes
after select.

If each user needs its own table with unique content then you need to
figure out how to generate a unique table name - for example user login
+ some identifier to signify which data it is might be enough but you
would know your own app better.

Raul

David


Whilst I support what Raul said in outline I do have a few questions:

1. how big would the table be
2. how often would the contents of the table change
3. does each user have their own content eg are parameters to the query changed per user or is the sql fixed




Roy Lambert

Thanks Raul/Roy.

1. The table would be no more that 1000 records, it depends on the users work load so would vary. I think size wise no more than 500k each at most.

2. The content is coming from an already existing disk based table, so this table would only be a subset of that and it would not change itself its simply to feed a big grid in the application.  Any writes are done with tmp queries on the disk based table.

3.Yes and know, the content can be filtered by user or by department, but other than that, it is pretty much fixed.

I did think about using a disk based table, but as Raul pointed out, would need to be managed and will be slower than a memory table.

I just wonder if DBISam is missing something here, if you have a canned memory table, not only do you have memory usage on the server with a memory tabke but you also have a table that is already on disk due to it being canned, so in effect you have 2 tables instead of just one.

Saying that, maybe DBISAM is already writing to disk anyway with the temp\canned table, so might not be an issue?

David


Roy Lambert wrote:

David


Whilst I support what Raul said in outline I do have a few questions:

1. how big would the table be
2. how often would the contents of the table change
3. does each user have their own content eg are parameters to the query changed per user or is the sql fixed




Roy Lambert

David

Reading your post I have a comment and an idea:

The comment: At the sizes you're talking its 20MB RAM from the server so as long as you dispose of it when no longer needed it shouldn't be a problem for all users to have their own copy. If it is bung another memory card in the server

The idea: From what you're saying the memory table isn't refreshed every time the underlying disk table is changed so why not, at user logon, create a memory table and stream it to the user so that they have a local copy - even better for speed and streaming the table across shouldn't take that long and if done in a thread shouldn't be noticeable.

Long time since I usd DBISAM so if you like the idea I'd suggest skimming these newsgroups for the code to achieve it.

Roy

David,

<< I have a query that I am considering putting into a memory table as I can get better results that way, however I am concerned that if 40+ people have this query open, each one will have its own memory table and use up more memory on the server than in really needed.  This query can remain open for hours per user. >>

I wouldn't worry about it.  The Windows virtual memory paging will ensure that the memory is paged out to disk, if necessary, if it isn't being used very often.  Not that this is an ideal situation (low physical memory), but it means that it's unlikely to cause any actual issues.  This is effectively how Windows manages the buffering of the file system - it will use as much as it can get its hands on, but will give it up if the memory is needed for actual processes, etc.

<< I had a though that if the memory table is canned, it will create a temporary table and I was hoping that I would then be able to drop the memory table and use the temp table to feed the client.  I tried this and it didn't work, I kind of already knew this but was hoping I might have been using them wrong for some time.

Is there a way to use a memory table like this.  Create memory table to generate result set then put into a temporary table and then allow the memory table to be dropped? >>

If you force a canned result set for the query (RequestLive=False), then this is, effectively, what you will get.

Tim Young
Elevate Software
www.elevatesoft.com

Thanks Roy, might be worth considering streaming, never tried it before.

Tim, are you saying that if I run a memory query as long as I have requestlive = false, then I can drop the memory table but keep canned temp table?  Would I need to drop the memory table in a separate query then as I can't figure out how I could do this in one script?

Regards
David.


Tim Young [Elevate Software] wrote:

David,

<< I had a though that if the memory table is canned, it will create a temporary table and I was hoping that I would rthen be able to drop the memory table and use the temp table to feed the client.  I tried this and it didn't work, I kind of already knew this but was hoping I might have been using them wrong for some time.

Is there a way to use a memory table like this.  Create memory table to generate result set then put into a temporary table and then allow the memory table to be dropped? >>

If you force a canned result set for the query (RequestLive=False), then this is, effectively, what you will get.

Tim Young
Elevate Software
www.elevatesoft.com

David


I just had another idea.

1. Create a disk table for each user with the structure you want.
2. When you want to update it
a) call table.EmptyTable (this is fast since all it does is truncate the file(s)
b) use an INSERT INTO query

When the user is deleted make sure you delete the table.

You could create/delete the table with each user session but this way would have the advantage of persistence. All you need for this is a unique user ID.

I'd probably have another table with user ID and date last updated

Roy Lambert

David,

<< Tim, are you saying that if I run a memory query as long as I have requestlive = false, then I can drop the memory table but keep canned temp table? >>

No, what I was saying was that what you get when RequestLive=False is the equivalent of a temporary table that will avoid any potential memory issues and will be isolated for each user session.  Temporary tables are flagged as such at the Windows level so that Windows will try to keep them buffered entirely in memory, anyway.

Tim Young
Elevate Software
www.elevatesoft.com