Thanks Anthony, searchable=False did the trick and removed the first 4
queries. Things got clear for me now ...
Dne středa, 12. června 2013 16:17:11 UTC+2 Anthony napsal(a):
>
> 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.
