Thanks for the explanation Anthony. Users with large datasets should be aware of this.
Regards Johann On 7 October 2013 13:59, Anthony <abasta...@gmail.com> wrote: > I don't think it's the grid specifically that's doing that. When the DAL > does a query with limitby, if no orderby is specified, it automatically > orders by the primary keys of the tables involved, I assume to ensure the > same query returns the same results each time (especially important for > pagination). There is an undocumented "orderby_on_limitby" argument of > .select() that controls this behavior (it defaults to True). > > Anthony > > > On Monday, October 7, 2013 6:41:54 AM UTC-4, Johann Spies wrote: >> >> This code in web2py: >> >> fields = [db.rissue.so, db.rissue.sn, db.rsubject.subject] >> query = ((db.rissue.id > 0)& >> (db.rsc_joern_link.rj_id == db.rissue.uuid)& >> (db.rsc_joern_link.sc_id == db.rsubject.uuid) >> ) >> data = SQLFORM.grid(query, >> fields = fields, >> maxtextlength=120, >> editable = False, >> deletable = False) >> >> results in the following backend query: >> >> SELECT rissue.so, rissue.sn, rsubject.subject, >> >> rsc_joern_link.id >> FROM rsc_joern_link, rissue, rsubject >> WHERE (((rissue.id > 0) >> AND (rsc_joern_link.rj_id = rissue.uuid)) >> AND (rsc_joern_link.sc_id = rsubject.uuid)) >> >> ORDER BY rsc_joern_link.id, rissue.id, rsubject.id >> LIMIT 20 >> OFFSET 0; >> >> which is not a serious problem when working with small datasets but the >> added 'orderby' clause causes serious delays when when working with large >> datasets. >> >> As a workaround I am nou using 'orderby = db.<table>.id in all cases to >> prevent web2py adding what I did not mean. >> >> On a small dataset (3543 records) the effect was: >> >> SELECT rissue.so, rissue.sn, rsubject.subject, rsc_joern_link.id FROM >> rsc_joern_link, rissue, rsubject WHERE (((rissue.id > 0) AND >> (rsc_joern_link.rj_id = rissue.uuid)) AND (rsc_joern_link.sc_id = >> rsubject.uuid)) ORDER BY rissue.id LIMIT 20 OFFSET 0; >> >> >> >> 1.58ms (this was with my orderby) >> >> SELECT rissue.so, rissue.sn, rsubject.subject, rsc_joern_link.id FROM >> rsc_joern_link, rissue, rsubject WHERE (((rissue.id > 0) AND >> (rsc_joern_link.rj_id = rissue.uuid)) AND (rsc_joern_link.sc_id = >> rsubject.uuid)) ORDER BY rsc_joern_link.id, rissue.id, rsubject.id LIMIT 20 >> OFFSET 0; >> >> 6.66ms (this was with web2py's interpretation of my query) >> >> >> I have not tested this but I suspect the multiple field orderby's only >> happens when joins are involved. >> >> Regards >> Johann >> -- >> Because experiencing your loyal love is better than life itself, >> my lips will praise you. (Psalm 63:3) >> > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > You received this message because you are subscribed to the Google Groups > "web2py-users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to web2py+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/groups/opt_out. > -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
