Hi, I wasn't overaly bothered about performance, since my backend jobs take hours to run and processing TB's of data, the overhead of opening and closing a few hundred database connections in the grand scheme of things is pretty low. It was a general style and usage question. I'm a C hacker by preference and have my own style of how I use memory, how I break things down into functions and use pointers and structures. I'm not a SQLite expert (pretty much a novice in fact) and wanted to get practises in from the beginning rather than trying to embed them later.
My backend process might run for an hour or two and only write out a few hundred bits of data, so I'll carry on opening and closing it as needed unless someone can say there's good practise not to do it. I like the idea of localised database transactions within the code, it's a bit like malloc and free. Keep it short and sweet if you can. Thanks again for the sensible and constructive replies. Rob. On 19 Jun 2013, at 20:15, RSmith <rsm...@rsweb.co.za> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
