Hello Roger, Monday, March 20, 2006, 5:19:53 AM, you wrote:
RG> Hi, RG> I'm writing an application that writes to SQLite at least once per RG> second. Is it wise to close the connection between each SQL call or is RG> it better to leave the connection open while the program is running? RG> This is a server application that will run for days and hopefully weeks RG> and months... There is another process which reads from this SQLite db. How often does your reader read? I'd leave it open myself but, there's really no reason to write much faster than the reader reads so, I'd probably try batching up the update into a block, say 10 inserts then wait another 10 seconds. The reason I suggest this is because during each insert, you're locking the database so, with your design the database is locked once a second for N seconds depending on how long the insert takes. That might not leave much of a window for the reader to read. I'm not clear on how granular the locking is so, I don't know if writing to a staging table once a second, then executing a transfer from the staging table to the real table every 10 seconds or so, would be better to open up the lock timing on the real table. Maybe write to a separate staging database then combine them every N seconds so, you have the reliability of having the data in the DB without locking the main DB all the time. If you have a crash, the data in the staging DB can still be sent to the main DB on restart. -- Best regards, Teg mailto:[EMAIL PROTECTED]
