sqlite and pragmas

Just starting out? Need help? Post your questions and find answers here.
coffee
User
User
Posts: 77
Joined: Fri Oct 06, 2017 10:43 am

sqlite and pragmas

Post by coffee »

hi,

i would like to set, for example

Code: Select all

PRAGMA cache_size=20000
PRAGMA synchronous=OFF
PRAGMA journal_mode=MEMORY
for a sqlite database with program code at database creation. how is that done?
it works from command line, but but not from purebasic code.

does anyone know?
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: sqlite and pragmas

Post by infratec »

coffee
User
User
Posts: 77
Joined: Fri Oct 06, 2017 10:43 am

Re: sqlite and pragmas

Post by coffee »

hi,
thank you for the links.
i do exactly what they did, but when i look with SQLiteExpert on the resultant database, the settings (pragmas) were not set.
when i set the pragmas with SQLiteExpert, they are set and visible.
how is that done programatically?
the way we do it wright now, seems not to be working, if i trust the display of SQLiteExpert

coffee
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: sqlite and pragmas

Post by infratec »

Hi,

I don't think that (all) the PRAGMA stuff ist stored somewhere.
If you look at the sqlite header, you find only few fields for PRAGMA stuff

http://www.sqlite.org/fileformat2.html#database_header

Maybe SQLiteExpert stores this stuff in an extra table.

To check it, simply set the journal mode to memory, start a transaction and see if there is a temporary file or not.

Bernd
coffee
User
User
Posts: 77
Joined: Fri Oct 06, 2017 10:43 am

Re: sqlite and pragmas

Post by coffee »

hi,
you were correct.
i read on some webpages on optimization, that these pragmas need to be set before before the creation of tables.
well it turns out you need to set them directly ofter opening the db - and that every time.
thank you for your help!!!!
User avatar
Kukulkan
Addict
Addict
Posts: 1352
Joined: Mon Jun 06, 2005 2:35 pm
Location: germany
Contact:

Re: sqlite and pragmas

Post by Kukulkan »

We use this and we simply execute the following pragmas after every call to OpenDatabase() using DatabaseUpdate():

Code: Select all

PRAGMA synchronous = 0
PRAGMA temp_store = 2
PRAGMA journal_mode = WAL
This massively increased the speed of our application, which is using several threads to use the same SQLite database in parallel.

But please be aware: choosing pragmas like this may affect the stability or security of your database! In our case it was not that critical as a database loss is no big deal. But this depends on your needs. Have a look at https://sqlite.org/pragma.html to find the various options and their functionality.
coffee
User
User
Posts: 77
Joined: Fri Oct 06, 2017 10:43 am

Re: sqlite and pragmas

Post by coffee »

thanks, these settings help me a lot.
coffee
Post Reply