On 11/20/07, Scott Krig <[EMAIL PROTECTED]> wrote: > There are apparently no folks with the experience to answer the > questions as given?
Those who have experience know better than to try to answer in an e-mail what 1000 mails in the mailing list are not enough. The wiki and documentation have more than enough information for the level of detail you want. Regards, ~Nuno Lucas > -----Original Message----- > From: Tom Briggs [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 1:41 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > > re: Q1 - Maybe (hopefully) someone will prove me wrong, but I think > that your question is too broad to be answerable (unless you're actually > attempting to assemble a collection of optimal values in all possible > situations for all existing pragmas... ;shrug) > > re: Q2 - At the risk of sounding crass, tuning queries is, has been > and always will be the best way to optimize the performance of any > database. I've done a lot of tuning of SQLite and a half dozen other > databases, and query design is always what has the most impact. > Pragmas, #defines, API usage, etc. are always a distant second in the > race for performance gains. > > -T > > > -----Original Message----- > > From: Scott Krig [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, November 20, 2007 4:24 PM > > To: sqlite-users@sqlite.org > > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > > > To the point, the questions are: > > > > > > 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. > > > > > > > > > > > > -----Original Message----- > > From: Tom Briggs [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, November 20, 2007 10:40 AM > > To: sqlite-users@sqlite.org > > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > > > > > 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] > > -------------------------------------------------------------- > > ---------- > > ----- > > > > > > > > > > -------------------------------------------------------------- > > --------------- > > To unsubscribe, send email to [EMAIL PROTECTED] > > -------------------------------------------------------------- > > --------------- > > > > > > ------------------------------------------------------------------------ > ----- > To unsubscribe, send email to [EMAIL PROTECTED] > ------------------------------------------------------------------------ > ----- > > > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
