if you don't need the grid to search through the table, the moment you turn off the search widget you won't get the "performance penalty" of creating the dropbox for the IS_IN_DB requirement you have in your models.
On Monday, September 9, 2013 10:24:48 AM UTC+2, Johann Spies wrote: > > 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 <[email protected] <javascript:>>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 [email protected] <javascript:>. >> 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
