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.
