sqlite optimization trick

[kinglestat]

hi guys

Code: Select all

Procedure               PragmaSet( table.s, updates.i = 64 )

   Protected            sql.s
   Protected            temp.s
   
   If table > ""
      temp = "PRAGMA " + table + "."
      sql = temp + "cache_size = " + Str( updates )
      DatabaseQuery( gLocalDB, sql )
      sql = temp + "default_cache_size = " + Str( updates )
      DatabaseQuery( gLocalDB, sql )
   Else
      ; Generic optimizations
   
      sql = "PRAGMA encoding = " + gszIC + "UTF-16" + gszIC   ; UTF-8 is default
      DatabaseQuery( gLocalDB, sql )
      sql = "PRAGMA journal_mode = TRUNCATE"  ;MEMORY is faster but unsafe if crash occurs
      DatabaseQuery( gLocalDB, sql )
      sql = "PRAGMA legacy_file_format = OFF"
      DatabaseQuery( gLocalDB, sql )
      sql = "PRAGMA locking_mode = exclusive"
      DatabaseQuery( gLocalDB, sql )
      sql = "PRAGMA page_size = 8192"
      DatabaseQuery( gLocalDB, sql )      
      sql = "PRAGMA synchronous = OFF"
      DatabaseQuery( gLocalDB, sql )
      sql = "PRAGMA temp_store = MEMORY"
      DatabaseQuery( gLocalDB, sql )
      sql = "PRAGMA default_cache_size = 128"
      DatabaseQuery( gLocalDB, sql )
   
   EndIf
   
   
EndProcedure

UTF-16 if you are using unicode of course
Has boosted up my database 6 fold

cheers

EDIT 8/Feb/2009

gLocalDB os handle to your database (in my case its a global)
The order had to be changed as parameters where not being applied properly. Plus I was not freeing the resources AND calling the wrong DB function. Doh!

Code: Select all

Procedure               linxPragmaSet( updates.i = 512 )

   Protected            sql.s
   Protected            temp.s
   
   
   sql = "PRAGMA page_size = 8192"
   DatabaseUpdate( gLocalDB, sql )
   FinishDatabaseQuery( gLocalDB )
   
   sql = "PRAGMA encoding = " + gszIC + "UTF-8" + gszIC   ; UTF-8 is default
   DatabaseUpdate( gLocalDB, sql )
   FinishDatabaseQuery( gLocalDB )
   sql = "PRAGMA journal_mode = TRUNCATE"  ;MEMORY is faster but unsafe if crash occurs
   DatabaseUpdate( gLocalDB, sql )
   FinishDatabaseQuery( gLocalDB )
   sql = "PRAGMA legacy_file_format = OFF"
   DatabaseUpdate( gLocalDB, sql )
   FinishDatabaseQuery( gLocalDB )
   sql = "PRAGMA locking_mode = exclusive"
   DatabaseUpdate( gLocalDB, sql )
   FinishDatabaseQuery( gLocalDB )
   
   sql = "PRAGMA synchronous = OFF"
   DatabaseUpdate( gLocalDB, sql )
   FinishDatabaseQuery( gLocalDB )
   sql = "PRAGMA temp_store = MEMORY"
   DatabaseUpdate( gLocalDB, sql )
   FinishDatabaseQuery( gLocalDB )
   
   sql = "cache_size=" + Str( updates )
   DatabaseUpdate( gLocalDB, sql )
   FinishDatabaseQuery( gLocalDB )
   
   sql = "pragma default_cache_size=" + Str( updates )
   DatabaseUpdate( gLocalDB, sql )
   FinishDatabaseQuery( gLocalDB )
   
   
EndProcedure