lieblerj / poi_converter

Convert openandromaps poi databases into Locus osm.db format
Apache License 2.0
7 stars 3 forks source link

Improve performance by batching inserts #6

Closed noschinl closed 4 years ago

noschinl commented 4 years ago

By batching inserts, performance can be greatly improved.

On my machine, runtime for importing Spain_Portugal.db went from 3m00 to 1m00. I verified that the resulting db contains the same elements by comparing the results of the query

select p.id, p.type, p.name, FR.name, FS.name, TK.name, TV.name
from Points p
         join Points_Root_Sub PRS on p.ROWID = PRS.Points_id
         join Points_Key_Value PKV on p.ROWID = PKV.Points_id
         join FoldersRoot FR on PRS.FoldersRoot_id = FR.id
         join FoldersSub FS on PRS.FoldersSub_id = FS.id
         join TagKeys TK on PKV.TagKeys_id = TK.id
         join TagValues TV on PKV.TagValues_id = TV.id
order by p.id
;

on the db before and after the change.

noschinl commented 4 years ago

This patch does not handle the append mode correctly, you will get duplicate Ids (actually, what is the use case for this mode?). I'll post a patch soon.

noschinl commented 4 years ago

Fixed the bug above; added two changes: