388243 - opening "page info" dialog can be slow to open for large histories

Status: NEW

(Firefox :: Page Info Window, defect)

Description

[(not reading, please use [e-mail] instead)]

opening "page info" dialog can be slow to open for large histories

using ispiked's places.sqlite, if I do page info on a page that he's visted over 20,000 times, opening page info can take several seconds.

profiling shows the problem to be in previousVisitCount() in mozilla/browser/base/content/pageinfo/security.js.

as is, that code will execute a query that is not the most efficient way to determine the number of times we've seen a given page in the past.

With the current code, we'll do:

SELECT h.id, h.url, h.title, h.rev_host, h.visit_count, v
.visit_date, f.url, v.session, null FROM moz_places h JOIN moz_historyvisits v O
N h.id = v.place_id LEFT OUTER JOIN moz_favicons f ON h.favicon_id = f.id WHERE
(v.visit_date <= ?1 AND h.rev_host >= ?2 AND h.rev_host < ?3  AND h.hidden <> 1
AND v.visit_type <> 4 )

The real problem is the nsINavHistoryService interface.  I think we should add getPageDetails() (note, we already have setPageDetails()).

That would allow the page info code to quickly get the total number of visits.  then we could subtract the number of visits today (using a query similar to what page info is doing already).

I'll log a spin off blocker bug on adding getPageDetails to nsINavHistoryService interface.

Comment 1

[Johnathan Nightingale [:johnath]]

Yeah, I think that code was the best-available-at-the-time, but I absolutely agree that it would be preferable to have a friendlier API.  I'll take this bug, once the nsINavHistoryService bug lands.  ss - have you filed that yet?

Comment 2

[(not reading, please use [e-mail] instead)]

Note, I misunderstood that the page info UI does a search by domain, not URI, so getPageDetails() won't do us any good (since we need to join moz_places with moz_historyvisits).

see bug #388314 for the api/query I am suggesting for efficiently getting the value you want.

Comment 3

[(not reading, please use [e-mail] instead)]

the call to execute this query blocks the opening of the dialog, as we do the work in a function that is called from the onload handler.

we might want to execute the query on a setTimeout(...,0), so that the page info dialog show up faster, in addition to fixing bug #388314.

Note, with larry, you get to page info from the "Tell me more about this website..." link.

Comment 4

[Michael Kohler [:mkohler]]

This is a mass change. Every comment has "assigned-to-new" in it.

I didn't look through the bugs, so I'm sorry if I change a bug which shouldn't be changed. But I guess these bugs are just bugs that were once assigned and people forgot to change the Status back when unassigning.

Comment 5

[Fanolian]

Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0a1) Gecko/20110507 Firefox/6.0a1 ID:20110507030609

Recently I have the same problem in Nightly, but not in Fx4.0.1 nor Aurora.

My daily-use places.sqlite is 10,240KB with the earliest history entry dated around 2011-04-26 (I don't know how to find the number of total entries without counting one by one). It is hogging my CPU (Intel Core2Duo E6300 65nm version) up to 50% of usage when I open Page Info if the URL is not about:* (I test it in www.google.com).
Exporting the places.sqlite to 3 new profiles for Nightly, Aurora and Firefox 4.0.1, I only see the hogging in Nightly. Aurora and Firefox open Page Info instantly.
Clearing the history in Nightly resolves the problem.

Comment 6

[Fanolian]

I forget to mention that Page Info takes about 8 seconds to pop up. During the period Nightly is completely unresponsive.

Comment 7

[Marco Bonardo [:mak]]

(In reply to comment #5)
> Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0a1) Gecko/20110507 Firefox/6.0a1
> ID:20110507030609
> 
> Recently I have the same problem in Nightly, but not in Fx4.0.1 nor Aurora.
> 
> My daily-use places.sqlite is 10,240KB with the earliest history entry dated
> around 2011-04-26

I have a 100MB db, and opening the page info is instant here (even on pages where I have more than 14000 visits)

> Clearing the history in Nightly resolves the problem.

This is weird, could you please install this addon in Nightly (https://addons.mozilla.org/firefox/addon/places-maintenance/), from Preset choose Deep Check, then execute it.
Does it change anything? Could you please paste the output here?

I admit the query we do in this case sucks, but I'm not even sure what's the usefulness of knowing how many times I've visited the page before today versus just knowing whether I've visited it before today or not (that would be much faster)

Comment 8

[Marco Bonardo [:mak]]

btw, we may even use
PlacesUtils.history.QueryInterface(Ci.nsPIPlacesDatabase)
                   .asyncExecuteLegacyQueries()
as a simple workaround to make the query async.

Comment 9

[Fanolian]

Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0a1) Gecko/20110517 Firefox/6.0a1 ID:20110517030625

After executing the Deep Check, I can open Page Info instantly.
The places.sqlite I use in question is a backup of the one I mentioned in comment 5.

Output:
> Integrity check
+ The database is sane
> Coherence check
+ The database is coherent
> Orphans expiration
+ Database cleaned up
> Vacuum
Initial database size is 10240 KiB
+ The database has been vacuumed
Final database size is 10240 KiB
> Statistics
Database size is 10240 KiB
user_version is 11
page_size is 32768
cache_size is 11795
journal_mode is wal
synchronous is 1
History can store a maximum of 193250 unique pages
Table moz_places has 7656 records
Table moz_historyvisits has 9833 records
Table moz_inputhistory has 69 records
Table moz_bookmarks has 711 records
Table moz_bookmarks_roots has 5 records
Table moz_keywords has 1 records
Table sqlite_sequence has 1 records
Table moz_favicons has 599 records
Table moz_annos has 345 records
Table moz_anno_attributes has 9 records
Table moz_items_annos has 139 records
Table sqlite_stat1 has 15 records
Index sqlite_autoindex_moz_inputhistory_1
Index sqlite_autoindex_moz_bookmarks_roots_1
Index sqlite_autoindex_moz_keywords_1
Index sqlite_autoindex_moz_favicons_1
Index sqlite_autoindex_moz_anno_attributes_1
Index moz_places_faviconindex
Index moz_places_hostindex
Index moz_places_visitcount
Index moz_places_frecencyindex
Index moz_places_lastvisitdateindex
Index moz_historyvisits_placedateindex
Index moz_historyvisits_fromindex
Index moz_historyvisits_dateindex
Index moz_bookmarks_itemindex
Index moz_bookmarks_parentindex
Index moz_bookmarks_itemlastmodifiedindex
Index moz_places_url_uniqueindex
Index moz_places_guid_uniqueindex
Index moz_bookmarks_guid_uniqueindex
Index moz_annos_placeattributeindex
Index moz_items_annos_itemattributeindex
Trigger moz_bookmarks_beforedelete_v1_trigger