Thanks Anthony. That explains some of the delays when I am using the grid. As a result of this experience I will try to avoid using it for queries involving large tables and look at alternatives like Solidtable.
Fortunately with the possibility (probably (co)created by you) to get a result of db.executesql into the 'Row' type it is fairly straigtforward to SQLTABLE-related stuff in views combined with db.executesql. Regards Johann On 5 September 2013 23:34, Anthony <abasta...@gmail.com> wrote: > > SELECT rresearch.nu, rresearch.ny, rresearch.nc, rresearch.id >> >> FROM rresearch >> WHERE (rresearch.id IS NOT NULL) >> ORDER BY rresearch.nu, rresearch.ny, rresearch.nc, rresearch.id; >> >> 2920.90ms >> >> > The above query is not used for the grid. Rather, it is used to generate a > dropdown list for the db.isi_alt_countrynames.rsc_id field in the grid's > search widget. The db.isi_alt_countrynames.rsc_id is a reference field > that references db.rresearch, so it gets a default IS_IN_DB(db, ' > rresearch.id', ...) validator. In forms (including the grid search > widget), that validator results in a <select> widget with an option for > each item in db.rresearch.id (note, you won't see the select widget > unless you first click in the grid's search box and then select the > "rsc_id" field). Not only does the query take a long time to run and parse, > but it also takes a long time to generate the <select> widget with all the > options and send the HTML to the browser. > > In general, if you create a reference field that references a table with a > large number of records, you should avoid the default <select> widget for > that field. You can do this by putting the validator in a list, which will > prevent the <select> from being created: > > db.isi_alt_countrynames.rsc_id.requires = [db.isi_alt_countrynames.rsc_id. > requires] > > > >> >> and then one query for each of the rows in the result of the previous query >> like this >> (taking between 0.44ms and 0.72ms each): >> >> >> >> SELECT rresearch.id, rresearch.cn, rresearch.nf, rresearch.nc, >> >> rresearch.nd, rresearch.nn, rresearch.ny, rresearch.np, rresearch.nu, >> rresearch.nz, rresearch.uuid >> FROM rresearch WHERE (rresearch.id = 642117) LIMIT 1 OFFSET 0; >> >> 0.50ms >> > > I think the above queries are due to an unnecessary select in the > "represent" attribute of db.rresearch.id: > > vars = dict(country = str(db.rresearch[id].nu)) > > Note, above, there is no reason to do db.rresearch[id].nu, which results > in a select. Instead, just do: > > vars = dict(country = str(row.nu)) > > Anthony > > -- > > --- > 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) -- --- 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.
