Servoy 2019.06 Launch Part 1 – Servoy Tech Webinar – No. 59

We’re doing this in three webinars to kick off this launch. The version of Servoix 2019-06 is currently in a public beta or a public release candidate. That should be finalized by the end of the month, which is next week. So we’ll be looking forward to that. In the meantime, you can download it. And this webinar is here to kind of highlight what’s new, although we cannot cover everything. You have to check out the release notes, but these are more of the enhancements and features less of the maintenance and trivial stuff. Today we’ll be covering, while I’m calling it, Founset Optimization, but it’s a feature that we introduced in 8.4 called View Founsets. And there’s an update on this feature that we told you we were planning for this release. And now we’re happy to show it to you. Essentially, this is going to be a way to optimize how Servoix talks to your database. So the default way that Servoix generates queries to your database is optimized more for a user kind of browsing through an application. So it’s generating lots of small inexpensive queries. However, in certain cases, you don’t want to generate a lot of small inexpensive queries. You’d rather generate one, perhaps larger, more expensive query. So that’s the main use case we’re going to look at in the demo. So let me jump into the demo. And of course, after the demo, if you are not totally bored, you can stick around and we’ll do a quick overview in the slides. Let me go here. Okay, we’re looking at an application which is showing a list of records from the primary table, which is sort of line items on an invoice or an order details. And pretty much every column on this grid is relating out to another table. So, and that could be several hops away. What I want to do is show you the performance data. In fact, what I ought to do is clear it because we have quite some stuff in there and relaunch this. So when that form loads, we can look at the queries that are generated from, from Servoyto the database. And you can see that already right off the bat just to show that grid, there’s quite some queries. There’s more than 100. And that’s because again, each one of these columns is really kind of a select from that table where the primary key equals the foreign key kind of thing. And that’s happening per row. And so you’ll also notice that if I, well, this is a, I’ll push the arrow down key on my grid and kind of blow through a lot of records. And as I come back here and reload the performance statistics, now you can see that I have a few hundred queries that were generated just from my user scrolling down. Now in this case, the queries are all very fast performing because this is a local database which has only a few thousand records in it. But you can imagine that if there was even, you know, queries that were taking five milliseconds or something, then you start to see the performance and feel the performance impact on the screen. So those of you that have a database background might say, hmm, it’s not really smart to generate a bunch of those queries. Why don’t you do it in a single query and join instead of making them all wear clauses? And so that’s exactly what we want to do. However, to do that, we need to, we need to sort of change the way that Servoy is issuing those queries to the database. Easiest way to do that is to make our own query. So what I want to show you is the next, the next screen here, and I meant to clear the statistics. Let me do that. And probably if I go in here and just enter, I think it reloads. Yeah, so let’s have a look at these statistics. So now you can see I get the same screen with only one query, but the query here is quite a bit more complex. It does all the joins and it does cost three milliseconds. But that’s actually faster than all those submillisecond queries added up to be. And that performance gain will be multiplied as you scroll through the grid or something. And you can see that if I, again, push my down arrow here, the scroll bar I can scroll through this grid quite quickly. And I scroll through several hundred records. And if I reload this, you can see that there was really only a couple queries that were issued now. It just took the same query and sort of reissued it for the next chunk size. So again, we’re greatly reducing the number or the volume of queries that are being issued. So these forms look pretty much identical. They behave fairly similar except that the second form will perform faster than the first form. Let’s take a look under the hood at how it was done. So we introduced in the in Servoy8.4 about six months ago or so a feature called view found set. And the view found set was a new type of data binding object that allows you to construct a Servoyfound set from a query, a custom query. But you had to define the data source sort of programmatically everywhere through code. What I’d like to show you today is the update on that feature, which is how you can define that data source at design time in the IDE and connect Servoydata bound objects such as forms, and values, etc. to that data source. So here I’m going to expand my data sources node on my example solution. And there is a new node that is not there prior to 20 1906, which is the view found set. And you can see I have one data source defined. The way that I made that is I right click and I said create view found set. you give it a name. And you go in and you start adding columns just like you would maybe define a database. So what I did here is I made these columns to match up with a query that I’m going to run to sort of fill that database. You’ll notice that the a lot of the tabs that you would see down at the bottom here that would be on a regular sort of physical database are gone. But we do have the events tab and there’s one event that is supported. And that’s the on load event for a found set, which is basically the first time that found set is loaded. It’s going to trigger this event and you combine it to your own handler. And in this case I pass that off to a method I’ve defined here called load order details. Now I don’t want to get too much into too much detail about how to use the query builder. We have a whole other webinar topic on that in our archive. So if you’re new to Servoy you want to check that out. But basically what I’ve done here is I say I’m going to query the order details table. And I’m going to start adding in all the joined columns. I’m going to join order details to orders, order details to products, products to suppliers, categories, etc. Each one of those was generating separate query per row. Now it’s going to just all go into a single query. So finally this line of code is where we sort of say that the query is now registered as a view found set data source. And we call database manager get view found set. We give it the name of the data source, which should match the name of this data source source that we created over here in the solution explorer order details view. Here I call it order details view. If those two names are the same, then when you request something which is bound to order details view, I’ll say, ah, you registered that query. I’m going to use it. And that’s how it works. This last argument is a Boolean. If you want to register it, you can use a view found set which is unregistered. Meaning you maybe you just want to use it for a one time program out of use or something. It doesn’t register it as being a data source that other objects combine to. But typically you want to set this to true. We’ll talk about the broadcasting in a minute. But I want to go back to the solution and talk about show you the broadcasting and then we’ll go back to the code. What I’m going to do is show you what happens if I’ve changed the underlying table that the view found set is generated from. So I’m going to go over to my default found set and actually my product table. That way, default found set. And I’m going to change the quantity for, I think we got to find one that’s showing up in both. I think we have this one is in both, right? No, that’s a different one. I should have put the order ID on the grid. Tell you what, we’ll do something that’s a lot easier to see. We’ll do a product name. So I’m going to click on tofu. I’m just going to change the name to a bc xyz, save it. Now when I go back to my default found set, you can see that everywhere that I’ve used tofu and I’ll make this a bit easier. Well there’s two tofu products. The first one, it’s updated because Servoy by default broadcast data changes and all sort of connected found sets will receive those and update themselves. And you get that for free, you don’t have to write any code, you don’t have to enable anything special. It just works that way out of the box. What happens when I go over to my view found set, which was generated from a custom query and I’m looking for my tofu one, should probably have lined one up. Well let’s do one that I can already see. So I’ll go and do chai and do the same thing. And you can see here, I thought I had chai up here. No, there it is. That it was updated. The reason it was updated is because I have enabled the data broadcast. I’m going to sort of comment these out. In fact that’s for the primary table. This is the product stable. I’m going to comment that out and I’m going to sort of reload this by researching it. So now if I were to come, can we get one from the top here? Let’s do this one. If I come into the product stable and change this one, you can see that down here the product name has not updated. So you have to enable broadcasting for whatever components of your query in order to get that broadcasting. The reason we do that is that you could imagine that for this query there’s lots of source tables that could go into the query and we don’t want to assume that you want to receive broadcasting all that because there starts to become a sort of a performance consideration. So we leave it up to you meaning it’s your responsibility to say in the code here. Yeah I want to enable broadcasting for the products table. That means when I change the product stable any found sets based on this data source, it was generated from that custom query, we’ll receive the data broadcast and requeary. But you don’t want your data source to requeary constantly if it doesn’t need to. So that’s up to you to change that. Finally I want to talk a bit about the limitations of the view found set. It is sort of a view into the results of that query and it can dynamically page in records and stuff but by default you can’t do simple things that you would get in a regular found set such as searching and loading records based on another query and that sort of thing. So you might have noticed that I was able to quickly type in tofu here or Tokyo. All right, I thought that was one of them. I’ll just put in another product. You can see I can easily type in searching the products table. That’s the only one I’ve enabled and that’s using the SVY search module which kind of gives you that for free. I could put in another customer name here and you can see that gets this column. But if I want to do that over here, now I’ve enabled it so I’m going to put in tofu and you can see that I got long life tofu and the other tofu that I modified but I had to do something for that and I want to show that to you as well. So by default you can’t put a view found set into find mode or you cannot load records based on a query but I’m going to open this form. By the way, I forgot to show you something which is kind of important on this form. You will notice that when I made this form, I have to choose a data source and it just shows up right here under view found sets. That’s kind of one of the missing links here. This was not available in Servoy84. You had to do it programmatically with the solution model. And this is true of any data bound object like if I create a value list and I want to say this comes from tables. You can see view found set shows up here, same for relations, etc. Anything that’s data bound is now able to talk to that view found set. But I digress on the action event of this text box which is bound to a form variable which captures the input. I had to do something to sort of re-queer your filter the results of that. And what you can do is you can ask for the query object of a view found set. And then you can modify, it’s like a clone. It’s not the original. But you can modify it and here I just kind of tucked in the where the product name, case and sensitive is like the search text the user entered or the customer. And those are both joins. The customer name is case and sensitive like the search text. I was printing out the SQL here to debug it. And then I re-registered that data source. You also have to tell the form to load records again, which is down here. If they cleared it, what I did is I called that original method which loaded it, you know, reloaded it without any conditions basically. But again, it’s this get view found set and then re-registering it, which is what I did. But what I did is I took the existing query and I modified it and then I re-registered it. I didn’t have to start again from scratch. So that’s kind of the same is true if you wanted to re-sort it. I mean, you can sort kind of the in memory way where it sorts what’s already cached. But if you want the sort to go to the database, you probably want to go in and get the query and change the sort columns programmatically on the query and then re-register it. So that’s kind of a, I think especially for existing Servoyusers that are used to all the stuff you get kind of for free on the found sets out of the box. This one, although you get more control, you have the responsibility to kind of change things yourself. So it’s important to know that. I think I covered all the things I wanted to demo just to give you a quick overview. We’ll go back to some slides. Excuse me. So the main use case here is optimizing performance and the use case I gave you replacing something which generates lots of small queries into something that generates fewer but more complex queries and getting the same sort of user experience as a result but better performance. The other example would be if you wanted to maybe make a screen which was showing a bunch of aggregates and in order to do that, you may want to write that query yourself. Well, now you can, you can write that query yourself and it’s a lot easier to sort of make that a data bound object than it is than it was prior. So that’s another use case. The way that it really works is you create the query. You do the things to the query that you want. You set what’s in the result and what’s in the result must match what’s in the predefined data source that you did at design time. And then you call get view found set with the argument here to register and that binds it in so that everything can register. So everything can bind to it. Again, that matches up with the one you created at design time when you did new view found set data source. Excuse me. That’s a bit of a duplicate here. Excuse me. Finally, you want to do things to enable data broadcasting because by default you normally get data broadcasting for free and sort of subtracts for you what needs to receive a broadcast and what doesn’t. But in this case, you kind of have to tell it. Again, you have the control to make this any way you want. Therefore, you also get the responsibility to make sure it’s updated via data broadcast if that’s something that you want. Something that I didn’t show in my demo, but I put here as an example on sort of the last one enabled broadcast with flags is that you can also extend the monitoring that you want to go even deeper to do things like monitor aggregates. So the example that I was talking about, unfortunately, I don’t have something to show you would be if you created sort of an aggregate view, say you wanted to see that my total sales by products or something. And then while you’re looking at that view, a customer service rep went and submitted a new order with more products being ordered, which would affect your total sales aggregates. You can actually receive that data broadcast by monitoring that data source deep enough to get the aggregates. And then you would actually, your aggregates would update because somewhere on that table, you know that it’s going to affect the aggregates like if they would go up because you added a new order. So it’s nice because you can actually construct these pretty complex aggregate type screens and they are actually connected to the data live rather than just a snapshot that’s offline.

Close