All the reference fields generate queries in order to display the values
from their referenced table instead of displaying the raw ID stored in the
reference field itself. Separate queries are done for each row in the
table, so this can get expensive. It would be more efficient to do a single
query with joins to get all the data, but that is not how the grid works --
instead it just relies on the "represent" attribute of each field, which
requires a query per field per record.
Anthony
On Wednesday, June 12, 2013 7:47:08 AM UTC-4, David Marko wrote:
>
> I have a following table definition in my app. The table contains one
> filed that references another table called 'category' and then 3 fields
> that references auth_user table.
>
> task_type=db.define_table('scheduled_task_type',
> Field('category','reference category', label='Kategorie', requires=
> IS_IN_DB(db,db.category.id,'%(name)s')),
> Field('name', label='Název', requires=IS_NOT_EMPTY()),
> Field('description', 'text', label='Popis'),
> Field('period', label='Periodicita',requires=IS_IN_SET(PERIODICITA)),
> Field('start_date','date',label='Počáteční datum',requires=IS_DATE(
> format='%d.%m.%Y')),
> Field('responsible_1','reference auth_user', label='Odpovědná
> osoba',requires
> =IS_IN_DB(db,db.auth_user.id,'%(username)s')),
> Field('responsible_1_time','time',default='09:00', label='Reakční
> doba'),
> Field('responsible_2','reference auth_user', label='Zástupce',requires
> =IS_IN_DB(db,db.auth_user.id,'%(username)s')),
> Field('responsible_2_time','time',default='10:00', label='Reakční
> doba'),
> Field('responsible_3','reference auth_user', label='Osoba pro
> informování', requires=IS_IN_DB(db,db.auth_user.id,'%(username)s')),
> Field('responsible_3_time','time',default='17:00', label='Reakční
> doba'),
> )
>
> Now when I use grid in controller like this:
> grid = SQLFORM.grid(db.scheduled_task_type.id>0, fields=[task_type.
> category,task_type.name,task_type.description])
>
> ... I can see this huge set of database queries in response.toolbar()
>
> SELECT category.name, category.id FROM category WHERE (category.id IS
> NOT NULL) ORDER BY category.name, category.id;
> 1.00ms
> SELECT auth_user.username, auth_user.id FROM auth_user WHERE (auth_user.id
> IS NOT NULL) ORDER BY auth_user.username, auth_user.id;
> 1.00ms
> SELECT auth_user.username, auth_user.id FROM auth_user WHERE (auth_user.id
> IS NOT NULL) ORDER BY auth_user.username, auth_user.id;
> 0.00ms
> SELECT auth_user.username, auth_user.id FROM auth_user WHERE (auth_user.id
> IS NOT NULL) ORDER BY auth_user.username, auth_user.id;
> 0.00ms
> SELECT category.name, category.id FROM category WHERE (category.id IS
> NOT NULL) ORDER BY category.name, category.id;
> 0.00ms
> SELECT auth_user.username, auth_user.id FROM auth_user WHERE (auth_user.id
> IS NOT NULL) ORDER BY auth_user.username, auth_user.id;
> 0.00ms
> SELECT auth_user.username, auth_user.id FROM auth_user WHERE (auth_user.id
> IS NOT NULL) ORDER BY auth_user.username, auth_user.id;
> 0.00ms
> SELECT auth_user.username, auth_user.id FROM auth_user WHERE (auth_user.id
> IS NOT NULL) ORDER BY auth_user.username, auth_user.id;
> 0.00ms
> SELECT category.name, category.id FROM category WHERE (category.id IS
> NOT NULL) ORDER BY category.name, category.id;
> 0.00ms
> SELECT auth_user.username, auth_user.id FROM auth_user WHERE (auth_user.id
> IS NOT NULL) ORDER BY auth_user.username, auth_user.id;
> 0.00ms
> SELECT auth_user.username, auth_user.id FROM auth_user WHERE (auth_user.id
> IS NOT NULL) ORDER BY auth_user.username, auth_user.id;
> 0.00ms
> SELECT auth_user.username, auth_user.id FROM auth_user WHERE (auth_user.id
> IS NOT NULL) ORDER BY auth_user.username, auth_user.id;
> 1.00ms
> SELECT count(*) FROM scheduled_task_type LEFT JOIN category ON (
> scheduled_task_type.category = category.id) LEFT JOIN auth_user ON (
> scheduled_task_type.responsible_1 = auth_user.id) WHERE (
> scheduled_task_type.id > 0);
> 0.00ms
> SELECT scheduled_task_type.category,
> scheduled_task_type.name,scheduled_task_type
> .description, scheduled_task_type.id FROM scheduled_task_type LEFT JOIN
> category ON (scheduled_task_type.category = category.id) LEFT JOIN
> auth_user ON (scheduled_task_type.responsible_1 = auth_user.id) WHERE (
> scheduled_task_type.id > 0);
> 0.00ms
> SELECT category.id, category.name, category.description FROM category
> WHERE (category.id = 1) LIMIT 1 OFFSET 0;
> 0.00ms
>
>
> When I remove grid, this SQL queries disappears, so all are related to
> GRID component. What is this? Why so many redundant queries? Please help me
> understand how to optimise it ...
>
>
--
---
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.
