On my development database I have a table with 115831 records and was
frustrated that a query to show the information in a grid, took a long
time. So I wrote an alternative function not using SQLFORM.grid as a
comparison.
The alternative function took about 2 seconds to show the result on the
screen, while the original one took more than 13 seconds. I want to use
this on a table with a record count of more than 80million records.
I wrote the second function to use the same sql query. I use postgresql.
The two functions reach the 'print count' statement about in the same
time. The grid one reaches the 'print db._lastsql' after more than 11
seconds.
Why is the the first one so slow?
Grid function:
@auth.requires_login()
def l1_countrynames():
response.view = 'isi/isi_grid.html'
opskrif="Alternative of country names"
links = [lambda row: (A(B(T('See records')), _target = "_blank",
_href = URL(r = request,
c = 'isi',
f = 'rsc_country_records',
vars = dict(country = str(row[db.
rresearch.nu]))))),
lambda row: (A(B(T('Update code')),# _target = "_blank",
_href = URL(r = request,
c = 'isi',
f = 'update_alt_country_code',
vars = dict(c_id = str(row[db.
isi_alt_countrynames.id])))))]
count = db.executesql("""SELECT reltuples::integer
FROM pg_class
WHERE oid =
'isi.isi_alt_countrynames'::regclass;""")[0][0]
print count
fields = [db.rresearch.nu, db.isi_alt_countrynames.code, db.
isi_alt_countrynames.id]
db.isi_alt_countrynames.id.readable = False
query = db.isi_alt_countrynames.rsc_id == db.rresearch.id
data = SQLFORM.grid(query,
maxtextlength=100,
cache_count = count,
fields = fields,
links = links,
editable = False,
deletable = False)
print db._lastsql
return dict(data=data, opskrif=opskrif)
Alternative one:
@auth.requires_login()
def l1_countrynames_alt():
response.view = 'isi/isi_grid.html'
opskrif="Alternative of country names"
db.rresearch.id.represent = lambda id, row: (A(B(T('See records')),_target
= "_blank",
_href = URL(r = request,
c = 'isi',
f = 'rsc_country_records',
vars = dict(country = str(db.
rresearch[id].nu)))))
db.isi_alt_countrynames.id.represent = lambda id, row: (A(B(T('Update
code')),# _target = "_blank",
_href = URL(r = request,
c = 'isi',
f = 'update_alt_country_code',
vars = dict(c_id = str(id)))))
count = db.executesql("""SELECT reltuples::integer
FROM pg_class
WHERE oid =
'isi.isi_alt_countrynames'::regclass;""")[0][0]
print count
data = SQLTABLE(db(db.isi_alt_countrynames.rsc_id == db.rresearch.id
).select(db.rresearch.nu, db.isi_alt_countrynames.code,
db.isi_alt_countrynames.id, db.rresearch.id,
limitby=(0,20)))
print db._lastsql
return dict(data=data, opskrif=opskrif)
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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
