> -----Original Message----- > From: Scott Derrick [mailto:[EMAIL PROTECTED] > Sent: Friday, July 20, 2007 8:54 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] quickest way to duplicate table data? > > I am using sqlite in an embedded application. The data store table > will be in its own file. > > The data acquisition application will be storing a row every 1 to 5 > seconds and cannot be interrupted for more than a couple seconds. > > A web server will access the table for read access in two ways. > First, a live view of incoming data will read the latest row every 1 to > 5 seconds. No problem there. > > The second method is the problem as I see it. The user will be able to > request a copy of the last 12 hours, 7 days, or the entire data set, > to be sent to a USB stick or over the network. The data set will be > stored in comma delimited format. > > In order to not interrupt the writing of data from the DAQ application I > think I should replicate the table or database and then dump the > required interval into comma delimited file. > > Would a simple OS file copy of the database file be the quickest way to > copy the table? Locking it first, do a filecopy and then unlock? > > Any other way to copy it faster?
Most modern platforms support some kind of filesystem snapshot (via LVM on Linux, ufssnap on Solaris, VSS on Windows, etc.) so that you can do 1. lock SQLite db 2. Make filesystem snapshot 3. unlock SQLite db 4. Copy sqlite.db from snapshot If snapshotting is fast (as it should be, so long as you don't use modes of VSS which integrate with applications), this might reduce the length of time for which you hold the SQLite database locked. Putting the database on its own dedicated volume could speed this up too. Of course, if your file is smaller than around 50MB, you might well just be able to use a regular OS copy routine inside your 1-2 second window. -- James ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
