Not logged in

back to the OSM tools main page

How-to optimize SQLite performances

SQLite is a very simple DBMS: it actually is surprisingly fast under many circumstances, but its performances could easily degradate when some really huge DB-file has to be processed.

Just a quick technical rationale: SQLite fully relies on the underlying file system efficiency, no special data access strategy is implemented by the DBMS itself.
So, as a rule-of-the-thumb, since the DB-file size well fits the available RAM size you can expect to get a good buffering support (fast, efficient disk access). On the opposite side, once that the DB-file widely exceeds the available RAM size a long sequence of continuous buffer stalls will be experienced, thus causing performance degradation (unexpected slowness).
Different operating systems have different intrinsic file system efficiency, and the brute force of the underlaying hardware (HDD) obviously plays a strong role in all this. On some platforms disk fragmentation could actually be a real plague, and antivirus software very often are obnoxious performance killers.

Anyway SQLite actively supports few configuration options really useful in order to get a real performance boost:
All SpatiaLite's OSM tools supports the same arguments set useful in order to control DB optimization:
back to the OSM tools main page