"Bert Nelsen" <bert.nel...@googlemail.com> schrieb
im Newsbeitrag
news:a5ffd531001010911r3de60ec1o44e2c14bce7a7...@mail.gmail.com...
> So SQLite looks at both the database on the disk
> and in memory?
> Wouldn't that be difficult???
Of course... ;-)
And what's so amazing with the SQLite-engine -
is, that all that comes in such a small package.
Regarding "aggregated writes" and transactions again...
What SQLites async-writer thread does, is to
implement something like a "delayed write" at
the DB-engine-level (instead to rely on such a
feature to be implemented at the filesystem-level).
Delayed writes can ensure better performance, since
the new gathered (to be written) data can be grouped
(sometimes also reordered) into larger chunks,
to let the "real disk actions" happen within a more
optimal (more efficient) "operation-window", so to say.
In case of your (smaller) VB-based application you can
achieve something like that also with an "always opened"
transaction, gathering the new to be written data (records)
using SQLites internal transaction-cache-mechanisms -
and then writing it out (syncing it) to disk in a somewhat more
"relaxed" fashion (with better efficiency) from within a timer-
event - thereby avoiding the usage of threads.
That said, I'd recommend the following "approach" only
for smaller Apps/Tools which "own the DB exclusively" -
and are not expected to grow much over time (implementing
smaller logging-scenarios for example - as in your case for
the incoming GPS-data).
At App-startup (e.g. in Form_Load of the VB-App):
Private Sub Form_Load()
InstantiateAndOpenTheConnection
Cnn.BeginTrans 'ensure an opened transaction
'... other init-stuff
End Sub
'in a Timer on that Form (e.g. set to 200-500msec)
Private Sub tmrDelayedWrite_Timer()
If Cnn.TransactionStackCounter = 0 Then
Cnn.BeginTrans 'just in case... (we're not expecting to reach here)
Else
Cnn.CommitTrans 'sync the currently "cached" content
Cnn.BeginTrans 'and reopen a new transaction after that
End If
End Sub
And on App-ShutDown just ensure, that "all the rest" is written too
Private Sub Form_Unload(Cancel As Integer)
tmrDelayedWrite.Enabled = False 'disable the timer first
If Cnn.TransactionStackCounter Then
Cnn.CommitTrans 'write the remaining stuff
End If
End Sub
As said, in that mode you should work only with "proven
commands" which are expected to "never fail" (with regards
to an then unnecessary Cnn.RollBackTrans, which could "mess
up" the whole simplified or "globalized" approach above).
And you should *not* work with the wrappers
Recordset.UpdateBatch-feature in that mode, since this
method performs an implicit transaction on the (Rs-internally)
gathered data (from Rs.AddNew, Rs.Delete or Rs-FieldChanges).
So, to be entirely sure, you could open all the Recordsets
ReadOnly (using the Optional Flag in the OpenRecordset-
method) - and then work only with Cnn.Executes or better
the Command-Objects in write-direction.
This way the "real writes" - the syncing - only happens within
the timer-event - you will risk no DB-corruption this way -
and in case of an unexpected Close of the App (due to whatever
reason), you will lose only the new data which was gathered
within the last timer-interval.
Olaf Schmidt
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
