[Solved] [SQLite] DatabaseUpdate ultra slow

[grabiller]

Hi,

Before I post some code and go into more details, I'm assuming DatabaseUpdate() do a commit on each request.

I'm building a database with all OpenGL constants and functions signatures, so far I'm adding to the database around 600 constants in a table.

It takes 30 to 40 seconds to do so

By the past I've used SQLite extensively in C and I've been able to add much more data in one shot for a fraction of the time.

So I'm wondering if it is possible from native PureBasic Database commands to use DatabaseUpdate() in a way it won't commit every time and then we do a commit at the end ?

I did not find infos on this. But perhaps I'm not using the native PB Database commands correctly.

Have you experienced such timing with lot of additions into a SQLite databases with native PB commands ?

[DarkDragon]

Afaik its the database, which does the commit on every command if no transaction was opened. To open a transaction use this:
http://www.sqlite.org/lang_transaction.html

[EDIT]
Ugh, the forums wen't down when I tried to send it.

[grabiller]

Oh right,

I forgot about the SQL BEGIN/COMMIT commands.., sorry about that. In C I was using apis encapsulating that into specific functions.

I just have to:

Code: Select all

DatabaseUpdate( dbid, "BEGIN;" )

DatabaseUpdate( dbid, "INSERT INTO ../.." )
DatabaseUpdate( dbid, "INSERT INTO ../.." )
; ../..

DatabaseUpdate( dbid, "COMMIT;" )

And now it takes less than a second.

Thanks for reminding me.

[r7mk4]

Ten years later....

Same thing here!
You solved it!

Thank's a lot