Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 9 of 9 total |
Canned Memory table question |
Thu, Jul 22 2021 4:18 PM | Permanent Link |
David | 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. |
Thu, Jul 22 2021 9:26 PM | Permanent Link |
Raul Team Elevate | 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 |
Fri, Jul 23 2021 2:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Jul 23 2021 11:35 AM | Permanent Link |
David | 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 |
Sat, Jul 24 2021 3:54 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Aug 4 2021 7:04 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Aug 9 2021 6:44 PM | Permanent Link |
David | 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 |
Tue, Aug 10 2021 2:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Aug 12 2021 7:29 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |