But that means, if there is a power off or an system crash, your data which you send at this moment, goes nirvana. But indeed it would be faster and also would save the hard disc from some writing operations.
Am 01.01.2010, 23:33 Uhr, schrieb Olaf Schmidt <s...@online.de>: > > "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 > -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
