Exploring SQL activity in Lino Noi¶

This document helps us to inspect and visualize some performance problems encountered on Jane.

Dashboard is slow after Django upgrade from 1 to 2
Viewing the generated SQL

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 insert 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.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.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
 votes_vote        SELECT      30
 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.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

Exploring SQL activity in Lino Noi¶

Dashboard is slow after Django upgrade from 1 to 2¶

Viewing the generated SQL¶

Dashboard is slow after Django upgrade from 1 to 2 ¶

Viewing the generated SQL ¶