Sign in to follow this  
Followers 0
Sn3akyP3t3

Sqlite attach strange behavior

5 posts in this topic

I needed a little encryption in my database just to make one particular column less of a gold mine if it ever gets pulled off the machine and distributed. The column happens to be email addresses.

A little more background on why I was trying to perform all my database operations in memory first. I was trying to decrease the amount of time required for the large number of INSERT operations performed. I realize that adding some encryption to the process increased the time per loop, but the hard drive seemed to be very busy so I tried to reduce that bottleneck to a minimum by incorporating the database to memory and then afterwards saving it to file.

The database file exists with the table structures already created. I run the following commands:

_SQLite_Startup()
$hDBMEM = _SQLite_Open(":memory:")
_SQLite_Exec($hDBMEM, "ATTACH DATABASE '" & $sSqliteDatabasePath & '\' & $sSqliteDatabaseFilename & "' AS memoryMedia")
< Pull data from a server's mysql database>
< Insert data from mysql to sqlite database, about 4000 records>
_SQLite_Close($hDBMEM)
_SQLite_Shutdown()

Without any attempt to save the database while operating in memory still results in the database being saved to file after the above operations are completed. During runtime I also noticed that the hard drive still appears to be very active which indicates that the database insert operations are being performed to the file and not to memory as expected. I've been trying to figure out why this occurs because the expected behavior is anything in memory is volatile and should be destroyed after sqlite terminates. Is my syntax incorrect for attaching a database existing as a file to memory?

Share this post


Link to post
Share on other sites



Are you operating in the wrong DB by accident?

_SQLite_Exec($hDBMEM, "ATTACH DATABASE '" & $sSqliteDatabasePath & '\' & $sSqliteDatabaseFilename & "' AS memoryMedia")

should be

_SQLite_Exec($hDBMEM, "ATTACH DATABASE '" & $sSqliteDatabasePath & '\' & $sSqliteDatabaseFilename & "' AS FileDatabase")

Share this post


Link to post
Share on other sites

Regarding encryption, the SQLite team recently announced that:

As of this release, the popular ADO.NET provider for SQLite by Robert Simpson, System.Data.SQLite, is hosted on the SQLite website. See http://System.Data.SQLite.org/ for additional information. Release builds of System.Data.SQLite will appears on the SQLite download page over the course of the next week.

System.Data.SQLite offers optional encryption for free.

Now regarding your performance issue, not only carefully check which DB you attach at which one as KaFu pointed out, but you also would greatly benefit of embracing your bulk inserts in a transaction:

_SQLite_Exec($hDbMem, "begin;")

...<your INSERT loop>

_SQLite_Exec($hDbMem, "commit;")

BTW, 4000 inserts are a fairly low load (inside a transaction!). You don't need the memory DB for inserting only 4k rows, even with triggers and constraints.

Try that and chime again if you need more tuning (there are many possible ways to speedup things).

Also don't forget that :memory: DBs are destroyed upon closing the connection (just like temporary tables), unless backed up to disk. So perform your processing before _SQLite_Close!


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

@KaFu

I'll check to see if I'm referencing my memory database correctly. Running low on sleep can twist logic in my experience and I could have been doing it wrong. Thanks for the advice.

Regarding encryption, the SQLite team recently announced that:

As of this release, the popular ADO.NET provider for SQLite by Robert Simpson, System.Data.SQLite, is hosted on the SQLite website. See http://System.Data.SQLite.org/ for additional information. Release builds of System.Data.SQLite will appears on the SQLite download page over the course of the next week.

System.Data.SQLite offers optional encryption for free.

Now regarding your performance issue, not only carefully check which DB you attach at which one as KaFu pointed out, but you also would greatly benefit of embracing your bulk inserts in a transaction:

_SQLite_Exec($hDbMem, "begin;")

...<your INSERT loop>

_SQLite_Exec($hDbMem, "commit;")

BTW, 4000 inserts are a fairly low load (inside a transaction!). You don't need the memory DB for inserting only 4k rows, even with triggers and constraints.

Try that and chime again if you need more tuning (there are many possible ways to speedup things).

Also don't forget that :memory: DBs are destroyed upon closing the connection (just like temporary tables), unless backed up to disk. So perform your processing before _SQLite_Close!

@jchd

I've only scratched the surface of tooling around with Sqlite. I'll try to adjust my code to take advantage of the referenced "bulk transaction" technique. I think my performance issue is mainly responsible to my looping structure and the design of Sqlite to write to the HDD before moving onward with another transaction to mitigate the possibility of data corruption on system or hardware failure.

Share this post


Link to post
Share on other sites

@jchd

I've only scratched the surface of tooling around with Sqlite. I'll try to adjust my code to take advantage of the referenced "bulk transaction" technique. I think my performance issue is mainly responsible to my looping structure and the design of Sqlite to write to the HDD before moving onward with another transaction to mitigate the possibility of data corruption on system or hardware failure.

SQLite performs an "autocommit" for every individual SQL statement not explicitely wrapped inside a transaction. That in turn causes it to request that (for instance after every insert) the OS flushes the data to the magnetic surface (and I'm skipping _much_ details here). Doing so requires prior acquisition of a write lock on the file, and release of this lock after completion. This is inappropriate for bulk write operations (insert, update, replace).

A transaction makes any changes within it either complete (success) or void (error happened) as if the whole lot were only one change. As a side bonus, this requires _much_ much less physical operations than the same series of individual writes in autocommit mode.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0