PDA

View Full Version : Are virtual tables now supported in grid section for Web/Mobile?



hmischel@diligentsystems.com
2015-01-05, 15:46
Hi,

I see VT's listed on v1.3 in the roadmap for web mobile.

Is this supported right now in 1.3RC10?

Thanks

Herb

hmischel@diligentsystems.com
2015-01-05, 16:05
I see I need a primary key.


**** Lianja error ****
odata_read([/warehouse/vt_currentactivity?$format=jsongrid&$page=1&$pagesize=25&_v=1], [_00006a18036b.tmp])
^
No PRIMARYKEY was specified. You need to specify a PRIMARYKEY when using Virtual Tables.


However, this is a read only VT based on a stored procedure.

It is data that changes constantly showing locking and blocking for SQL Server spids on the server.

What would be the best way to handle this?

(perhaps being able to specify readonly would negate the need for a primary key)

Thanks.

Herb

davefoss
2015-01-05, 16:06
Hi Herb,

Yes, there is VT support in the 1.3 RC 10 release.

barrymavin
2015-01-05, 19:53
Hi Herb,

Without a primary key it is not possible to handle any updates but also it is not possible to handle any pagination which will not scale and would be unusable in a web / mobile app scenario. The primarykey should be specified in the VT properties.

This is a core problem if stored procedure calls are used as the VT SQL statement.

When no SQL SELECT is specified Lianja makes use of the basetable property for updates.

It does however need a primarykey as it generates the backend SQL SELECT statement dynamically when fetching a row or a page of rows to display in the Web Client. It is impractical to read millions of rows into a web browser or a mobile device. Lianja therefore reads on demand and the client fetches x rows starting at a specific offset.

This is all done independent of the backend database server. So it handles MSSQL, MySQL, PostgreSQL, LianjaSQL and other SQL syntax.

barrymavin
2015-01-05, 20:15
It may be possible to add another VT property called localpaging so that all the data from a stored procedure call is fetched into the cloud server and it performs the pagination from the local cursor but I dread to think of the performance consequences of this on a heavily loaded server with thousands of users fetching one record to be displayed in a form section from a query that results in millions of records.

And to be sure somebody will build such a system them complain about performance.

hmischel@diligentsystems.com
2015-01-05, 22:57
Hi Barry,

hmmm.......

I see your point about using stored procedures in web/mobile.

Someone will inevitably write a proc that returns a hundred millions rows and then complain that Lianja crashes their system.

However.......

Would it be feasible to have some sort of "buyer beware" switch in the VirtualTable definition. something like Properties "PAGINATION = OFF, MAXROWS =250".

This would make it the responsibility of the developer to manage the rowsizes being returned to a hard limit that Lianja can handle.

It would not be updatable, so there should not be a need to round trip.

Stored procs are such a big part of what we do, I'd love to figure out a way to get them into the web at some point.

Thanks.

Herb

barrymavin
2015-01-05, 23:34
Hi Herb

Its not a question of a huge query being retrieved that crashes the server its a question of how to populate paginated grids in the web client and how to fetch one record from a specified position in the resultset to display in a form section or canvas section as you navigate data.

The only solution is to handle localpaging in the cloud server OData call as I previously described.

You can't turn off pagination on a grid section in the web client -- that's how it works -- it retrieves a page of records as requested. Similarly a form section only has one record in view at any one point in time, it does not retrieve all records and send them down the line to the web or mobile client.

The only other solution if you must use stored procedures would be to recognize some special macros in the call and substitute them with the Fetchposition and Fetch size e.g {fetchposition} and {fetchsize} then you would need to specify these are parameters to the stored procedure call. Your stored procedure would then have to handle what was selected and returned back. The problem with this approach is that you would need to edit all your stored procedures.

It seems to me that localpaging is the best approach which would happen when a VT has the localpaging=1 property specified.

If this is required now (v1.3 has already had a lengthy beta) then it will delay the release for a week as it will need implemented and re QA'd.

Note: Just to clarify this is only an issue with VTs that call stored procedures. It would be exactly the same issue no matter what platform was being used be it .net or Java. Large databases should be queried for a subset of data not all the data at once. This is what pagination does in Lianja to reduce network and memory usage on the server so that it scales properly.

hmischel@diligentsystems.com
2015-01-06, 08:18
Hi Barry,

localpaging sounds like an excellent solution.

Since no one else has been asking for until now, I would not want your team to deviate from the roadmap for this.

I have a current workaround for me testing that is putting my result set into a live table with a primary key.

I will submit an Enhancement request and keep a close eye out for it.

Thanks.

Herb

barrymavin
2015-01-06, 08:37
Hi Herb

Ok I will look into it in a point release after v1.3