Tom Brigg's response to your question 2 was an excellent response. Efficiently constructed queries will generally produce the best results.
Also, indexes on fields contained in WHERE clauses will generally produce good results. Lee _________________________________ -----Original Message----- From: Scott Krig [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 3:03 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods There are apparently no folks with the experience to answer the questions as given? -----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] -----------------------------------------------------------------------------
