Joahnn, yes on large datasets the ordering is devastating, since it causes full table sweep even if not needed.
We must add the orderby_on_limitby paramenters in the book because it impacts large datasets. Shortcut queries that return single values like db.table(id=3) are already optimized to avoid ordering. mic 2013/10/7 Johann Spies <johann.sp...@gmail.com> > 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. > -- 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.
