query wrapped and sort is gone

Classic Servoy
1

Hi,

I use the query below to load a foundset.
When the foundset is loaded, the sort is not as requested.

query = 'select ONDHCFI_ID ' +					    
        'from ondh_conf_items ' +
	'where BEDR_BEDR_ID like ? ' +
	'  and ONDHCFI_COURANT = 1 ' +
	'  and (ONDHCFI_NR like ? ' +
	'   or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ONDH_CONF_SL_REGELS where ONDHCNF_ONDHCNF_ID in (select ONDHCNF_ID from ondh_configuraties where ONDHCNF_NR like ?)) ' +
	'   or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ONDH_CONF_SL_REGELS where ONDHCNF_ONDHCNF_ID in (select ONDHCNF_ID from ondh_configuraties where upper(ONDHCNF_NAAM) like upper(?))) ' +
	'   or upper(ONDHCFI_NAAM) like upper(?) ' +
	'   or upper(ONDHCFI_SERIENR) like upper(?) ' +
	'   or AFLERGL_AFLERGL_ID in (select AFLERGL_ID from afleverregels where VERKOR_VERKOR_ID in (select verkor_id from verkorderregels where verkok_verkok_id in (select verkok_id from verkorderkaarten where verkok_nr like ?))) ' +
        '   or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ondh_cfi_locinfos where vstg_vstg_id in (select vstg_id from vestigingen where vstg_zoeknaam like upper(?))) ' +
				        '      ) ' +
				        'order by ONDHCFI_NR asc';

if I look at the admin performance page I see that my query is wrapped in another query and the sort has gone.

select ONDHCFI_ID 
from ONDH_CONF_ITEMS 
where ONDHCFI_ID in (

select ONDHCFI_ID from ondh_conf_items where BEDR_BEDR_ID like ? and ONDHCFI_COURANT = 1 and (ONDHCFI_NR like ? or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ONDH_CONF_SL_REGELS where ONDHCNF_ONDHCNF_ID in (select ONDHCNF_ID from ondh_configuraties where ONDHCNF_NR like ?)) or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ONDH_CONF_SL_REGELS where ONDHCNF_ONDHCNF_ID in (select ONDHCNF_ID from ondh_configuraties where upper(ONDHCNF_NAAM) like upper(?))) or upper(ONDHCFI_NAAM) like upper(?) or upper(ONDHCFI_SERIENR) like upper(?) or AFLERGL_AFLERGL_ID in (select AFLERGL_ID from afleverregels where VERKOR_VERKOR_ID in (select verkor_id from verkorderregels where verkok_verkok_id in (select verkok_id from verkorderkaarten where verkok_nr like ?))) or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ondh_cfi_locinfos where vstg_vstg_id in (select vstg_id from vestigingen where vstg_zoeknaam like upper(?))) 

)

Why is this ??

2

Hi Hans,

Servoy has been wrapping our SQL used in foundsets since the a while now (since 3.5 I believe). If you want to use a sort you have to set it (and defer it) using controller.sort().
so in your case you use the following code:

controller.sort('ONDHCFI_NR asc', true); // defer the sort until the foundset is loaded
foundset.loadRecords(mySQL, myParams);

And I can’t help but notice the use of sub-selects instead of faster joins in your SQL.
What RDMS is this? Some old version of MySQL that doesn’t support joins? :shock: ;)

3

Hi Robert,

Thanks for the answer.
I would still like to now why Servoy does this.

On the sub-selects : I use sub-selects al lot.
Let’s not start a discussion here, but joins are not by definition faster then sub-selects.
I am using Oracle 11g, which has a cost-based optimizer.

: this cost-based optimizer will also ( almost always ) result in the same execution plan regardless of sub-query or join.

Regards,

4

Hi Hans,

Since we are not starting a discussion here ;)

Hans Nieuwenhuis:
: this cost-based optimizer will also ( almost always ) result in the same execution plan regardless of sub-query or join.

Good for Oracle. :)
I guess to each their own. I would use a WITH clause and JOINs to keep the SQL easier to read and therefor be less error prone. But that is me.
Although I am not entirely sure Servoy’s foundset.loadRecords() supports the WITH clause yet, I would have to check that.

5

Hans Nieuwenhuis:
Hi Robert,

Thanks for the answer.
I would still like to now why Servoy does this.

Hans,

Servoy does not parse the custom sql, we leave that to the db.
To be able to add stuff to the sql (like sorts, joins) we need something to add these to, so we wrap the custom sql in a subselect.
Unfortunately, this looses the existing sorting.

You could have a look at the query builder introduced in Servoy 6.1, it does not have this limitation.

Rob