Exploring SQL activity in Lino Noi¶
This document helps us to inspect and visualize some performance problems encountered on Jane.
This is a tested document. The following instructions are used for initialization:
We use the lino_book.projects.noi1e demo database.
>>> from lino import startup
>>> startup('lino_book.projects.noi1e.settings.demo')
>>> from lino.api.doctest import *
Dashboard is slow after Django upgrade from 1 to 2¶
The following helped us to fix #2782. When rendering the 
button of the insert action of MyTickets in the dashboard, Lino needs to create
a temporary ticket object because get_status() must specify the known values for
the insert window that will potentially open when you click on the + button. But
Lino created a data_record with all detail fields, including e.g.
DuplicatesByTicket (a slave table that shows all duplicates of this ticket).
These slave tables make no sense on a master without id. For some reason Django2
now executed a database query for each of them. Django1 disdn't, but the problem
was was also there. We now changed the core so that
BaseRequest.elem2rec_insert() specifies only the fields needed by the window.
>>> lh = tickets.MyTickets.insert_layout.get_layout_handle(settings.SITE.kernel.default_ui)
>>> print(" ".join([f.name for f in lh._data_elems]))
summary ticket_type priority end_user site description
>>> ba = tickets.MyTickets.insert_action
>>> ba
<BoundAction(tickets.MyTickets, <lino.core.actions.ShowInsert insert ('New')>)>
>>> ses = rt.login("robin")
>>> ar = tickets.MyTickets.request_from(ses)
>>> st = ba.action.get_status(ar)
>>> print(' '.join(st.keys()))
data_record
>>> print(' '.join(st['data_record'].keys()))
phantom data title
>>> st['data_record']['phantom']
True
>>> st['data_record']['data']
>>> 'DuplicatesByTicket' in st['data_record']['data']
False
>>> st['data_record']['data']['DuplicatesByTicket']
<Element table at ...>
Viewing the generated SQL¶
The following is an example of you you can print the SQL generated by some query.
>>> import sqlparse
>>> qs = tickets.Tickets.request().get_data_iterator()
>>> sql = str(qs.query).replace('"', '')
>>> print(sqlparse.format(sql, reindent=True, keyword_case='upper'))
...
SELECT tickets_ticket.id,
tickets_ticket.modified,
tickets_ticket.created,
tickets_ticket.ref,
tickets_ticket.parent_id,
tickets_ticket.user_id,
tickets_ticket.assigned_to_id,
tickets_ticket.closed,
tickets_ticket.planned_time,
tickets_ticket.site_id,
tickets_ticket.priority,
tickets_ticket.private,
tickets_ticket.summary,
tickets_ticket.description,
tickets_ticket.upgrade_notes,
tickets_ticket.ticket_type_id,
tickets_ticket.duplicate_of_id,
tickets_ticket.end_user_id,
tickets_ticket.state,
tickets_ticket.deadline,
tickets_ticket.reporter_id,
tickets_ticket.waiting_for,
tickets_ticket.feedback,
tickets_ticket.standby,
tickets_ticket.fixed_since,
tickets_ticket.last_commenter_id,
tickets_ticket.regular_hours,
tickets_ticket.extra_hours,
tickets_ticket.free_hours,
users_user.id,
users_user.email,
users_user.language,
users_user.modified,
users_user.created,
users_user.start_date,
users_user.end_date,
users_user.password,
users_user.last_login,
users_user.username,
users_user.user_type,
users_user.initials,
users_user.first_name,
users_user.last_name,
users_user.remarks,
users_user.partner_id,
users_user.verification_code,
users_user.verification_code_sent_on,
users_user.time_zone,
users_user.date_format,
users_user.dashboard_layout,
users_user.access_class,
users_user.event_type_id,
users_user.open_session_on_new_ticket,
users_user.notify_myself,
users_user.mail_mode,
users_user.github_username,
T4.id,
T4.email,
T4.language,
T4.modified,
T4.created,
T4.start_date,
T4.end_date,
T4.password,
T4.last_login,
T4.username,
T4.user_type,
T4.initials,
T4.first_name,
T4.last_name,
T4.remarks,
T4.partner_id,
T4.verification_code,
T4.verification_code_sent_on,
T4.time_zone,
T4.date_format,
T4.dashboard_layout,
T4.access_class,
T4.event_type_id,
T4.open_session_on_new_ticket,
T4.notify_myself,
T4.mail_mode,
T4.github_username,
T5.id,
T5.modified,
T5.created,
T5.ref,
T5.parent_id,
T5.user_id,
T5.assigned_to_id,
T5.closed,
T5.planned_time,
T5.site_id,
T5.priority,
T5.private,
T5.summary,
T5.description,
T5.upgrade_notes,
T5.ticket_type_id,
T5.duplicate_of_id,
T5.end_user_id,
T5.state,
T5.deadline,
T5.reporter_id,
T5.waiting_for,
T5.feedback,
T5.standby,
T5.fixed_since,
T5.last_commenter_id,
T5.regular_hours,
T5.extra_hours,
T5.free_hours,
contacts_partner.id,
contacts_partner.email,
contacts_partner.language,
contacts_partner.url,
contacts_partner.phone,
contacts_partner.gsm,
contacts_partner.fax,
contacts_partner.country_id,
contacts_partner.city_id,
contacts_partner.zip_code,
contacts_partner.region_id,
contacts_partner.addr1,
contacts_partner.street_prefix,
contacts_partner.street,
contacts_partner.street_no,
contacts_partner.street_box,
contacts_partner.addr2,
contacts_partner.vat_regime,
contacts_partner.vat_id,
contacts_partner.prefix,
contacts_partner.name,
contacts_partner.remarks,
contacts_partner.payment_term_id,
contacts_partner.purchase_account_id,
contacts_person.partner_ptr_id,
contacts_person.title,
contacts_person.first_name,
contacts_person.middle_name,
contacts_person.last_name,
contacts_person.gender,
contacts_person.birth_date
FROM tickets_ticket
INNER JOIN tickets_site ON (tickets_ticket.site_id = tickets_site.id)
LEFT OUTER JOIN users_user ON (tickets_ticket.user_id = users_user.id)
LEFT OUTER JOIN users_user T4 ON (tickets_ticket.assigned_to_id = T4.id)
LEFT OUTER JOIN tickets_ticket T5 ON (tickets_ticket.duplicate_of_id = T5.id)
LEFT OUTER JOIN contacts_person ON (tickets_ticket.end_user_id = contacts_person.partner_ptr_id)
LEFT OUTER JOIN contacts_partner ON (contacts_person.partner_ptr_id = contacts_partner.id)
WHERE (NOT tickets_ticket.private
AND NOT tickets_site.private)
ORDER BY tickets_ticket.id DESC
During startup there were two SQL queries:
>>> show_sql_queries()
SELECT excerpts_excerpttype.id, excerpts_excerpttype.name, excerpts_excerpttype.build_method, excerpts_excerpttype.template, excerpts_excerpttype.attach_to_email, excerpts_excerpttype.email_template, excerpts_excerpttype.certifying, excerpts_excerpttype.remark, excerpts_excerpttype.body_template, excerpts_excerpttype.content_type_id, excerpts_excerpttype.primary, excerpts_excerpttype.backward_compat, excerpts_excerpttype.print_recipient, excerpts_excerpttype.print_directly, excerpts_excerpttype.shortcut, excerpts_excerpttype.name_de, excerpts_excerpttype.name_fr FROM excerpts_excerpttype ORDER BY excerpts_excerpttype.id ASC
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ...
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ...
Now we do a single request to Tickets. And look at all the
SQL that poor Django must do in order to return a single row.
>>> reset_sql_queries()
>>> r = demo_get('robin','api/tickets/Tickets', fmt='json')
>> r = demo_get('robin','api/tickets/Tickets', fmt='json', limit=1) >> res = test_client.get('/api/tickets/Tickets?fmt=json&limit=1') >> res = check_json_result(res) >> rmu(res.keys()) ['count', 'rows', 'no_data_text', 'success', 'title', 'param_values'] >> len(res['rows']) 1
>>> show_sql_summary()
================= =========== =======
table stmt_type count
----------------- ----------- -------
UNKNOWN 1
UPDATE 1
django_session SELECT 1
tickets_site SELECT 13
tickets_ticket SELECT 2
users_user SELECT 1
working_session SELECT 15
================= =========== =======
>>> show_sql_queries()
...
To verify whether the slave summary panels are being computed:
>>> for f in sorted([str(f) for f in rt.models.tickets.Tickets.wildcard_data_elems()]):
... print(f)
tickets.Ticket.assigned_to
tickets.Ticket.closed
tickets.Ticket.comments
tickets.Ticket.created
tickets.Ticket.deadline
tickets.Ticket.description
tickets.Ticket.duplicate_of
tickets.Ticket.end_user
tickets.Ticket.extra_hours
tickets.Ticket.feedback
tickets.Ticket.fixed_since
tickets.Ticket.free_hours
tickets.Ticket.id
tickets.Ticket.last_commenter
tickets.Ticket.modified
tickets.Ticket.parent
tickets.Ticket.planned_time
tickets.Ticket.priority
tickets.Ticket.private
tickets.Ticket.ref
tickets.Ticket.regular_hours
tickets.Ticket.reporter
tickets.Ticket.site
tickets.Ticket.standby
tickets.Ticket.state
tickets.Ticket.summary
tickets.Ticket.ticket_type
tickets.Ticket.upgrade_notes
tickets.Ticket.user
tickets.Ticket.waiting_for
