Jump to content

best practice to use in-memory table


mazluta

Recommended Posts

i would like to here from experience users... or those with knowledge...

what is the best way to use in-memory tables.

i have form that contain 8 tabs, each tab hold some "group" data to enter, check, sort....

NO 1 - AdoDataSet in ReadOnly Mode for look up, select, check existing.

NO 2 - TClientDataSet as In-Memory table to help user fill all data related to Indexind Document...

some of the data is 1:1 relation (1 document - some property)...

some of 1:N relation (list of users / groups with authentication permission) ...

 

what in-memory table beside of clientdataset can be used?

who is the prefer one? even if it cost...

remember i use ADO as connection to SQL database.

if i use SQL memory table as (create #dbTemp....)

and 200 users are working simultaneity which method will use less memory in the server?  

i also have to sort data in memory table by user demand. ASC and DESC.

 

need advised.... 

 

image.thumb.png.ed02d81ddc4057d663acffb7ae032d99.png 

Link to comment
Share on other sites

well.... farshad... sherzod...

How do you build temp in-memory tables in your applications...

in the bible, book of judge chapter 9: see what I'm doing and quickly do the same... so let learn from you guys...

Link to comment
Share on other sites

2 hours ago, mazluta said:

well.... farshad... sherzod...

How do you build temp in-memory tables in your applications...

in the bible, book of judge chapter 9: see what I'm doing and quickly do the same... so let learn from you guys...

Hello, if I can help with a suggestion.

In one of my cases I have to create a unique identifier for each user (session). I use this identifier in several tables.

So, If You can create unique ID for every user (and session) then this will be helpful for creating temporary tables name.

 

The collaboration of sessionID, IP addresses, date and Time and some UserID orTerminalID can help you add names like:

mytemptable125236 - where 125236 is the uniq ID for this session.

Saving uniqID for a session in cookies is useful.

You must to know, i don't use Append, Edit, Post and so on.

I work only with Queries (INSERT, UPDATE, DELETE) , this help me to use AdoCommand to reduce using of memory.

 

I don't know if this is best practice but i use it in my work.

Link to comment
Share on other sites

hi irigsoft

thank for your answer. 

but this not what i meant.

the problem is not "the unique name of the table..."

=========

if i have 200 users and this form have 10 in-memory tables, the the server have to deal with 2000 in-memory tables.

if i use SQL temp methode, like Create table #ClientTemp fld1 fld2... the application wil use the AdoDataset name. but sql will give any CREATE its on name and all good.

but what is the best practice  - to use ClientDataAet as temp tables or create SQL temp table and let SQL to handle it ?

should we use SQL, OR CLIENTDATASET or some thing else?

 

Link to comment
Share on other sites

what do you mean AdoQuery...

i have dbgrid.

it depend on DataSource MyTblSrcDS

this DataSource Depend on MyTblSrc wich is in-memory table...........

--->> what dose MyTbl Is

1. SQL temporary table?

2. clientdataset object?

3. third-party in-memory tables?

Link to comment
Share on other sites

4 hours ago, mazluta said:

what do you mean AdoQuery

You mention before that: 

On 8/24/2021 at 9:38 PM, mazluta said:

remember i use ADO as connection to SQL database.

In my case I use TAdoConnection for connection with database, TAdoQuery to execute queries and DBGrid to show results. I using point 1:

On 8/24/2021 at 9:38 PM, mazluta said:

NO 1 - AdoDataSet in ReadOnly Mode for look up, select, check existing.

In some forums in web i found that: To reduce memory usage, for some queries it is good to use TAdoCommand.(Queries without showing of user  results)

 

in the case of uniGui you can try TuniQuery.

Link to comment
Share on other sites

13 hours ago, mazluta said:

if i have 200 users and this form have 10 in-memory tables, the the server have to deal with 2000 in-memory tables.

If i understand you, you are trying to develop a web application like we did in the past with a classical Client /Server application. Sorry, This is a bad practice.

Using All Client PC memory 4Go (as a super market software when you fetch all data (Articles , prices, Qte,...) had no impact.

Now, the best practice on a web application is to show only the eyes of Mona Lisa and not the whole portrait.

So in your 10 tabs, you can limit max 25 rows, and use SQL classical tables.

I can suggest you devart datasets. They are very..very..very fast with low foot print memory.

(instead of Ado). 

Link to comment
Share on other sites

hi Abaksoft.

Thanks for your answer.

it's look i can not forward my needs....

every session make Query as the eyes of dutifully Mona Lisa.

i ask about the INPUT data table.

All the frame intend to get A LOT OF DATA for the same - 1 - Document.

property about the document... date, description, type, file (box), client....

property about view authentication - how is permitted to see the document.... users (many) , groups of users (many)

property about "where" to fill this document - maybe to some other files (box's) too (the document contain data for 2-10 clients..)

property to whom create assignment (one or many) - the document arrive - do something...

........ more 6 data tables....

 

FOR ALL THAT INPUT DATA PROPERTY i use IM-MEMORY tables (for now TClientDataSet).

in the VCL application i used TMemTable of EhLib - very good.

in some case i used to create temporary tables in the SQL server and use those tables as in-memory tables.

on Normal VCL if i used TMemTable or TKbmTempTable each pc use it's on memory to deal with the data.

Here - The data is going from the server back to the client and vice versa.

so - if i have 200 user, with 10 temporary tables per session for INPUT data - what is the BEST METHODE

1. SQL temporary table? let the SQL server to deal with the data

2. TClientdataset object? - use the pc + server memory

3. third-party in-memory tables? - use the pc + server memory

 

 

Link to comment
Share on other sites

I suggest loading the data using TFDMemTable (firedac) or TVirtualTable (sdac)
using tables in memory in the database, you will consume memory to keep those tables on the server during your use, and it will also consume memory in your application server, as you will load this data in memory for display to your user.

using TFDMemTable, you only use memory in the application server, because you search your database and the connection can be undone.
another basic rule, limit the data loaded to the minimum possible, never use select * from table, when you know that the table may have many records.

Link to comment
Share on other sites

hi wilton.

thanks for the answer.

i read about DevArt, nice, but since i work with SQL or Access for demos it is better to use ADO or FD.

the thing is - if i will use SQL temporary table, all the work in done in the SQL server (sort, handle, post, insert, memory management...)

and SQL server knows how to do this thing...

i think it's better (work with SQL temp table), but i afraid that the SQL server himself will crash, i don't have enough experience in Delphi for the web.

200 users with 10 temp table * 20 records Average, is not look so bad, but if the organization have more system work with the same SQL, maybe ClientDataSet or FD will be better.

Link to comment
Share on other sites

the question of you bringing this to your application is not related to whether or not sql will manage better as tables, but that you share the workload, and leave sql for the most important things.

an important detail and precisely the ordering, it costs a lot for sql to load your data already sorted,

if you load the data without order, and do this in your application, you will find it cost much less.

if you research sql server sorting on the internet, you will find that this should only be used when necessary.

Link to comment
Share on other sites

It's time to use ExtJs Stores power, with a dedicate components for manage LOCAL (CLIENT) datasets.
I need to download "static" memory tables (or queries) to clients for lookups or other needs, freeing up resources on server for every session.
We need 2 different type of datasets: server and client one. Unigui already fill the client dataset, but leave open the original resource on server for manage changes and updates.
Good is to have a client dataset, for several pourpose,  that is disconnected from the server (zip lookups, cities, etc..) or for perform fast calculations on CLIENT side.
Better is to have a client dataset with "cached update" features. So we can disconnect and close server query, perform CRUD ALL ON CLIENT, and reconnect to server for apply updates.
This can save a lot of resource on server (especially on installation with a lot of users) and very very much network traffic.
(now there is a call for every grid scroll or navigation key).
UNIGUI is wonderful, but now we need more powerful CLIENT dedicate components in addition to existing ones, for optimize resource and traffic, shifting the load to the client.
In the above scenario, many of the tables can be sent to the client (an operation that Unigui already does very well) without having to leave them open on the server. This for every session. That's a nice saving if you have 1000 active sessions :-)

(Sorry for my bad english :-))        

Link to comment
Share on other sites

1 hour ago, Stemon63 said:

In the above scenario, many of the tables can be sent to the client (an operation that Unigui already does very well) without having to leave them open on the server. This for every session. That's a nice saving if you have 1000 active sessions 🙂

Hello,

You can use StringGrid to load data from query. After populating data on ClientSide close Query on the Server Side.

This idea can work good if You dont need  live connection with server.

If this is made by UNIGUI is better ;)

 

Link to comment
Share on other sites

2 hours ago, irigsoft said:

Hello,

You can use StringGrid to load data from query. After populating data on ClientSide close Query on the Server Side.

This idea can work good if You dont need  live connection with server.

If this is made by UNIGUI is better ;)

 

Yes, but StringGrid is visual and for display pourpose only. I intend a way for use ExtJs Store (populate from Unigui) as a true client dataset. For lookups, for example.
I have a "prototype" code for use store as lookups (but I don't known ExtJS). It's fast. BUt we need a Farshad "native" solution.



 

  • Like 1
Link to comment
Share on other sites

hi wilton

image.thumb.png.ba3e69e6e78458cde992aa6c440960e7.png

 

i don't think so. SQL was build to work with lot of table, lot of users, query, ordering and so on,

there no such thing show grid with no order....

for big project (like for insurance company), when i know -  "there is money" there - good server, good SQL server , different from the APP server, i will always chose SQL temporary tables, since they work mostly in TERMINAL server (and all users use the same machine...)

but... this is my first project building Web application, and i don't want to use something that can make project fall.

 

Link to comment
Share on other sites

and for irig-soft, i am not talking on query data from the server.

i'm talking OF GETTING INPUT DATA FROM THE USER. and for 10 table *LOT of FIELDS * 200 USERS in Session could be HAVY (maybe?).

SO IF the masters Farshad or Sherzod can answer that it will be nice....

what is your method of GETTING LOT OF INPUT DATA from the user

1. SQL temporary tables?

2. TClientDataSET,

3. FDMemTables

4. other solution

 

image.png

Link to comment
Share on other sites

If you are only talking about input, there should be no problem if you use insert queries only.

Because then you are not opening any tables, but only executing queries.

If you need to display a lot of data after all the input has been saved, you can join many tables in a query.

I only use queries, never any table dataset components, and I only use live queries with small tables.

Better to write the SQL yourself, and select/insert/update only what you need if you need speed.

Link to comment
Share on other sites

it look like no one really understand

(execute query = open table, if it join qry then = create new table with new fields, read tow or more tables, some function [join], insert to the new table....)

12 hours ago, mazluta said:

what is your method of GETTING LOT OF INPUT DATA from the user

1. SQL temporary tables?

2. TClientDataSET,

3. FDMemTables

4. other solution

don't give me explanation, what are you doing?, what are you using?, what is the method you use? - for input data

the tell us WHY THIS METHOD and not other

Link to comment
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
×
×
  • Create New...