Servoy makes a simply query a lot more complex

Classic Servoy
1

Hi,
Can anyone explain to me why servoy (or how i stop servoy) taking what seams to be a simple query and making it more complex and very slow to run:

my query is

select id from drcoop_ledger where YYYYMM='201101' group by debtor

my servoy code is

var fs = databaseManager.getFoundSet("reportwriter","drcoop_ledger")
fs.loadRecords("select id from drcoop_ledger where YYYYMM=? group by debtor",[globals.YYYYMM])
application.output(fs.getSize())

In my Sequel Pro browser this query takes 10.4 ms and returns 39 rows

In servoy (5.2.6) it took:

Query_time: 561 Lock_time: 0 Rows_sent: 39 Rows_examined: 201709627

use appointment;
select id from drcoop_ledger where id in (select id from drcoop_ledger where YYYYMM=‘201101’ group by debtor) order by id asc;

and as you can see from the slow query log it seemed to create a query within a query hence the delay i assume.

Is this a servoy issue? is there any way around it?

Thanks

David

2

Hi David,

Apart from the delay (did you check the exact query time in the servoy-admin statistics page ?) your GROUP BY clause doesn’t make much sense.
You select the unique ID column and try to group that on deptor. Since ID is always unique your grouping attempt is pointless. You always end up with the same list of ID’s.
So try to run the query without the GROUP BY and see how that performs.

Hope this helps.

3

Yes I did check it.

09:20:906 1 09:20:906 Custom select id from drcoop_ledger where id in (select id from drcoop_ledger where YYYYMM=? group by debtor) order by id asc

The design of the query is to return me the debtors who have records in the current months debtor ledger which I can then run through in order to pull out the sum and then a list of each item pertaining to them.

if I replace the id with debtor then i get:

fs.loadRecords("select debtor from drcoop_ledger where YYYYMM=? group by debtor",[globals.YYYYMM])

the query Servoy does is
used sql select id from drcoop_ledger where id in (select debtor from drcoop_ledger where YYYYMM=? group by debtor) order by id asc questiondata.length 1

which gives no results but still takes two minutes. Yet in SQL PRO i run it and i get 39 records in 10 ms!

I just find it odd that servoy doesnt simply do the query it is asked to do?

David

4

Servoy needs to select primary keys. So it has to create a “SELECT primary_key” from your query. If you let your query select the primary key from a fully qualified query, the query Servoy fires will look different I think.

5

fs.loadRecords requirers you to select the PK.

Why are you using foundsets to do this? normally loadRecords is used on a foundset that is used on a form as a quick way to display records. And as you notice, it does add some extra overhead because since its a foundset, servoy had to deal with the PK’s for record loading. If you just want to run a query EXACTLY as you write it, use databaseManager.getDataSetByQuery instead, and work with the dataset instead of the foundset in your code.

My general rul of thumb…
Use Founsets for adding, deleting, editing, simple searching, or anything dealing with displayed data on screen
Use datasets for programatic looping if you just need to deal with data, without adding, deleting, or editing.