Hi,
Is it possible to manipulate in-memory datasources at runtime?
Situation I have right now :
1 datasource mem:article_inmem with fields : name, article_code, description
This allows me to bind this datasource to a grid component designtime and in servoy developer you can choose the matching dataprovider from this in memory ds for each column
This datasource is populated with a dataset containing these 3 fields.
_ds.createDataSource('article_inmem')
Now I want to define some extra columns in this datasource, because my dataset will also be modified : 4 columns instead of 3.
I don’t see any options to do that?
When datasource contains 3 columns and dataset contains 4 you get this error :
Data set rows do not match column count
Wrapped com.servoy.j2db.persistence.RepositoryException: Data set rows do not match column count
Thanks
Robrecht
Hi,
You can do it but you have to add the additional column to the dataset before doing the “createDataSource”.
Here is an example to add columns to a dataset :
var $ds_movim = databaseManager.getDataSetByQuery(serverName,q_movim,args,-1)
//Adding columns credito, debito
$ds_movim.addColumn(‘debito’)
$ds_movim.addColumn(‘credito’)
If needed you can also get or set the dataset contents programmatically using for instance :
$pro_ant = $ds_movim.getValue(i,1)
$ds_movim.setValue($pp,idxFinal,$saldo_ant)
Hi Robrecht,
When populating a design-time in-mem data source, the columns of the dataset must match the data source definition.
(A bit different when creating the datasource completely at runtime)
May I ask why are you changing the definition ? Why not edit the original data source ? or create a 2nd in-memory datasource ?
I assume this is a dynamic thing, were you have several columns you know about in advance, but also need to add some that you do not know at runtime ?
Hi Sean,
This is indeed what I meant.
No problem to add columns to datasets, but I wanted to change the design-time definition of the in mem datasource.
I am facing the current situation:
I have built a custom grid (web)component on which we can show several columns with the dynamic dataproviders.
On a first try I tried to bind these columns with dataproviders from real table foundsets and related foundset.
The problem is that servoy is executing hundreds and thousands of queries to get all this related table data for all rows and columns displayed in the grid, and still needs extra queries when scrolling through the grid.
So I thought the inmem datasource could be useful since we can populate this datasource with 1 single query. Loading extra rows would only execute extra “select top…” queries on the _sv_inmem database.
This seems a good solution since there was a significant performance boost.
But then indeed, we don’t want all columns fixed at design time. We should be able to write some custom code and dynamically add extra columns at runtime without having to create a new datasource.
Besides that, what is the use of a design-time in memory datasource when you can’t populate this datasource design time? You have only a definition and as far as I see now, you have to bind a dataset to it in the js code. It would be nice if we can for example add a query to this datasource design time?
Hi Robrecht,
Besides that, what is the use of a design-time in memory datasource when you can’t populate this datasource design time? You have only a definition and as far as I see now, you have to bind a dataset to it in the js code. It would be nice if we can for example add a query to this datasource design time?
What would this look like in developer ? There is not currently anything that lets you define a query at design-time, so that would have to be invented as a first step.
Anyway, I guess you can call databaseManager.createDataSourceByQuery(q) in the solution onOpen event. This should give you everything at the start, no ?
Also, I should point out that in-mem source could be populated by web service or file, etc, so it is really meant to be populated at runtime.
robrecht:
The problem is that servoy is executing hundreds and thousands of queries to get all this related table data for all rows and columns displayed in the grid, and still needs extra queries when scrolling through the grid.
And did you take a deeper look to the root cause of this behaviour of Servoy?
Why are thousands of queries needed? Are Calculations involved or any other fancy stuff?
I mean it might be easier to solve the root cause instead of introducing a workaround with in memory datasources, which has then other challenges.
Hi Bernd,
This is default Servoy behaviour when working with foundset and related foundsets, isn’t it?
If you have a table view for example with 20 columns on it, and each column contains information from another database table, then servoy executes 20 queries just to show 1 row in the table.
Suppose you scroll through this table view from record 0 to 1000, servoy executes (maximum) 1000 * 20 = 20.000 queries…
I know this is worst case scenario with no cached relations but still this is possible even without calculations or rendering or other stuff.
I would like to let a real Servoy insider respond to you about this Servoy behaviour, as I do not know the Servoy mechanisms from the inside.
When you do a minimum example like one table and one related field from another table, and then use in Servoy Help/ServoyAdminPage, go to PerformanceData and clearStatistics and let the table form show and then look at those statistics - how many new queries can you see then?
I made a test form with 1 column from 1 table and 1 column from a related table
PerformanceData for this form (a locked table view) :
4 x “load foundset” : select top 31 … from table 1
110 x “relation” : select top 61 … from table 2
This is behaviour I know for years and I was hoping using memory tables would decrease the amount of queries in such a scenario…
I also made a test, loading a form from our projects table with one relation to the customer, and can confirm your observations.
There were three different queries.
The first loads all project table fields (called 3 times),
the second seems to load all customer fields for each different customer that appears in the relation (called 20 times),
and the last loads only the primary key project_id (called one time).
That’s interesting for me, as I am interested in performance behaviour.
I will investigate this further.
[attachment=0]form_loading.jpg[/attachment]
Thank you Bernd,
If we can help somehow in this investigation please let me know, for our company the overall table/grid performance is the biggest issue to cover this year.
Robrecht
Help in this case would come from Servoy directly. You could create a case in JIRA.
One solution could be that we could set a servoy form to a kind of “SQL-fast-mode”, so that we could provide the ideal SQL query that would get all needed fields in just one query, including the related fields.
Hi Robrecht,
Yes this is a very common observation in Servoy.
Keep in mind that the reason that Servoy loads related records/foundsets in separate queries is to support a user really browsing through data.
Rather than query just what might be shown on a form, the complete record is loaded when it is first needed. But those records will also remain cached. Moreover, they are also updated via data broadcasting. Therefore, when you do something like navigate to detail of a related record, it is already loaded. It is even updated when another client session changes the records. This works nicely for many setups.
However, performance can become an issue on grid views which are showing many related records (or calculations).
It is best to profile your grid loading with the performance data
Clear the stats just before your form shows (for the first time) and then you really see both the volume of queries and the performance of each query.
Although, many queries are issued, the types of queries that load related records should be very fast. Be sure that tables are indexed properly (on foreign keys for example). The difference between 2 ms and 10 ms may not sound like much, but if the volume of queries is very high, then you see performance degrade quickly.
Also, grids have a higher impact in rendering too. Which client do you use? If NG client, then consider the Table Component (over the richer, but slower Table View form).
Finally, if your grid loading is optimized as best as it can be, but you still see a performance issue, then you can turn to in-mem datasource as you suggest. In this case, keep in mind that foundsets for in-mem datasource will not be updated via broadcasting. So you should be careful to ensure that you are not showing a “stale cache”. Trap onDataBroadcast event to know about changes and force reload of a cached record where applicable.
We are actively researching into better support for in-mem data sources, and reducing the complexity at design-time for pagination and sorting as well as a way to receive data broadcasting automatically (i.e. based on known relations used in a query or something)
Hi Sean,
it is a very common use case to show orders or invoices in tables views.
Then you have a lot of related data per record/row, like customer name, project name, project leader name, own branch name, own department name, business segment name etc.
So when every table row has for example six related fields that need to show, and that results in six queries for each row where the full related record with dozens or even hundreds of fields is queried and not only the tiny field like the customer name, that’s clearly suboptimal.
Would a “SQL-fast-mode” be thinkable for a Servoy form, where this standard behaviour could get switched off by the developer?
Instead the complete ideal single query could be provided in that “SQL-fast-mode” by the developer, which would result in just one SQL access to the physical DB-server instead of hundreds.
I got your point that cached data is nice when it is likely that a detail view is to be expected, but in that use case where you just browse through a large table to get an overview, detail views for all the related records are quite unlikely.
Hi Bernd,
When you need to show a list with records that come from multiple tables it’s recommended to do that in one SELECT statement.
However Servoy is designed around the JSFoundSet which is a representation of a database table and any related columns are pulled in with another query. In a way this is the ‘nature of the beast’.
So if you want ‘SQL-fast-mode’ you could create a database View and use that as a datasource for your forms. This way all data is collected in one SELECT statement.
And using In-Memory tables filled using a query is essentially a View but then done in the Application-Layer. Both approaches have their pros and cons.
And of course Views are not updatable so if you need to update the source tables that would require some extra logic. But from what I understand this gets a lot easier in Servoy 8.2 where you can code your own SELECT/INSERT/UPDATE/DELETE code on a (In-Memory based) JSFoundSet.
Hi,
I coulnd’t agree more with Bernd.
The “SQL-fast-mode” is what I was trying to accomplish by using the in-mem datasource.
I know in advance which (single) query I want to execute to populate the whole table view at once, and it would be nice to have this already arranged design time.
We got a bit off-topic but the original question was how to manipulate this original datasource when a client wants to load and see more fields than provided in design time.
However, performance can become an issue on grid views which are showing many related records (or calculations).
We have already put a lot of effort in optimizing our queries - none of the queries have more than 1ms execution time - but we are at a point now that we should do something about the actual amount of queries and data model changes are out of the question
Also, grids have a higher impact in rendering too. Which client do you use? If NG client, then consider the Table Component (over the richer, but slower Table View form).
I’m using the NG client and the grid is a custom grid we built with an external library.
The table component nor the table view are sufficient for us as we desire a very rich ànd fast performing grid component…
Robrecht
The table component nor the table view are sufficient for us as we desire a very rich and fast performing grid component…
@Servoy: It looks like it (fast and rich) is possible in the NG-client ! Please make it available as standard Servoy component…
ROCLASI:
So if you want ‘SQL-fast-mode’ you could create a database View and use that as a datasource for your forms. This way all data is collected in one SELECT statement.
Hi Robert,
this seems to be the ideal solution with currently available Servoy techniques.
So I immediatly tried it and created a minimum viable example :
CREATE OR REPLACE VIEW _v_test AS
SELECT
p.project_id,
p.p_name AS project_name,
bp.bp_name1 AS customer_name
FROM projects p
LEFT JOIN business_partners bp ON bp.business_partner_id::text = p.customer_id::text
Although I added the PK project_id to the view, Servoy complains now as below.
Servoy refuses therefore to show the form.
Can this be solved?
[attachment=0]problem.jpg[/attachment]
Side note: I once heard that a view should not be added to the database information files, as then a real table would be created.
So it is better to accept the warning in that case.
Hi Bernd,
Bernd.N:
Although I added the PK project_id to the view, Servoy complains now as below.
Servoy refuses therefore to show the form.
Can this be solved?
Yes, go into the Table editor in Servoy and mark that column as row_ident. That’s all.
While you are at it you might have to ‘unhide’ the table in Developer by right clicking on the table in the Database server tree and select unhide.
Hope this helps.
Hi Robert,
Thanks a lot for your help !
I will try this as well in combination with the grid, I’m really curious about the performance results…
Robrecht
