Sqlite attach strange behavior

[Sn3akyP3t3]

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?

[KaFu]

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")

[jchd]

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!

[Sn3akyP3t3]

@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.

[jchd]

@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.