Smartgrid link very slow.

David Manns

unread,

Apr 15, 2019, 12:24:44 PM4/15/19

to web2py-users

here is my smartgrid (simplified to show only essential pieces):

    grid = SQLFORM.smartgrid(db.Members, linked_tables=['Affiliations'],
                    constraints=dict(Members=query),
                    deletable=False, details=False, editable=True, create=True)

Members is a table with several thousand records.

Affiliations is a table with a reference field back to Members; each member generally has a small number of Affiliations.

query on the Member table that selects a number of Members (generally a few up to a few hundred).

The page displays the subset of Members efficiently, with each selected member including an edit button and an Affiliations link.

As expected the edit button displays the individual Member record promptly.

Clicking the Affiliations link does display the page showing the small number of affiliations belonging to the member as one would expect, but very slowly (order of 10 seconds in my test environment). It must be retrieving all Affiliations and then filtering to rows belonging to the member???

I'm updating an aging implementation that uses legacy crud, as smartgrid will allow me to greatly reduce the amount and complexity of controller code, however the legacy implementation generates the equivalent page with no apparent delay.

David Manns

unread,

Apr 16, 2019, 8:42:43 AM4/16/19

to web2py-users

The problem is with SQLFORM.grid

Table Affiliations has some 5,000 rows. Each row has references to 2 other tables, Members and Colleges each of which has format=... so it displays text rather than the id as a number.

So SQLFORM.grid(db.Affiliations.Member==<id>, .... will correctly display only the (usually 1) rows out of the 5,000 odd referring to <id>. However, this takes many seconds to process, I suspect its doing a full left join of Affiliations with Members and Colleges, whereas a select like

db(db.Affiliations.Member==member.id).select(db.Affiliations.ALL, db.Colleges.ALL, db.Members.ALL,
left=[db.Colleges.on(db.Colleges.id==db.Affiliations.College),
db.Members.on(db.Members.id==db.Affiliations.Member)], ...

takes no time at all.

dgm...@gmail.com

unread,

Apr 23, 2019, 4:20:58 PM4/23/19

to web2py-users

I discovered why my smartgrid (and grids) were very slow. =

I had neglected to set searchable=False, sortable=False. With those options disabled (I need neither), performance is very good. It is the searchable option that is very costly.