sqlite - Performance Tuning

Not logged in

• Honeypot
• Browse
• Home
• Login
• Reports
• Search
• Ticket
• Timeline
• Wiki

• Contents
• No-Diff
• Text

*** 26,33 ****
  
  <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>
  
  </html>
--- 26,50 ----
  
  <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>
+ 
+ <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>