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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
