Bug 711734 (SlowMainThreadSQL)

Slow SQL Statements on Main Thread

NEW
Unassigned

Status

()

Firefox
General
6 years ago
5 years ago

People

(Reporter: asa, Unassigned)

Tracking

(Blocks: 1 bug)

Trunk
Firefox 11
x86_64
Windows 7
Points:
---

Firefox Tracking Flags

(Not tracked)

Details

(Reporter)

Description

6 years ago
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

6 years ago
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())
(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
(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.
(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.

Updated

6 years ago
Blocks: 699820

Comment 5

6 years ago
(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?
(In reply to henry.fai.hang.chan from comment #5)
> 149 in unsorted, is that a lot?

no, it's not.
Target Milestone: --- → Firefox 11

Updated

6 years ago
Alias: SlowMainThreadSQL
You need to log in before you can comment on or make changes to this bug.