Performance Impact of utils.hasRecords()

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Performance Impact of utils.hasRecords()

Postby Bernd.N » Tue Feb 23, 2016 7:47 am

We use utils.hasRecords(project_elements_to_supplier_invoices) to test if an invoice has been already booked against a project element.

Am I right that this will basically fire a SQL COUNT(*) to decide the hasRecords-question?
Because I wonder why I do not see such a COUNT() in the SQL performance log on the AppServer.

I am in general curious about the performance impact of the hasRecords() function.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 486
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Performance Impact of utils.hasRecords()

Postby mboegem » Tue Feb 23, 2016 12:32 pm

Bernd.N wrote:Am I right that this will basically fire a SQL COUNT(*) to decide the hasRecords-question?
Because I wonder why I do not see such a COUNT() in the SQL performance log on the AppServer.


Your assumption is not correct.
Servoy will fire a full SELECT as if you were requesting the related foundset.

So this:
Code: Select all
utils.hasRecords(project_elements_to_supplier_invoices)

will have the same backend impact as:
Code: Select all
var _fs = project_elements_to_supplier_invoices; var _bHasRecords = _fs.getSize() > 0


Will the 'utils.hasRecords' function have to much impact on performance? I wouldn't say so.
Most of the times you'll use this function to check if you need to perform an action on / read data from the related foundset.
As executing 'utils.hasRecords' will make Servoy cache this particular foundset, the query won't be executed twice in the back-end.

Depending on the relation you could prevent he application server to execute the query.
So in case of a relation 'project_to_client', you would have the client_id as a FK in the table projects.
Code: Select all
if(client_id && utils.hasRecords(project_to_client)) {
   application.output('This project is for a client!');
}

The above code will already stop the evaluation in the 'if' statement as soon as the client_id doesn't contain a value.

Hope this is clarifying things.
_____________________
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance SAN Developer

Image
User avatar
mboegem
 
Posts: 1395
Joined: Sun Oct 14, 2007 1:34 pm
Location: Hoofddorp, The Netherlands

Re: Performance Impact of utils.hasRecords()

Postby patrick » Tue Feb 23, 2016 1:49 pm

Depending on the size of your table and the relation criteria, a count could be really expensive... That's why you have a special databaseManager.getFoundSetCount(foundset).
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3633
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: Performance Impact of utils.hasRecords()

Postby Bernd.N » Tue Feb 23, 2016 1:55 pm

Thanks to all for the clarification!
Now I see why there was no server log entry, as Servoy looked at the cached foundset.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 486
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Performance Impact of utils.hasRecords()

Postby juan.cristobo » Wed Feb 24, 2016 11:01 am

About this (and perhaps off-topic, sorry...), is there any difference between utils.hasRecords() and databaseManager.hasRecords()?
Juan
Madrid (Spain)

Servoy 7.4.x - MySQL / SQL Server 2008-2016
Windows 10 Pro
juan.cristobo
 
Posts: 183
Joined: Thu Apr 19, 2012 9:12 am

Re: Performance Impact of utils.hasRecords()

Postby mboegem » Wed Feb 24, 2016 12:02 pm

juan.cristobo wrote:About this (and perhaps off-topic, sorry...), is there any difference between utils.hasRecords() and databaseManager.hasRecords()?

No.
Actually utils.hasRecords() was deprecated for a short while, but as the 'databaseManager' node is not available in 'calculation-mode' the deprecated-state has been reverted.
In general I always use databaseManager.hasRecords(), except for calculations.
But I try to stay away from related stuff in calculations as this executes a whole lot of queries which could have a huge drawback on performance.
_____________________
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance SAN Developer

Image
User avatar
mboegem
 
Posts: 1395
Joined: Sun Oct 14, 2007 1:34 pm
Location: Hoofddorp, The Netherlands

Re: Performance Impact of utils.hasRecords()

Postby juan.cristobo » Wed Feb 24, 2016 12:05 pm

Ok, thanks for the info!
Juan
Madrid (Spain)

Servoy 7.4.x - MySQL / SQL Server 2008-2016
Windows 10 Pro
juan.cristobo
 
Posts: 183
Joined: Thu Apr 19, 2012 9:12 am


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 4 guests

cron