Has anyone had experience using Views in servoy.
I am attempting to optimize a solution where the majority of data is stored in a 300 column table. Most of the data is answers to interview questions and therefore logically sits in a single table. This also makes online offline syncing between smart client and servoy offline a lot easier.
My issue is that my index pages and search pages, which use say only 10 key fields, appointment time, surname, client reference, our reference, firstname, date of birth etc, seem to cause a lot of slow down as Servoy insists on loading all the row data in the list view, rather than only the columns that are being displayed. Given each record may be say 100K in size and servoy loads in 61 rows by default, the can be expensive on WAN transfer times and i think is one issue in slowing my solution down.
I would consider one easy potential fix would be to create those 10 columns as a view and then servoy would only display and transfer that data X 61 and then when i actually do a find on the record i want to edit, would it load in all the data.
My question is, do views actually exist in memory on a mysql server or are they essentially adding a nested SQL query to mine each time i do a search, and if the latter is the case, would this actually lose me more performance than i gain, or would the time it took to return the data from the main table into the view be negligible and therefore my gain would be worth while as i would be avoiding sending large chunks of unnecessary across the WAN.
The real answer i know is to split this data out and use a 1:1 relationship, but in the first instance if the view idea worked it would gain me a lot of speed with the least effort and then give breathing space to remodel my solution.
WOuld appreciate anyone who knows what actually goes on in a view. I am not joining tables through the view, just would be simply using it to limit the columns returned into the servoy solution when returning data into a list/table form view.
(3.5.10)
David