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.
[attachment=2]view_test.jpg[/attachment]
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.
[attachment=0]warning.jpg[/attachment]
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?
HI Bernd,
That looks normal. Foundset loading is composed of two different queries.
- 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)
select pk from table where <condition> order by <sort columns>
- The record loading:
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.
Interesting, Sean, those chunks seem to have the size 30, as that is the number of the questionmarks in the first SELECT.
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)
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. ![:-)]( "Smile")
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
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.
