[fdo-internals] SQLite provider

[Traian Stanev]

They are non-breaking for the format, but rely on some assumptions about usage patterns that are unique to the provider, and are probably not useful for SQLite in general. The 3-4 additional if checks involved would make the general case ever so slightly slower.

For example, since I can assume that access to the connection will be sequential, I can get away with referencing values directly in the SQLite B-Tree memory instead of copying them over (which also implies that it saves us memory allocations). This means for example that strings are not null-terminated and the provider knows how to deal with that fact.

Also, I modify the current instruction pointer in the compiled bytecode for the SQL query being executed (in order to prevent it from opening and closing the B-Tree every time we step the query), and also modify the bytecode itself when SQLite pauses query execution for the client to read a row (in order to set the next FID to seek to, based on the spatial iterator, if any). Some pretty bold assumptions there, but well worth the performance gain - it is faster to read the database with the provider than with SQLite by itself.

This fast code path is only used when the select command fits certain common patterns. In other cases, it simply uses regular SQLite C APIs and does not mess with the bytecode. Another thing I changed was to make the engine compute byte offsets only up to the column that is currently needed. Normally SQLite does it for all columns in a row, and this is slow if one has 100+ columns. This is also why the converter tool makes it so that the ID and geometry are first in the table, followed by all other attributes.

I am aware of the changes made in the new version (he rewrote the execution engine to be register based rather than stack based), and so far it looks to me like they do not affect the shortcuts I am using - they may affect the instruction pointer hack, but the other two changes would carry over unchanged. I will re-apply my patch when the time comes to upgrade to a new SQLite - it is less than 50 lines of code.

Traian


From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Jason Birch
Sent: Saturday, April 19, 2008 1:28 AM
To: FDO Internals Mail List
Subject: RE: [fdo-internals] SQLite provider

You mentioned making some performance improvements to SQLite for this provider.  Are these generic/nonbreaking changes that could be contributed back to the SQLite project?

I guess there may be some problems here because we're on 3.5.4, and "Version 3.5.5 changes over 8% of the core source code of SQLite " [1]  :(

http://www.sqlite.org/news.html

Jason

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-internals/attachments/20080418/786f7fa0/attachment-0001.html