Sorry, didn't look to closely at the queries. In this case, most of those
queries are for the search widget. The default search widget includes
dropdowns for all the reference fields that have "requires" attributes with
IS_IN_DB validators -- the IS NOT NULL queries for db.category and
db.auth_user are for populating those dropdowns. If you don't want that, in
the call to .grid(), you can set searchable=False, or you can specify
search_widget=[your custom widget] (or you can temporarily set the requires
attributes of the reference fields to None).
I'm not sure why each of those queries appears three times, though -- are
there multiple calls to .grid() in the request? Also, is that your exact
.grid() call? I see left joins in the queries, but you don't have a "left"
argument in your .grid() call.
Anthony
On Wednesday, June 12, 2013 8:18:59 AM UTC-4, David Marko wrote:
>
> 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.
