designtime In memory datasource : modifications

[robrecht]

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

[lwjwillemsen]

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

[Bernd.N]

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 :

Code: Select all

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?

problem.jpg (32.97 KiB) Viewed 7838 times

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.

[ROCLASI]

Hi Bernd,

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.

[robrecht]

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

[Bernd.N]

Thanks, Robert, that worked well.
I found the view in Servoy in a subfolder of the database and could open it.
Then I changed the RowIdent-column to row_ident.

view_test.jpg (65.08 KiB) Viewed 7798 times

There is still one problem with a warning regarding the null value, but in the Servoy-View-definition-table I can not edit the checkbox to deactivate the null column.
So I would just ignore that warning if there is no other solution.

warning.jpg (26.35 KiB) Viewed 7797 times

That resulted now in a much reduced and optimized SELECT statement as below.
That is exactly the SQL-fast-mode I was searching for.
But is it normal that Servoy will fire that view-SELECT three times?

[sean]

HI Bernd,

That looks normal. Foundset loading is composed of two different queries.

1. The underlying query of the foundset which identifies which records to load. (This is limited to a fetch size, i.e. 200 at a time)

Code: Select all

select pk from table where <condition> order by <sort columns>


2. The record loading:

Code: Select all

select column1, ...columnN from table where PK in <block of records to load>


The latter query can be called multiple times because it is really loading chunks of records as needed.
They are loaded and cached individually like this. They won't be loaded again once they are cached.

[Bernd.N]

Interesting, Sean, those chunks seem to have the size 30, as that is the number of the questionmarks in the first SELECT.

[sean]

Hi Bernd,

The PKs are always fetched in blocks of 200 (as is the JDBC default)
Chunk size for records can vary depending on the usage (I'm not exactly sure how the decision is made - I think the default is 30)

[mboegem]

Hi all,

from what I've seen on SW17, there's already a grid-component available for NG through the Servoy Extra Components package.
I don't know if this will fulfil the needs of Robrecht, but I do know that it's easy to configure using an InMem datasource (configurable at designtime) and to be populated through a query.
Also a more rich featured 'UI Grid' is on its way.

As Robert mentioned in his reply, the InMem datasource at designtime can be configured using queries, so the INSERT event could be 'forwarded' directly to a more complex table structure, also other event triggers are available so definitely something to look into. InMem datasources at designtime are already possible in the current 8.1 releases, so no need to wait.

Having all this, I see the perfect combination to have blazing fast tableviews in NG.
In smart-client I've used the datasetGrid to accomplish this (and still do in versions before version 8 ), but now that you can easily configure the default tableview based on a custom datasource now, I'd say that Servoy has already given us the tools to create very fast tableviews.

Personally I prefer the InMem tables over DB views, as it's easier to create, modify and deploy since it's all packed into the Servoy export.
Modifying DB views is a lot harder, sometimes even necessary to re-create the view.
Besides that - as mentioned by Bernd - if you don't create the view on your targeted deploy environment up front, the deployment will 'take care' of the creation of the view by creating a new table > that's NOT something you want to happen.

Hope this helps

[jcompagner]

if you have loads of related look ups in a table (like productname, company name. Then it could be that using a valuelist on top of the product_id field is better then using a relation indirection.

Because the valuelist will be able to load and cache id->display value in one go. Ofcourse it could be if the table is really big that it still needs to query a specific id because that is not kept yet in the valuelist.
But if the valuelist can hold enough id->display values then it is possible that the next row can reuse something that is already in the valuelist.

Besides this we are playing with the idea that in servoy you can define a "orm" mapping on top of your tables, this mostly means that you can create a datasource (besides db:// and mem:// ) where you can say i want to have a datasource based on this main table and those 3 others and the join between them is this and i have some other filter criteria's.
So this can be seen as datasources (JSFoundset) by query or in database terms: update able views

Because you say which tables are really used and which columns you want to query of those tables, we know exactly the pk and table that belongs to a specific column so we can update it, We could even support delete (but then i guess you want to configure what should be the deleted, all 3 tables or just the main or 2 of the 3). Because for order_lines you really only want to delete the main order_lines table not the N-1 table that product_id points to
Relations between those tables can only be 1-1 or N-1, not 1-N (so orders to order_lines) because that wouldn't make much sense then there is no 1 record per main.

[Harjo]

Besides this we are playing with the idea that in servoy you can define a "orm" mapping on top of your tables, this mostly means that you can create a datasource (besides db:// and mem:// ) where you can say i want to have a datasource based on this main table and those 3 others and the join between them is this and i have some other filter criteria's.
So this can be seen as datasources (JSFoundset) by query or in database terms: update able views

Because you say which tables are really used and which columns you want to query of those tables, we know exactly the pk and table that belongs to a specific column so we can update it, We could even support delete (but then i guess you want to configure what should be the deleted, all 3 tables or just the main or 2 of the 3). Because for order_lines you really only want to delete the main order_lines table not the N-1 table that product_id points to
Relations between those tables can only be 1-1 or N-1, not 1-N (so orders to order_lines) because that wouldn't make much sense then there is no 1 record per main.

+1