Servoy 8.4 part 3: Enhanced performance

Hi, welcome everybody and most welcome to webinar number 50. So this series has been now going on for more than two years. About two years ago we promised you would be doing one every two weeks and we’ve been doing nearly every two weeks. So quite from when maybe Sean was on holiday or on deep travel, but nearly every two weeks we had this great series of tech webinars for developers only. And we’re celebrating number 50 with the third installment of the launch of Sir Roy 8.4. So I’ll welcome you to grab a couple of your favorite drink and join me for a similar time having a wonderful fresh brewed coffee that I just milled and put through a filter and back into filter coffee. How about you, Shah? The kind of coffee you drink. Felt it. Excellent. I wasn’t as presser for a while, but I’d back to filter. You can drink it more during the day I noticed. Alright guys, welcome all. Feel free to ask any questions or post comments on the questions channel. We are here to answer those questions. This session will be recorded. Actually, we already posted part one and part two on both our website and on YouTube. So depending on where you prefer to go to visit those webinars, feel free to check the recordings of one and two and we will post part three later today as well. Today we’ll be looking at performance in Sir Roy 8.4. So far the demo gods have been positive with us. One part one and two went well. So let’s see how it goes this today, Shah. I’m curious to see. Thank you. And it’s for jinxing it. Okay, let’s get started. As the tradition goes, we do a demo first, then we do some boring slides because the demo is the fun part. What I’m going to be showing today is a feature which is new in the 8.4 release candidate. It’s called the found set. And the main reason for this feature is to allow developers to control how the application performs while accessing access and data. So it’s up to you whether or not and how and when to use this feature. I’m going to show you how it works and explain what it does. For that we have an example solution that I will launch. Okay, so we’re looking at a grid here which is a default found set, bound to a form the same way you would set up any other form. In this case, it’s looking at the order detail records in the example database. However, we’re using the relation features to bring in related data as we need. So in this grid, I’ve placed related data providers so order details to products to categories. That’s two tables away. Order details to products is one table away. Supplier is also two tables away because it goes from the order detail to the product table then from the products table to the supplier table. Also order details to orders to customers and order details to orders to the sales rep. So in one table, this is one of the features of Servoythat’s been in the product for many years and it’s very powerful and it allows users to easily flip through related data without writing any code. In this case, the performance is pretty good. You can see I’m scrolling through all of these records and there’s not much lag. What I want to do is go to the performance statistics available on the admin page and take a look at what kind of queries are generated. For that, you can go to local host and developer, AD80, Servoyadmin and then you click the database performance link and it will show you statistics for an individual database. So what we can see here are all the different queries that Servoyjust sent to the database when I showed this grid. You can see that there is the main, the main found set query which is loading the primary keys from the order details table. Then what it does is it generates that the relations for each one of those related tables, it’s generating all of those queries. There’s an aggregate in here. You can ignore that. That just kind of got mixed in there. But what I would point out here is that there’s many queries being issued. So for each one of these relations, it’s not doing a join, it’s doing a where. you can also kind of where fk equals pk if you will. It’s the same part of the join that you would have if you did a join like the on clause, but it’s just in a where clause. And there’s a reason for this. The reason for this is that by default, Servoyis optimized for users browsing data. So I might be looking at an order and then I say, oh, I want to look at the customer information for that order and I’m loading the related record kind of separately. It’s not meant to be looked at in sort of aggregate, if you will. So the result of this is that it’s generating just when I scroll through a few dozen records there, it’s generating literally hundreds of queries to my database. This is not a bad thing. This is primarily a good thing because it makes the application easy to develop and intuitive to use. However, there are situations where you may want to do something which is more efficient. So you can see that the time on these queries is basically sub millisecond. It’s a local database on my laptop with just a few thousand records in it for the order details. So consider that this is a database which may be remote to the application server. Maybe has millions of records. So we add in that little bit of latency. All of a sudden if these queries took even 10 milliseconds, we would get a lag time of a few seconds just to kind of scroll through the table. So you can see how that compounds quickly based on the sheer number of queries. So going back to our example, what we’ll first what I’d like to do is clear the statistics. So now we have a fresh look. Go back to another example which is built with a view found set. You can see that the data providers here, the columns on the grid are the same. We’re bringing in data from from those related tables. However, when I look at the, when I reload the page here, you can see that only one query was issued. And instead of doing lots of queries with the where clause is doing a join clause. So you can see all the joins sprinkled in here. Now if I were to sort of scroll down and force it to load more records, come back here and reload this. Well it seems I haven’t exceeded my cache yet. Go a little bit farther. Try this now. There we go. So the query count went up a couple times. So now we’re loading in much more information or the same amount of information with much fewer queries. The query itself is more expensive because it’s more complicated. But in a real world situation, this will probably perform better provided the databases indexed for it and everything. So let’s go under the hood and take a look at how that was accomplished. For my example solution here, I have an on solution open event handler. And when we go into that, I’m calling this load order details method. And it starts off by creating a query against the order details table. And then it is joining in all of the, I’ll minimize that, it’s joining in all of the related tables here using the joins and the relation identifier. I’m going to sort of skip over the query builder part here because we’ve done webinars in the past that are comprehensive and focused just solely on using the query builder. So I’ll kind of skip through that, but it’s very easy to join in that data without having to write any SQL yourself. But the important part to notice is down here, once we have sort of formed the query the way we want it, we call database manager get few found set. And that’s the new feature that’s in 8.4. And what that returns is a found set object, which is kind of a subset of the regular found set. It is a different class. You can see if I mouse over this and get the doc the return type. Oops, I’m sorry, lost it. Try that again. The return type here is a real view found set class. And if I ask for code complete, you will see that the methods available on it are it has some of the methods of the regular found set, but it’s limited. It doesn’t have all of them. So once I have my view found set, I’m going to skip over this broadcasting part for now and register it in the client session. And you have to do this in order to make it available as a data source so that you can have data bound forms and other data bound objects to be able to work with it. Not call register view found set. This individual found set. So for example, you could take this and set it directly as the data source for the table component, the simple table component, for example, you can just say dot found set equals and then your view found set and you’re done or if you want to do something programmatically with that found set, you can just stop right here and start using the found set as is. But if you want it to be a data source to which you can bind other objects, you need to call this register method right here. Finally, in this release of 8.4, we don’t yet have the data source at design time. So the data source must be created dynamically this way and therefore it must be set as the data source for the data binding in a form or any other object via the solution model. So this here solution model, I get my form and I set the data source to be this data source so that when that form shows, it will load all the records from that data source. In the next version, we’re going to add support for design time data source of this type of view found set so that you could just start in the designer’s day. Here’s my form, it’s bound to this view data source that I’ve defined and then it runtime all I have to do is call the query or set the query. But for now we have this step. This is the same way we released the in-memory data source initially. If you guys might remember those of you from a while back, we released it first via solution model and after that in the designer. So that part will be coming. So once we’ve set this, as soon as that form shows, then all of the data will load and will be bound to that data source. The other example that I’d like to show, which I fear is not quite working but we’ll sort of walk through it and then we’ll discuss what, how to do what the impacts are. So when we’re looking at this data source, there’s a difference between the one that’s based on the view found set and the one that’s really coming from data where everything’s joined. Because in this one, we really know how that is loaded and we’re able to bring data broadcasting to it automatically. So for example, if I’m looking at this record with the KSO, Cabrales product and I come over to my product table in another form and I modify, let’s say, we add a description and we save it. If I come back to my regular found set based form, you can see that it’s automatically updated and that’s true not only in this client session but Connecti Client. That’s something that Servoyhas always had you get for free with writing any code. On this form found set, we don’t get the data broadcasting by default. We have to enable it. So there’s a way that through the API that you can enable various aspects of the data broadcasting and still have control over performance. So if we go back to the code example here, you can see that I have enabled data broadcasting for the primary table and for the related product table so that if any changes were to happen to that during runtime that we’ve now registered sort of a listener that will, that’s bound to this view found set and will then cause the view found set to re-queer its data if those changes happen. And you can be nuanced about this. In this case, I’m listening for basically column changes on the target table but if you you look at the API for enabled data broadcast, you can see that there’s a second argument for, and there’s a constant you can put in there for some flags you can set. And you can set multiple of these and join them together to get a more nuanced approach. So if you really want to fine tune performance, for example, I may want to listen for monitoring join conditions so that if I actually change which record it might be bound to. So for example, if I’m looking at the order details and I change the supplier of one of the products, well then you know that that’s actually a change in the join condition and one of the foreign keys and this will listen for that and it will still update. So if you don’t put anything in then you’re really listening for everything but you can sort of fine tune and say I just want to listen for column changes or, you know, deletes or whatever it might be. So you have some control over that here. Because I’ve enabled this, actually that data broadcast part should work but I think there’s a small bug in this remembrance is still a release candidate that is already fixed but I didn’t apply the update. I didn’t want to do it minutes before the demo. So maybe we can make a point of trying it out in the, trying out the update at the end of the webinar. But that’s just the other keep in mind here. Unless you specify data broadcasting, your view found set is kind of offline. It’s all alone but you can hook it up this way and then it will receive broadcasting. That’s kind of the gist of the features. What I thought it could do is just take you through a quick overview and try to tie it all together. So again, the way that you use this is you create a query which is supplying the results of your found set. So in my case I just did a bunch of joins because that’s a nice way to show the performance improvement. Taking this to step further and doing aggregates really can save on performance. So if I were looking at say product aggregates, so total sales and quantity sold and that sort of thing of the product stable, in that case the aggregates do become more expensive to query in sort of that relation default found set way, much better to let the database do it for you in a more complex query. And again the overall goal of this is to take what is normally a small many queries and change that into smaller amounts of larger queries or more complex queries and the basic use cases again performance. So what I did is I first define a query then I create a new view found set of that query by calling this new API called database manager get view found set. Once you have the found set you can use it however you want but if you really want it to be data bound you have to register it as a data source. So you call database manager register view found set and today you have to set this in the form via solution model. There’s no design time data binding to view found sets of view data sources. We’ll be bringing that in a subsequent version and then you’ll be able to just design a form that’s directly hooked up to what will be the results of a query. Really remember that you don’t get data broadcasting built in you have to enable it. It’s pretty easy you just tell it which data sources you want to enable data broadcasting for. You have some nuance over how you control that you can monitor specific things like aggregates or join conditions or say I just want table or sorry I just want column monitoring. So you get you definitely get to the find green control over that. So that’s that’s really where we’re at now as I said what’s next is in the designer will will have this available so you can make forms directly against the query and we’re interested to hear your feedback and what use cases you would like to try this out on. So please have a download and give it a shot. I’m going to leave some useful links up here while we take some questions and then after the questions we’ll put in a point for an edit and then we’ll maybe try something out after that. Yeah and do we have any questions? Let’s see if any questions are a topic. Well it looks like everybody either understands this completely or is flabbergasted or a combination of both because I’m not seeing any questions so far popping in. I think it is good to point out that if you’re new to Servoy then you are probably not going to need this in quite a while. So this is really when you are building pretty large and complex applications and you’ve deployed them and you’re starting to to fine tune your performance then this is typically capability that you’ll need. I think it was requested by some of the larger ERP vendors running Servoy not shown. Do you know anything about that? Yeah that’s correct. And you’re absolutely right. This is kind of an advanced feature if you’re new to Servoy you probably want to leave this alone and just know that it’s nice to have in the future. You really want to optimize performance but yeah we had some ERP vendors requesting this because they’re hitting really large tables with complex join situations and if they’re doing default found set stuff you can see that many hundreds of queries will add up and give a performance hit. you

Close