The project/+download page issues at least 2 queries for every file listed
Bug #389596 reported by
Guilherme Salgado
This bug affects 2 people
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Launchpad itself |
Triaged
|
Low
|
Unassigned |
Bug Description
One of the queries is to retrieve the LibraryFileAliases individually and the other is to retrieve the LibraryFileDown
OOPS-1263EA751 shows them, but in there you'll see 2 times more LFDC queries, which are not issued since bug 388082 was fixed.
Changed in launchpad-registry: | |
importance: | Undecided → Low |
tags: | added: releases |
tags: | added: tech-debt |
To post a comment you must log in.
Below is a query, provided by Stuart, which retrieves everything needed to render that page.
The following query retrieves all the information I think is needed to render the entire report. It runs in about a second for bzr, and gives the running hit count rather than the cached count (there is no performance improvement using LibraryFileAlia s.hits over SUM(count) ). It should be possible to convert this to Storm syntax if you want. The result will need to be transformed into a data structure usable by the existing template, or the existing template rewritten.
SELECT es.name, ias.id AS signature_id, aseFile. description, Alias.id, Alias.filename, Content. filesize, product = Product.id productseries = ProductSeries.id milestone = Milestone.id ile.productrele ase = ProductRelease.id ile.libraryfile ent.id = LibraryFileAlia s.content loadCount loadCount. libraryfilealia s = LibraryFileAlias.id ile.signature
Product.name,
ProductSeri
SignatureAl
Milestone.name,
ProductRele
LibraryFile
LibraryFile
LibraryFile
SUM(count),
MAX(day) AS last_downloaded
FROM
Product
JOIN ProductSeries ON ProductSeries.
JOIN Milestone ON Milestone.
JOIN ProductRelease ON ProductRelease.
JOIN ProductReleaseFile
ON ProductReleaseF
JOIN LibraryFileAlias
ON LibraryFileAlias.id = ProductReleaseF
JOIN LibraryFileContent
ON LibraryFileCont
LEFT OUTER JOIN LibraryFileDown
ON LibraryFileDown
LEFT OUTER JOIN LibraryFileAlias AS SignatureAlias
ON SignatureAlias.id = ProductReleaseF
WHERE
Product.name = 'bzr'
GROUP BY es.name, aseFile. description, Alias.id, Alias.filename, Content. filesize
Product.name,
ProductSeri
signature_id,
Milestone.name,
ProductRele
LibraryFile
LibraryFile
LibraryFile
ORDER BY es.name, Alias.filename;
ProductSeri
Milestone.name,
LibraryFile