*** 1,7 **** <html> <h1> Performance Tuning</h1> ! <p>This page is an area where you can place code, hints, suggestion or text that relates to increasing the speed of the SQLite databases.</p> <h2>Place indexes on join and where column</h2> --- 1,7 ---- <html> <h1> Performance Tuning</h1> ! <P>Any questions about the meaning of the content of this page can be sent to vbsqliteNOSPAM@NOSPAMag-software.com (just remove he NOSPAM)</P> <p>This page is an area where you can place code, hints, suggestion or text that relates to increasing the speed of the SQLite databases.</p> <h2>Place indexes on join and where column</h2> *************** *** 20,40 **** <i><b>SQLite implements JOIN USING by translating the USING clausing into some extra WHERE clause terms. It does the same with NATURAL JOIN and JOIN ON. So while those constructs might be helpful to the human reader, they don't really make any difference to SQLite's query optimizer.</b></i> </blockquote> ! <p>So if there work in working out joins it stands to reason that doing that work for SQLite will speed up the compile times. If you are returning 100's of rows this might not matter but if you are returning 1 row many times this might be a big issue. (<i>Does anyone understand this paragraph? I don't. It should probably be rewritten or removed.</i>)</p> - <P> I think the above paragraph is pointing out that SQLite translates the JOIN syntax - to WHERE expression clauses before byte-coding. The suggestion is that using - WHERE clauses in your SQL statements will eliminate this step and thus speed - up the compilation of the SQL. This is probably true, but the savings will - be quite miniscule. Perhaps around 2 or 3 - malloc()/free() calls per JOIN clause. <a href="http://www.sqlite.org/cvstrac/wiki?p=PrecompiledSql">PrecompiledSql</a> might be a better trick. - </P> <H2>Order your tables properly in your where clause</H2> <p>Place the tables where you can eliminate the most rows by using a where clause (preferably on an indexed column) first, in order to limit the number of JOIN operations required.</p> ! <H2>Idexes on INTEGER PRIMARY KEY columns (don't do it)</H2> <p>When you create a column with INTEGER PRIMARY KEY, SQLite uses this column as the key for (index to) the table structure. This is a hidden index (as it isn't displayed in SQLite_Master table) on this column. Adding another index on the column is not needed and will never be used. In addition it will slow INSERT, DELETE and UPDATE operations down.</p> --- 20,33 ---- <i><b>SQLite implements JOIN USING by translating the USING clausing into some extra WHERE clause terms. It does the same with NATURAL JOIN and JOIN ON. So while those constructs might be helpful to the human reader, they don't really make any difference to SQLite's query optimizer.</b></i> </blockquote> ! <p>SQLite converts joins into where statements. This requires CPU time to perform the conversion, however small that CPU time might be. If you are calling a lot of SQL statements which are returning small amount of rows then it might reduce the overall time (CPU cost) if you manually remove the JOIN and replace them with where statements.<br>While savings on this might be small in tight loops of 1000's or in low memory situations it could be worth the coding. When SQLite supports a prepare/precompile interface then precompiling the SQL Statements will be the better way to go.<p> <H2>Order your tables properly in your where clause</H2> <p>Place the tables where you can eliminate the most rows by using a where clause (preferably on an indexed column) first, in order to limit the number of JOIN operations required.</p> ! <H2>Indexes on INTEGER PRIMARY KEY columns (don't do it)</H2> <p>When you create a column with INTEGER PRIMARY KEY, SQLite uses this column as the key for (index to) the table structure. This is a hidden index (as it isn't displayed in SQLite_Master table) on this column. Adding another index on the column is not needed and will never be used. In addition it will slow INSERT, DELETE and UPDATE operations down.</p>
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|