Hi all,
I have a form with dataproviders with two scenarios.
placed directly the value field from a relation based on the id field on both the tables.
placed the same id field on the form and attached a valuelist which will display the actual value against the ID.
Which will be the best strategy in terms of performance?
Thanks,
Excellent question. The answer could depend on the size of the table that holds the id’s and actual values, and what is used in the relation.
- placed directly the value field from a relation based on the id field on both the tables.
This will issue SQL to your DB using the join criteria that is used in your relation to pull back the name from the ID table. If the join criteria is pretty simple, this should be very fast.
- placed the same id field on the form and attached a valuelist which will display the actual value against the ID.
Servoy must first build the value list, so if that ID table is huge, that could take a little extra time
The best way to find out, is to do a simple test. Build each scenario, and test it one at a time in Developer. Before the test, open the Servoy Admin page in localhost and go to the performance statistics page. Click clear at the top so you start fresh. Then go to the test form. Then go back to performance statistics page and view what SQL queries were used to build the form and see how long and complex the queries are. From that info, you should be able to determine which is the best approach for your situation.
Thanks scott for your response.
I went for the 1st. method. Yes, it seems to be faster than the 2nd. method.
I have also tried with other method, by creating a stored calculation for that field in the table and using the same in the form. This is seems to faster than the 1st. method.
Thanks,
Absolutely option 1!!
try it in table-view and see the difference.
with option 1 Servoy queries the data in larger junks.
with option 2, every record is also a new query!
Harjo:
with option 2, every record is also a new query!
Not always, depends on how you create your valuelist. I make some of my valuelists of the type ‘Customer values’
And I load once the values into the valuelist (unless the valuelist need to be reloaded)
The only problem you can have here, is that when user A adds a record to the database, that user B will not see this record until the solution is restarted.
If your performance is more important than seeing all records, then you should consider this.
For example I have a valuelist/table with countries. This table is not being changed that much.
In that case I prefer to keep the valuelist with countries in memory using a customer valuelist in stead of a valuelist based on the table.
martinh:
Harjo:
with option 2, every record is also a new query!The only problem you can have here, is that when user A adds a record to the database, that user B will not see this record until the solution is restarted.
If the new record is databroadcasted maybe you can solve this situation using the onDataBroadcast event of the solution and doing here your favourite strategy to reload the ‘Custom Values’ valueslist.
victor.rojo:
martinh:
Harjo:
with option 2, every record is also a new query!The only problem you can have here, is that when user A adds a record to the database, that user B will not see this record until the solution is restarted.
If the new record is databroadcasted maybe you can solve this situation using the onDataBroadcast event of the solution and doing here your favourite strategy to reload the ‘Custom Values’ valueslist.
You could be right Victor, but I’m still on Servoy 4.1
Infop:
I have also tried with other method, by creating a stored calculation for that field in the table and using the same in the form. This is seems to faster than the 1st. method.
FYI, avoid stored calculations whenever possible. You don’t have a lot of control over how often then run (or if they will run at all). I use them only if the value is being used in an Aggregate (count, sum, etc), since the value must be stored in order to be used with an Aggregate. Otherwise I try to avoid them.
As a general rule of thumb I use option #1, but the main idea here is for you to learn how to analyze your own app. Using the Performance Statistics and Profiling pages are very helpful. You should periodically check your app in those pages and make sure there aren’t any long or expensive queries or event firing multiple times, etc. If you wait until your app is completely built, then its a lot more work to fix those types of problems.