Using as_list() as a performance boost

[Vinicius Assef]

Hi everyone.

I'm developping a function that wil be called through ajax and it accesses the DB, but I need it to be as fast as possible.

Would I expect the `as_list()` method be faster than the normal `select()`?

—
Vinicius.

[Anthony]

.as_list() operates on a Rows object (i.e., after you have already called .select()), so it would actually slow things down, as it involves additional processing.

If you want to avoid the creation of the Rows object and instead just want a list of dictionaries (based on the raw data returned from the db), the easiest method is probably:

db.executesql(db(query)._select(), as_dict=True)

The ._select() method returns the SQL code, which is then executed via db.executesql() -- with as_dict=True, it converts the list of tuples returned by the database driver to a list of dictionaries.

More generally, you can take the standard list of tuples returned by executesql and process it however you like. Another way to do that is:

db(query).select(..., processor=custom_processor)

where the custom_processor function takes the following arguments: rows, fields, columns, and cacheable.

Note, to improve speed, you should select only the columns you need, and it will be even faster if you leave the list of tuples as is rather than converting to dictionaries (though then you will have to access the field values based on position within the tuples -- so be careful to do that in a way that won't break if you add, remove, or re-order columns in the future).

Finally, if you still want a Rows object, you can speed things up a bit by doing:

db(query).select(..., cacheable=True)

though you will lose the .delete_record and .update_record methods of each Row.

Anthony

[Vinicius Assef]

Very complete, Anthony. Thank you very much!

—
Vinicius.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
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+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.