SQLite CVSTrac

*** 31,50 ****
  <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>
  
  <h2>Use transactions when updating tables</h2>
- make sure that you wrap up all multiple updates inside a transaction.<br>
- i.e. (The word transaction is optional)<br>
- <b>
- Begin Transaction<br>
- Update table1 set Col1='1'<br>
- Update table1 set Col1='2'<br>
- ...<br>
- insert into table1(col1) values('2')<br>
- ...<br>
- ...<br>
- Commit Transaction<br>
- </b>
- <br>Using transaction for updates is the fastest way to update SQLITE.  Basically this is how it works after each transaction the SQLite engine closes and opens the database file.  When SQLite opens a database file is populates the SQlite internal structures , which takes time.  So if you have 100 updates and don't use a transaction then SQlite will open and close the database 100 times.  Using transactions improves speed, use them.
  
  
  
  </html>
--- 31,52 ----
  <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>
  
  <h2>Use transactions when updating tables</h2>
  
+ <p>Make sure that you wrap up all multiple updates inside a transaction,
+ e.g.:</p>
  
+ <pre>
+ BEGIN TRANSACTION;
+ UPDATE table1 SET col1='1';
+ UPDATE table1 SET col1='2';
+ ...
+ INSERT INTO table1(col1) VALUES ('2');
+ ...
+ COMMIT TRANSACTION;
+ </pre>
+ 
+ <p>(The word "TRANSACTION" is optional.)</p>
+ 
+ <p>Using a transaction is the fastest way to update data in SQLite.  Basically, this is how it works: After each transaction the SQLite engine closes and opens the database file.  When SQLite opens a database file it populates the SQlite internal structures, which takes time.  So if you have 100 updates and don't use a transaction then SQlite will open and close the database 100 times.  Using transactions improves speed. Use them.</p>
  
  </html>

sqlite - Performance Tuning