<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<title>RE: [fdo-internals] SQLite provider</title>
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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. <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Traian<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
fdo-internals-bounces@lists.osgeo.org
[mailto:fdo-internals-bounces@lists.osgeo.org] <b>On Behalf Of </b>Jason Birch<br>
<b>Sent:</b> Saturday, April 19, 2008 1:28 AM<br>
<b>To:</b> FDO Internals Mail List<br>
<b>Subject:</b> RE: [fdo-internals] SQLite provider<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div id=idOWAReplyText66554>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>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? </span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>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] :(</span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'><a
href="http://www.sqlite.org/news.html">http://www.sqlite.org/news.html</a></span><o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Jason</span><o:p></o:p></p>
</div>
</div>
<div>
<p class=MsoNormal> <o:p></o:p></p>
</div>
</div>
</div>
</body>
</html>