I thought so but it is from table with one record only and one record
reference category and three users. But I can see many queries to auth_user
table with WHERE (auth_user.id IS NOT NULL) clause which is quite strange.
Whats more, as you can see from my example I dont list user name columns at
all in grid see fields=[task_type.category,task_type.name,task_type.
description]
Dne středa, 12. června 2013 14:08:46 UTC+2 Anthony napsal(a):
>
> 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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
