designtime In memory datasource : modifications

[robrecht]

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.

Code: Select all

_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

[achiary]

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)

[sean]

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 ?

[robrecht]

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?

[sean]

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.

[Bernd.N]

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.

[robrecht]

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.

[Bernd.N]

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?

[robrecht]

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...

[Bernd.N]

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.

form_loading.jpg (213.05 KiB) Viewed 10931 times

[robrecht]

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

[Bernd.N]

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.

[sean]

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 [url=localhost:8080/servoy-admin/database-performance]performance data[/url]
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)

[Bernd.N]

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.

[ROCLASI]

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.