Actually, having benched this specific thing before, I can tell you that there is a performance penalty opeining and closing DBs (as
opposed to just keeping a connection open) - but this is not news, what might be news is that the performance penalty is much much
smaller than I anticipated and in fact probably negligible for any sort of web application - so my advice would be to go with what
suits your feelings best and the rest of your app best.
One comment: I see you wirte "...keep the database connection open as other users would/could/might want to add stuff to the
queue..." - AFAIK there is zero impact on who else wants to write to your database, any other connection can still write to it (just
not at the exact same time, but even then SQLite will handle the timing/locking/writing very well). Only worry about this if your
connection locks up a table for a long time - but my guess is that either it doesn't, or there's nothing you can do about it, the
data MUST be updated etc.
Btw, to all, I concur very much with Rob as to the quality of replies on this
forum - many thanks to all!
On 2013/06/19 16:45, Rob Willett wrote:
Stephen, Simon,
I'm not worried about the CPU load at all, it was purely a question of style.
Stephan has talked about keeping the connections open for the lifetime of a
desktop connection, Simon has talked about closing and opening connections
many, many times during the course of a transaction. My model is more towards
Simon's.
Part of this model is due to the backend process taking hours to run. I wanted
the web user to kick this off and come back when it's finished (possible
tomorrow). It didn't feel write to keep the database connection open as other
users would/could/might want to add stuff to the queue to be run.
The fact I have had two replies, and thank you both for writing back, but have
very different viewpoints and experiences makes me think that my model isn't
bad for my particular use case. Clearly other people will have different
experiences and in different situations I would do it differently.
On another note, I have been impressed with the quality of replies on this list. I listen
into other lists on other technologies and the quality and standard of 'stuff' here is
very high. Not seen very many "My database don't work, fix it for me" type
mails here which is great. Some of the detail people go into is wonderful and I've
learnt an awful lot just reading replies.
Best wishes,
Rob.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
