|
formspider has three types of DSDs - Table - View - Query and four types of DS fetch modes - Fetch all (max row fetch size) - Paging + rows per page (+ max row fetch size, why?) - infinite scroll (+first fetch size) type A) query as needed - infinite scroll (+first fetch size) type B) query all To be able to code efficient sql I need to know in which of these 12 cases formspider modifies my and what it's doing to it. (Like ORACLE Reports tells the developer what a group by will do to her query) Background: I found out, that one of three detail-siblings of a test form had its query drastically modified into a multiply nested version, with rownum < :A at the inner level and row > :B at an outer level, although I never declared a DS to be paging. This modification, of course, ruined the performance. I'm asking about 12 cases, because I found, that not only the Query Type of the DS affects SQL Text mangling but turning a View into a Query in the DSD also helps in making formspider keep its finger out of my query. |
|
Hi Paul, Formspider does not run stateful. Therefore we cannot leave an open cursor like Oracle Forms does. For infinite scroll to work we have two options 1) Query all, show in batches This provides a consistent view of the data (sort of) like a cursor. So the grid can be editable 2) Query in batches, show in batches There is no way we can do this without using the wrapper SQL, because we are not in a stateful environment (not yet anyway). However, note that this is not a consistent view of the data. While the user scrolls down, she might encounter new rows that should have shown up earlier (because somebody else modified the data and messed up your order by) In your case I'd either use 2) or use paging in the Grid and make Grid not editable. I'd create another dialog for edits. If that's not an option, do a Query All but limit the number of rows to be fetched. Warn the user if her query exceeds the limit, saying that she needs to be more specific in her criteria if she wants to see other rows. Also see what we recommended at the bottom of the infinite scroll tutorial: http://theformspider.com/learningcenter/tutorial-56-how-to-use-infinite-scroll/ Once Formspider runs stateful in verison 1.9, we will have the technical capability to implement a datasource definition with an open cursor. I am not saying we are going to deliver such a datasource definition but it will be technically possible. And then you could build your screen pretty much exactly like in Forms. Kind Regards, Yalim excellent answer, exactly what I saw in SQL-trace!
I'm really looking forward to stateful 1.9 (not for a WebShop, mind you) for desktop work.
(08 Dec '14, 07:02)
dipr
|
|
Hi Paul, I don't think Formspider modifies your Query. In several cases we wrap the SQL of the Datasource Definition around some other SQL, because we have to, but I don't think we ever modify the original query. (The only other thing we do with the original query is to replace bind variables). So I'd be very interested to know more if you spotted a case where we actually modify the query you wrote. If the datasource fetch mode is Paging, Formspider adds the predicates you noticed. It adds the same predicates if the fetch mode is Infinite Scroll and Query Type is Query As Needed. We have to do this to provide the desired functionality. If you didn't use paging in your datasource, I suspect you used Infinite Scroll with Query As Needed. Switch to Query All. In this case, Formspider will read all the rows from the database but send the client these rows in batches as the user scrolls down. Kind Regards, |
|
Correctly guessed, I used "Infinite Scroll" with "query as needed", and I must say I'm surprised to read that this added the paging outer queries! But using "Infinite Scroll" + "Query All" is an absolute nono, when this results in formspider internally reading all lines! We all know the the worst thing an applocation developer can code is a go "last button" (ORACLE Forms course for beginners). But no developer knows if a (errm, weird) user might actually want to scroll down 3000 lines, once per day, because he "has to". Thus: What DS setup in formspider would you suggest for a case, when the users is presented with a list of, say, Orders, sorted by Order_ID desc (prefectly optimize via an index). She sees the 10 highest order_ids, can scroll down "as needed" (!) and even might scroll down hundersthousands of lines (until ORACLE Forms runs out of buffer disk space :) ) This is what they are used from all my forms blocks. Default sort shows important rows at the top, but they can scroll down as much as they feel necessary, knowing that in most cases they are happy with top 10 lines. |
|
So with FS 1.9 and stateful session rolled out (congratulation to that), How are my chances to show the top N "youngest" records in a grid, while giving the user the opportunity to scroll down (AND fetch only "as needed"), WITHOUT "Query all" or pagination? ;) The OracleForms error when the crazy user was scrolling down through zillions of records or an occassional ORA-1555 are acceptable, "Query all" or a paginating userinterface is not. |
|
Hi Paul, The implementation of infinite scroll did not change in Formspider 1.9. So a datasource with infinite scroll in query as needed mode still looks like the correct choice for you. Scrolling down a lot of records in this scenario might be an issue for two reasons: The database table might not be able to handle that many rows for some reason. Or the client side JavaScript might get crushed. In any case, I guess you have to try to find out. Are these rows going to be editable or is this Grid read-only? (just curious) There are a lot of optimisations we can do for datasource definitions if the application runs in stateful mode but we have not done them yet. I don't see us doing them soon on our own any time soon. Our plate is quite full for the next few months. Kind Regards, |
