designtime In memory datasource : modifications

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Re: designtime In memory datasource : modifications

Postby robrecht » Tue Jun 13, 2017 10:20 am

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
robrecht
 
Posts: 77
Joined: Wed Aug 01, 2012 4:30 pm

Re: designtime In memory datasource : modifications

Postby lwjwillemsen » Tue Jun 13, 2017 12:25 pm

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...
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 566
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: designtime In memory datasource : modifications

Postby Bernd.N » Tue Jun 13, 2017 1:28 pm

ROCLASI wrote: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
problem.jpg (32.97 KiB) Viewed 792 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.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 474
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: designtime In memory datasource : modifications

Postby ROCLASI » Wed Jun 14, 2017 4:28 pm

Hi Bernd,

Bernd.N wrote: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.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5205
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: designtime In memory datasource : modifications

Postby robrecht » Wed Jun 14, 2017 5:33 pm

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
robrecht
 
Posts: 77
Joined: Wed Aug 01, 2012 4:30 pm

Using a view in Servoy to optimize DB SELECTs

Postby Bernd.N » Thu Jun 15, 2017 11:22 am

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
view_test.jpg (65.08 KiB) Viewed 752 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
warning.jpg (26.35 KiB) Viewed 751 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?
Attachments
new_profile.jpg
new_profile.jpg (81.73 KiB) Viewed 752 times
Last edited by Bernd.N on Thu Jun 15, 2017 11:09 pm, edited 1 time in total.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 474
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: designtime In memory datasource : modifications

Postby sean » Thu Jun 15, 2017 6:16 pm

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.
Software Engineer
Servoy USA
sean
 
Posts: 86
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: designtime In memory datasource : modifications

Postby Bernd.N » Thu Jun 15, 2017 11:06 pm

Interesting, Sean, those chunks seem to have the size 30, as that is the number of the questionmarks in the first SELECT.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 474
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: designtime In memory datasource : modifications

Postby sean » Fri Jun 16, 2017 5:01 am

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)
Software Engineer
Servoy USA
sean
 
Posts: 86
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: designtime In memory datasource : modifications

Postby mboegem » Fri Jun 16, 2017 4:12 pm

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
Last edited by mboegem on Tue Jun 20, 2017 1:46 pm, edited 1 time in total.
_____________________
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance SAN Developer

Image
User avatar
mboegem
 
Posts: 1382
Joined: Sun Oct 14, 2007 1:34 pm
Location: Hoofddorp, The Netherlands

Re: designtime In memory datasource : modifications

Postby jcompagner » Tue Jun 20, 2017 11:11 am

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.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 7974
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: designtime In memory datasource : modifications

Postby Harjo » Tue Jun 20, 2017 3:36 pm

jcompagner wrote: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 :D :D
Harjo Kompagnie
Direct ICT / Servoy Hosting / ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
User avatar
Harjo
 
Posts: 4269
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Previous

Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 3 guests