711734 - (SlowMainThreadSQL) Slow SQL Statements on Main Thread

Status: NEW

(Firefox :: General, defect)

Description

[Asa Dotzler [:asa]]

Slow SQL Statements on Main Thread

Hits: 1  Total Time (ms): 392

Statement: 
SELECT value, ROUND( timesUsed / MAX(1.0, (lastUsed - firstUsed) / :timeGroupingSize) * MAX(1.0, :maxTimeGroupings - (:now - lastUsed) / :timeGroupingSize) * MAX(1.0, :agedWeight * (firstUsed < :expiryDate)) / :bucketSize , 3) AS frecency, 1 AS boundaryBonuses FROM moz_formhistory WHERE fieldname=:fieldname AND (value LIKE :valuePrefix ESCAPE '/') ORDER BY ROUND(frecency * boundaryBonuses) DESC, UPPER(value) ASC

Comment 1

[henryfhchan]

Hits	Total Time (ms)	Statement
2	472	SELECT h.id, h.url, IFNULL(b.title, h.title), h.rev_host, h.visit_count, h.last_visit_date, f.url, null, b.id, b.dateAdded, b.lastModified, b.parent, null, h.frecency, b.position, b.type, b.fk, b.guid FROM moz_bookmarks b LEFT JOIN moz_places h ON b.fk = h.id LEFT JOIN moz_favicons f ON h.favicon_id = f.id WHERE b.parent = :parent ORDER BY b.position ASC
1	152	DELETE FROM moz_bookmarks WHERE parent IN (:parent)
2	661	INSERT OR IGNORE INTO moz_places (url, title, rev_host, hidden, typed, frecency, guid) VALUES (:page_url, :page_title, :rev_host, :hidden, :typed, :frecency, GENERATE_GUID())

Comment 2

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

(In reply to henry.fai.hang.chan from comment #1)
> Hits	Total Time (ms)	Statement
> 2	472	SELECT h.id, h.url, IFNULL(b.title, h.title), h.rev_host,
> h.visit_count, h.last_visit_date, f.url, null, b.id, b.dateAdded,
> b.lastModified, b.parent, null, h.frecency, b.position, b.type, b.fk, b.guid
> FROM moz_bookmarks b LEFT JOIN moz_places h ON b.fk = h.id LEFT JOIN
> moz_favicons f ON h.favicon_id = f.id WHERE b.parent = :parent ORDER BY
> b.position ASC

This is a query populating a bookmarks view (toolbar/Library), do you have many bookmarks or tags?

> 1	152	DELETE FROM moz_bookmarks WHERE parent IN (:parent)

Removal of a bookmarks folder, maybe hitting a checkpoint (if it was a pretty large folder)

> 2	661	INSERT OR IGNORE INTO moz_places (url, title, rev_host, hidden, typed,
> frecency, guid) VALUES (:page_url, :page_title, :rev_host, :hidden, :typed,
> :frecency, GENERATE_GUID())

likely creation of a bookmark, maybe hitting a checkpoint

Comment 3

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

(In reply to Marco Bonardo [:mak] from comment #2)
> > 1	152	DELETE FROM moz_bookmarks WHERE parent IN (:parent)
> 
> Removal of a bookmarks folder, maybe hitting a checkpoint (if it was a
> pretty large folder)

Actually this may even just be a livemark updating.

Comment 4

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

(In reply to Asa Dotzler [:asa] from comment #0)
> SELECT value, ROUND( timesUsed / MAX(1.0, (lastUsed - firstUsed) /
> :timeGroupingSize) * MAX(1.0, :maxTimeGroupings - (:now - lastUsed) /
> :timeGroupingSize) * MAX(1.0, :agedWeight * (firstUsed < :expiryDate)) /
> :bucketSize , 3) AS frecency, 1 AS boundaryBonuses FROM moz_formhistory
> WHERE fieldname=:fieldname AND (value LIKE :valuePrefix ESCAPE '/') ORDER BY
> ROUND(frecency * boundaryBonuses) DESC, UPPER(value) ASC

This is form history, I'm not surprised it may be slow considering the ordering, it can't use an index. formhistory is being rewritten to be async in bug 566746, that will just move the problem to another thread, though.

Comment 5

[henryfhchan]

(In reply to Marco Bonardo [:mak] from comment #2)
> (In reply to henry.fai.hang.chan from comment #1)
> > Hits	Total Time (ms)	Statement
> > 2	472	SELECT h.id, h.url, IFNULL(b.title, h.title), h.rev_host,
> > h.visit_count, h.last_visit_date, f.url, null, b.id, b.dateAdded,
> > b.lastModified, b.parent, null, h.frecency, b.position, b.type, b.fk, b.guid
> > FROM moz_bookmarks b LEFT JOIN moz_places h ON b.fk = h.id LEFT JOIN
> > moz_favicons f ON h.favicon_id = f.id WHERE b.parent = :parent ORDER BY
> > b.position ASC
> 
> This is a query populating a bookmarks view (toolbar/Library), do you have
> many bookmarks or tags?

149 in unsorted, is that a lot?

Comment 6

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

(In reply to henry.fai.hang.chan from comment #5)
> 149 in unsorted, is that a lot?

no, it's not.

Comment 7

[BugBot [:suhaib / :marco/ :calixte]]

The severity field for this bug is relatively low, S3. However, the bug has 10 votes.
:mossop, could you consider increasing the bug severity?

For more information, please visit auto_nag documentation.

Comment 8

[BugBot (nomail) [:suhaib / :marco/ :calixte]]

The last needinfo from me was triggered in error by recent activity on the bug. I'm clearing the needinfo since this is a very old bug and I don't know if it's still relevant.