Which pragmas will be most effective (and what values you should use for each) depends on what you're trying to do with the database. Synchronous is important if you're writing frequently, for example, but won't matter much in a read-only setting. Appropriate values for the page_size and cache_size pragmas vary depending on whether the database is write-mostly or read-mostly and also depending on whether you want to optimize for reading or writing.
So in short, the answer is, it depends. Depends on what you're trying to tune for, that is. -T > -----Original Message----- > From: Scott Krig [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 1:13 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Performance tuning using PRAGMA, other methods > > > 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'; > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
