Welcome | Get started | Dive | Contribute | Topics | Reference | Changes | More

Exploring SQL activity in Lino Noi

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

Side note: Code snippets (lines starting with >>>) in this document get tested as part of our development workflow. The following initialization snippet tells you which demo project is being used in this document.

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 didn’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()
>>> print(" ".join([f.name for f in lh._data_elems]))
summary private urgent order end_user group
>>> ba = tickets.MyTickets.insert_action
>>> ba
<BoundAction(tickets.MyTickets, <lino.core.actions.ShowInsert insert ('New')>)>
>>> ses = rt.login("robin")
>>> ar = tickets.MyTickets.request(parent=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 ...>

EmptyResultSet

A queryset returned by QuerySet.none() is in many regards like normal querysets. You can call filter() on it (which does nothing, but it’s allowed and the result is of course stil none). It has a query attribute, which is a django.db.models.sql.Query object, but when you try to print it, Django raises an exception EmptyResultSet:

>>> qs = tickets.Ticket.objects.none()
>>> qs.query  
<django.db.models.sql.query.Query object at ...>
>>> str(qs.query)
Traceback (most recent call last):
...
django.core.exceptions.EmptyResultSet

Viewing the generated SQL

The following is an example of how you can print the SQL generated by some query.

>>> qs = ses.spawn(tickets.Tickets).data_iterator
>>> printsql(qs)
... 
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.group_id,
       tickets_ticket.private,
       tickets_ticket.urgent,
       tickets_ticket.priority,
       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.order_id,
       tickets_ticket.regular_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_password,
       users_user.verification_code,
       users_user.verification_code_sent_on,
       users_user.time_zone,
       users_user.date_format,
       users_user.dashboard_layout,
       users_user.event_type_id,
       users_user.open_session_on_new_ticket,
       users_user.notify_myself,
       users_user.mail_mode,
       T3.id,
       T3.email,
       T3.language,
       T3.modified,
       T3.created,
       T3.start_date,
       T3.end_date,
       T3.password,
       T3.last_login,
       T3.username,
       T3.user_type,
       T3.initials,
       T3.first_name,
       T3.last_name,
       T3.remarks,
       T3.partner_id,
       T3.verification_password,
       T3.verification_code,
       T3.verification_code_sent_on,
       T3.time_zone,
       T3.date_format,
       T3.dashboard_layout,
       T3.event_type_id,
       T3.open_session_on_new_ticket,
       T3.notify_myself,
       T3.mail_mode,
       T4.id,
       T4.modified,
       T4.created,
       T4.ref,
       T4.parent_id,
       T4.user_id,
       T4.assigned_to_id,
       T4.closed,
       T4.planned_time,
       T4.group_id,
       T4.private,
       T4.urgent,
       T4.priority,
       T4.summary,
       T4.description,
       T4.upgrade_notes,
       T4.ticket_type_id,
       T4.duplicate_of_id,
       T4.end_user_id,
       T4.state,
       T4.deadline,
       T4.reporter_id,
       T4.waiting_for,
       T4.feedback,
       T4.standby,
       T4.fixed_since,
       T4.last_commenter_id,
       T4.order_id,
       T4.regular_hours,
       T4.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
LEFT OUTER JOIN users_user ON (tickets_ticket.user_id = users_user.id)
LEFT OUTER JOIN users_user T3 ON (tickets_ticket.assigned_to_id = T3.id)
LEFT OUTER JOIN tickets_ticket T4 ON (tickets_ticket.duplicate_of_id = T4.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)
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', None, 117, 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
----------------- ----------- -------
                   COMMIT      1
                   UNKNOWN     1
                   UPDATE      1
 django_session    SELECT      1
 groups_group      SELECT      7
 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.feedback
tickets.Ticket.fixed_since
tickets.Ticket.free_hours
tickets.Ticket.group
tickets.Ticket.id
tickets.Ticket.last_commenter
tickets.Ticket.modified
tickets.Ticket.order
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.standby
tickets.Ticket.state
tickets.Ticket.summary
tickets.Ticket.ticket_type
tickets.Ticket.upgrade_notes
tickets.Ticket.urgent
tickets.Ticket.user
tickets.Ticket.waiting_for