Open->executeSql->close

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Open->executeSql->close

Roger Gullhaug
Hi,

I'm writing an application that writes to SQLite at least once per
second. Is it wise to close the connection between each SQL call or is
it better to leave the connection open while the program is running?
This is a server application that will run for days and hopefully weeks
and months... There is another process which reads from this SQLite db.

Reply | Threaded
Open this post in threaded view
|

RE: Open->executeSql->close

Chethana, Rao (IE10)
I think its better to close, since there is another process reading from
this db.

-----Original Message-----
From: Roger Gullhaug [mailto:[hidden email]]
Sent: Monday, March 20, 2006 2:20 AM
To: [hidden email]
Subject: [sqlite] Open->executeSql->close

Hi,

I'm writing an application that writes to SQLite at least once per
second. Is it wise to close the connection between each SQL call or is
it better to leave the connection open while the program is running?
This is a server application that will run for days and hopefully weeks
and months... There is another process which reads from this SQLite db.

Reply | Threaded
Open this post in threaded view
|

Re: Open->executeSql->close

Martin Engelschalk
Hi,

i disagree. As far as i understand sqlite, if you sqlite3_finalize()
your statement and close your transaction, there is no reason why the
connection can not remain open. It will not hinder any reading processes.

Martin

Chethana, Rao (IE10) schrieb:

>I think its better to close, since there is another process reading from
>this db.
>
>-----Original Message-----
>From: Roger Gullhaug [mailto:[hidden email]]
>Sent: Monday, March 20, 2006 2:20 AM
>To: [hidden email]
>Subject: [sqlite] Open->executeSql->close
>
>Hi,
>
>I'm writing an application that writes to SQLite at least once per
>second. Is it wise to close the connection between each SQL call or is
>it better to leave the connection open while the program is running?
>This is a server application that will run for days and hopefully weeks
>and months... There is another process which reads from this SQLite db.
>
>  
>
Reply | Threaded
Open this post in threaded view
|

Re: Open->executeSql->close

John Stanton-3
In reply to this post by Roger Gullhaug
Roger Gullhaug wrote:
> Hi,
>
> I'm writing an application that writes to SQLite at least once per
> second. Is it wise to close the connection between each SQL call or is
> it better to leave the connection open while the program is running?
> This is a server application that will run for days and hopefully weeks
> and months... There is another process which reads from this SQLite db.
>
>
Leave it open, and better still use sqlite3_prepare to compile the SQL
just once, then use sqlite3_reset between calls.  You then avoid the
overhead of opening and closing the database and of compiling the same
SQL every second.
Reply | Threaded
Open this post in threaded view
|

Re: Open->executeSql->close

D. Richard Hipp
In reply to this post by Roger Gullhaug
"Roger Gullhaug" <[hidden email]> wrote:
> Hi,
>
> I'm writing an application that writes to SQLite at least once per
> second. Is it wise to close the connection between each SQL call or is
> it better to leave the connection open while the program is running?
> This is a server application that will run for days and hopefully weeks
> and months... There is another process which reads from this SQLite db.

It is better to leave it open.  Just be sure to call sqlite3_reset()
or sqlite3_finalize() on each statement after it runs.
--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Open->executeSql->close

Teg-3
In reply to this post by Roger Gullhaug
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:[hidden email]