On 4 September 2013 15:34, Massimo Di Pierro <[email protected]>wrote:
> Please check with the response.toolbar to see if there is are hidden > recursive queries. > > Thanks! I did not use the 'db stats' function before when debugging and it seems a useful tool! Interestingly the difference is the following: When using the grid, there are two queries: 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 SELECT rresearch.nu, isi_alt_countrynames.code, isi_alt_countrynames.id, rresearch.id FROM rresearch, isi_alt_countrynames WHERE (isi_alt_countrynames.rsc_id = rresearch.id) ORDER BY rresearch.id, isi_alt_countrynames.id LIMIT 20 OFFSET 0; 325.16ms and when using SQLTABLE there are a big one: SELECT rresearch.nu, isi_alt_countrynames.code, isi_alt_countrynames.id, rresearch.id FROM rresearch, isi_alt_countrynames WHERE (isi_alt_countrynames.rsc_id = rresearch.id) ORDER BY rresearch.nu LIMIT 21 OFFSET 0; which took 185.42ms 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 This leaves me with more questions about the efficiency of the translation of the queries to the backend: * In the case of the grid it will be totally impracticable to try and load the whole rresearch table when working with table containing 80million records. Why was this necessary? * In the case of the SQLTABLE why would each of those queries select all the fields in stead of just those necessary in the original query? I am beginning to think of bypassing DAL in these queries and go straight to the backend. Regards Johann -- --- 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.
