I have found that using the 'Represents' functionality as described in http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=lazy#Rendering-rows-using-represent slows things down quite a lot - more than doubles the time it takes to process some tables. This is a shame because this option makes it really easy to get human readable data from very simple table SELECTs.
I think this performance is to be expected since I assume it is issuing a SELECT for every record to get the Represents values. So I have 1 SELECT for my set of, say, 100 records and then there is another 100+ SELECTS to get the Represent values for the fields in one or more related tables as I access those records.
Any tips on how to improve the render() function performance? Or should I expect the render() option to be sluggish? Is the only alternative to build a query with the necessary joins in order to get the Represents data?
Thanks
By default, reference fields that refer to a related table with a format attribute defined will get a represent function that does indeed select the related record (and then applies the format function/specification to the retrieved record), leading to the "N+1" query problem you have observed.
If you still want to make use of rows.render() to apply the represent functions of other types of fields that do not involve database selects, you can eliminate the problem for reference fields by explicitly removing their default represent attributes:
Field('other_table', 'reference other_table', represent=None)
Of course, in that case, you do not get the nicely formatted presentation of the other_table field. As you suggest, the more efficient alternative in that case is to build a query that joins the related table.