Hi Scott,
Thanks for the information related to Performance tunning using PRAGMA.
My embeded app needs to be tunning too.
I have serveral questions for you.
Current I had these PRAGMAS in my app:

 sqlSt = sqlite3_exec(pDb, "PRAGMA page_size=32768", NULL, 0, &errMsg); 
  sqlSt = sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, &errMsg); 
  sqlSt = sqlite3_exec(pDb, "PRAGMA temp_store = MEMORY ", NULL, 0, &errMsg); 
  sqlSt = sqlite3_exec(pDb, "PRAGMA cache_size = 2000 ", NULL, 0, &errMsg); 
 Questions:
1) Do I need this : sqlSt = sqlite3_exec(pDb, "PRAGMA page_size=32768", NULL, 
0, &errMsg); 

2) sqlSt = sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, &errMsg); 
    is this the same as your :  rc = sqlite3_exec(*dbHandle, "PRAGMA 
synchronous = 0;", 0, 0, 0);

3) sqlSt = sqlite3_exec(pDb, "PRAGMA temp_store = MEMORY ", NULL, 0, &errMsg); 
    is this the same as your :  rc = sqlite3_exec(*dbHandle, "PRAGMA temp_store 
= 2;", 0, 0, 0);

4) Do I need this : sqlSt = sqlite3_exec(pDb, "PRAGMA cache_size = 2000 ", 
NULL, 0, &errMsg); 

5)  Also Do I need these two setting as you mentioned in your previous email
        rc = sqlite3_exec(*dbHandle, "PRAGMA read_uncommitted = 1;", 0, 0,0);
       rc = sqlite3_exec(*dbHandle, "PRAGMA locking_mode = exclusive;", 0,0, 0);

Thank you so much.
JP

----- Original Message ----
From: Scott Krig <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 29, 2007 10:56:37 AM
Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods

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]
-----------------------------------------------------------------------------


      
____________________________________________________________________________________
Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

Reply via email to