1347281 - Investigate using a TEMP TABLE to make guid to id lookups quicker

Status: RESOLVED WONTFIX

(Toolkit :: Places, enhancement, P3)

Description

[:Gijs (he/him)]

From bug 1344282:

> I have an half idea on how we could improve this, removing any needed I/O. May be worth a follow-up if you measure these SELECT queries as being expensive for your batch insert.
> We have AFTER_INSERT and AFTER_DELETE triggers in moz_bookmarks, they currently update a static int containing the last inserted id, but that's accessible only from cpp.
> I was wondering about the possibility to extend that, keeping a TEMP TABLE associating guids to ids. Basically i'm thinking to replace the guids Map we have in PlacesUtils with a TEMP TABLE. The triggers could keep the TEMP TABLE updated, querying from it would not cause any I/O.
> I'd not do this here, but it could be worth a try elsewhere.


My system runs on SSDs only, so I'm not sure I can profile the IO impact reliably, but I agree this is worth checking. (Taking the liberty of setting this to P3 for now.)

Comment 1

[Marco Bonardo [:mak]]

I think we should move everything exposed to the outside to use guids, and internals can use id. The mapping in most cases should not be necessary, and eventually we can use RETURNING clause to get what we need from update queries.
Theres meta bug 1071511 tracking conversion, I don't think we should add temp tables.