BTW, several PRAGMAS actually increase performance in my embedded app
case - maybe 15-30% depending upon transaction activity and the way I
structure transaction commits. Specific PRAGMAS that helped include:
//
// Synchronous OFF (0)
//
rc = sqlite3_exec(*dbHandle, "PRAGMA synchronous = 0;", 0, 0, 0);
//
// Keep temporary storage in MEMORY (2) instead of a file
//
rc = sqlite3_exec(*dbHandle, "PRAGMA temp_store = 2;", 0, 0, 0);
//
// Allow reads from uncommitted memory containing DB tables/records
//
rc = sqlite3_exec(*dbHandle, "PRAGMA read_uncommitted = 1;", 0, 0,
0);
//
// Exclusive access to DB to avoid lock/unlock for each transaction
//
rc = sqlite3_exec(*dbHandle, "PRAGMA locking_mode = exclusive;", 0,
0, 0);
-----Original Message-----
From: Scott Krig [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 20, 2007 2: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]
-----------------------------------------------------------------------------
