1137462 - Improve history database performance

Status: RESOLVED FIXED

(Firefox for iOS :: Data Storage, defect)

Description

[Wesley Johnston (:wesj)]

I haven't spent a lot of time working on performance of history access, but I think we can do a little better:

1.) Inserts into the visits currently have to do a separate lookup in the history table for the site id. We can do that in one command. Some perf testing on desktop shows about a 25% boost (i.e. 1.5 vs 2s to insert 10,000 entries). Seems like an easy win.

2.) Improve the date sorting. We current do our sectioning in the history view by iterating through all the data and looking at dates. There's no reason we can't just do 4 separate queries for it though (it also makes the code a lot cleaner). There's probably overhead to repeating the query 4 times, but it shows about an order of magnitude perf difference for me.

Comment 1

[Wesley Johnston (:wesj)]

Attachment: Pull request

This implements the fixes. I also did some other random cleanup as I went through here (like showing urls' when we don't have titles).

Comment 2

[Stefan Arentz | :st3fan | ⏰ EST | he/him]

Comment on attachment 8570144 [details] [review]
Pull request

Added some small comments in the PR. My main concern is that there is too much hardcoded.

Comment 3

[Richard Newman [:rnewman]]

Nick mentioned he was taking this over.

See some notes and query plan analysis in <https://etherpad.mozilla.org/rnewman-history-sql>.

Comment 4

[Richard Newman [:rnewman]]

Did this land?

Comment 6

[Aaron Train [:aaronmt]]

Well?

Comment 7

[Wesley Johnston (:wesj)]

The first suggestion in the initial comment here is already implemented. The second isn't really needed (we've revamped the in-memory partitioning a few times). I'm closing this as Fixed. We can open a meta somewhere to track general perf improvements, (but I think we have that already somewhere...)