query wrapped and sort is gone

Questions, tips and tricks and techniques for scripting in Servoy

query wrapped and sort is gone

Postby Hans Nieuwenhuis » Wed Apr 17, 2013 9:26 am

Hi,

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

Code: Select all
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.

Code: Select all
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 ??
Hans Nieuwenhuis
Betagraphics
http://www.deltics.nl
http://www.betagraphics.nl

Servoy Version 7.3.1
Java version 1.7.0.x
Database Oracle 11g
User avatar
Hans Nieuwenhuis
 
Posts: 1026
Joined: Thu Apr 12, 2007 12:36 pm
Location: Hengelo, The Netherlands

Re: query wrapped and sort is gone

Postby ROCLASI » Wed Apr 17, 2013 10:45 am

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:
Code: Select all
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: ;)
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5276
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: query wrapped and sort is gone

Postby Hans Nieuwenhuis » Wed Apr 17, 2013 10:55 am

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.

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

Regards,
Hans Nieuwenhuis
Betagraphics
http://www.deltics.nl
http://www.betagraphics.nl

Servoy Version 7.3.1
Java version 1.7.0.x
Database Oracle 11g
User avatar
Hans Nieuwenhuis
 
Posts: 1026
Joined: Thu Apr 12, 2007 12:36 pm
Location: Hengelo, The Netherlands

Re: query wrapped and sort is gone

Postby ROCLASI » Wed Apr 17, 2013 12:05 pm

Hi Hans,

Since we are not starting a discussion here ;)
Hans Nieuwenhuis wrote:<edit> : 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.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5276
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: query wrapped and sort is gone

Postby rgansevles » Tue Apr 23, 2013 10:58 am

Hans Nieuwenhuis wrote: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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1864
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to Methods

Who is online

Users browsing this forum: No registered users and 10 guests

cron