Improve database performance

[Ed McDonagh]

Lots of long query delays, need to reduce them.

[David Platten]

@Ed McDonagh do you have any particular parts of OpenREM in mind for this?

[Ed McDonagh]

Added index to modality type in GeneralStudyModuleAttr and the foreign key in GeneralEquipmentModuleAttr - both make RF filter page much faster. Refs #922

→ <<cset 09dd5ee78b9f>>

[Ed McDonagh]

Replacing a new count with one that already exists, removing slow query. Refs #922

→ <<cset 465cc30609cf>>

[Ed McDonagh]

@David Platten this sort of thing! I’m generally working through easy fixes.

[Ed McDonagh]

Replacing a new count with one that already exists, removing slow query (DX). Refs #922

→ <<cset efc36963f02d>>

[Ed McDonagh]

Replacing a new count with one that already exists, removing slow query (MG). Added indexes which massively reduces query time for MG filtered. Refs #922

→ <<cset 9c80f4efc0d1>>

[Ed McDonagh]

Replacing a new count with one that already exists, removing slow query (CT). Refs #922

→ <<cset fc056b4ef68f>>

[David Platten]

@Ed McDonagh can you remind me of how I profile the database queries?

[Ed McDonagh]

For the web interface: https://docs.openrem.org/en/latest/enabling_debug_toolbar.html

I’d like to be able to do the same for the exports, but they will probably be improved as a side effect of adding indexes for improving the web interface. Otherwise I need to investigate https://docs.djangoproject.com/en/3.2/faq/models/#faq-see-raw-sql-queries

[Ed McDonagh]

Additional indexes to speed up RF detail page. Refs #922

→ <<cset 406913517b50>>

[Ed McDonagh]

Additional indexes to speed up RF detail page. Refs #922

→ <<cset 66575cd92e53>>

[Ed McDonagh]

Indexes to speed up CT detail pages. Refs #922

→ <<cset 564df2e30ae7>>

[Ed McDonagh]

Index to speed up DX detail pages. Refs #922

→ <<cset 83733faf0284>>

[David Platten]

I think that changing the paginator lines from

paginator = Paginator(f.qs, user_profile.itemsPerPage)

to

paginator = Paginator(f.qs.order_by(), user_profile.itemsPerPage)

Provides a small (maybe 5%) speed increase as it resets the sorting to the default (fastest) sorting for the paginator calculations

‌

Scrap the above - it doesn’t work when the user chooses something in the ordering filter.

[Ed McDonagh]

Added indexes to all the foreign key relationships, on the assumption that they will improve as the ones that were called in the interface were... Refs #922

→ <<cset be14789c1273>>

[Ed McDonagh]

Yuck - black wants the indexes to be spread over many lines.... Refs #922

→ <<cset 8cb47eac6471>>

[Ed McDonagh]

Adding ref #922 to changes.

→ <<cset f8e52ddb7179>>

[David Platten]

@Ed McDonagh I’ve just done some timing tests on this branch compared with the current develop version.

I used a copy of a database with 251683 CT studies.

I recorded how long the CT summary page SQL queries took using the debug toolbar, refreshing the page seven times for each branch:

Develop branch (s)	Issue 922 branch (s)
3.9	2.2
2.8	1.8
2.9	1.7
3.4	1.6
3.2	1.9
4.3	1.8
4.0	1.8

The 922 branch significantly speeds up the page load times.

[Ed McDonagh]

Thanks David. Did it speed up actual times too?

[David Platten]

Running on my desktop test system now (12 core AMD Ryzen 3900X, 64 GB RAM, PCIe 4.0 drive):

219731 CT studies

Develop branch (SQL, total time) (s)	Issue 922 branch (SQL, total time) (s)
1.4, 11.3	0.9, 10.7
1.5, 11.0	0.8, 10.3
1.4, 11.1	0.9, 10.6
1.3, 11.1	0.8, 10.5
1.3, 11.0	0.9, 10.7

To obtain above timings OpenREM has to be run with debug=True in local settings. Switching this off speeds up the load time significantly, but I don’t get the times from the debug tool bar. Timing with my phone, the page loads in about 1.5 seconds (922 branch), and 2.2 seconds (develop).

[Ed McDonagh]

Thanks David. That’s really helpful.

[Ed McDonagh]

Merged in issue922database (pull request #472)

Fixes #922

Approved-by: David Platten

→ <<cset 9006c10c5115>>

[David Platten]

Some more timings from the standard name mapping branch.

251683 CT studies; CT summary view; running on my laptop
Times in seconds (total time / SQL query time)

Without database indexes / view change

Standard mapping disabled
8.2 / 4.5
8.0 / 4.1
8.8 / 4.8
7.6 / 3.9

Standard mapping enabled; mapped head study descriptions
9.3 / 3.9
6.8 / 2.8
6.8 / 2.7
7.0 / 2.8

‌

With issue 922 database changes merged in
251683 CT studies

Standard mapping disabled
5.8 / 2.0
4.2 / 1.7
4.2 / 1.7
4.9 / 1.8

Standard mapping enabled; mapped head study descriptions
5.4 / 1.7
6.4 / 2.0
6.4 / 1.8
6.6 / 2.1

[Ed McDonagh]

Awesome!