[sqlite] Performance tuning using PRAGMA, other methods

[Scott Krig]

 
What are the 'biggest bang for the buck' sqlite optimization techniques
to apply to a working system to tune performance?

Q1)) PRAGMA: Does anyone have experience and good results optimizing
sqlite performance using PRAGMA's? If so, which ones, how were they
used, and what was the performance increase?
Q2)) Other techniques: Any success stories on sqlite optimization
methods of any type would be appreciated.
 
Thanks.
 
 
Scott
-=-
 
 
 
Here is a list of the PRAGMA examples from the sqlite documentation:
 
 
 
PRAGMA auto_vacuum;
PRAGMA auto_vacuum = 0 | none | 1 | full | 2 | incremental;
 
PRAGMA cache_size; 
PRAGMA cache_size = Number-of-pages;
 
PRAGMA case_sensitive_like; 
PRAGMA case_sensitive_like = 0 | 1;
 
PRAGMA count_changes; 
PRAGMA count_changes = 0 | 1;
 
PRAGMA default_cache_size; 
PRAGMA default_cache_size = Number-of-pages;
 
PRAGMA default_synchronous;
 
PRAGMA empty_result_callbacks; 
PRAGMA empty_result_callbacks = 0 | 1;
 
PRAGMA encoding; 
PRAGMA encoding = "UTF-8"; 
PRAGMA encoding = "UTF-16"; 
PRAGMA encoding = "UTF-16le"; 
PRAGMA encoding = "UTF-16be";
 
PRAGMA full_column_names; 
PRAGMA full_column_names = 0 | 1;
 
PRAGMA fullfsync 
PRAGMA fullfsync = 0 | 1;
 
PRAGMA incremental_vacuum(N);
 
PRAGMA legacy_file_format; 
PRAGMA legacy_file_format = ON | OFF
 
PRAGMA locking_mode; 
PRAGMA locking_mode = NORMAL | EXCLUSIVE
PRAGMA main.locking_mode=EXCLUSIVE; 
 
PRAGMA page_size; 
PRAGMA page_size = bytes;
 
PRAGMA max_page_count; 
PRAGMA max_page_count = N;
 
PRAGMA read_uncommitted; 
PRAGMA read_uncommitted = 0 | 1;
 
PRAGMA short_column_names; 
PRAGMA short_column_names = 0 | 1;
 
PRAGMA synchronous; 
PRAGMA synchronous = FULL; (2) 
PRAGMA synchronous = NORMAL; (1) 
PRAGMA synchronous = OFF; (0)
 
PRAGMA temp_store; 
PRAGMA temp_store = DEFAULT; (0) 
PRAGMA temp_store = FILE; (1) 
PRAGMA temp_store = MEMORY; (2)
 
PRAGMA temp_store_directory; 
PRAGMA temp_store_directory = 'directory-name';