Alex, > Le 14 sept. 2016 à 20:29, Alex Ward <cov...@yahoo.com> a écrit : > >> Unless you have a very complex schema (I would say at the very least more >> than on hundred tables and other create statement), opening a SQLite >> connection is lightweight, > > We currently have 500 tables and 1500 triggers in the schema. Perhaps that > is why we didn't have much luck having one connection per thread or opening a > connection per access. Perhaps our schema needs a rework, would one table > with a million rows be better than 500 tables with 2000 rows each? > > We were considering adding a view per table too, that would make it 3000 > elements in the schema, if 100 is considered a lot are we attempting to use > sqlite in a manner that it is not suited for? We may need to consider > another alternative if that's the case, although that would be a shame.
My 100 number is nothing definitive. SQLite stores the text of the schema, and parses the schema on connection. The parser has an impressive speed. The larger/more complex the schema becomes, it clearly will take a little bit longer to parse. But it is very hard to know where to draw the line. Below 100 is certainly small, that does not necessarily make > 100 too big. For sure one million rows (assuming they're not each extraordinary in size) is _not_ a large number of rows. Is it more fitted than 500 tables with 2000 rows each? I can't discuss your schema (and many people here are way more proficient than me in SQL design) with so few knowledge of what's the model of the data. I can only say that if you're artificially splitting a single logical data set in 500 tables of 2000 rows, then I would keep them in a single table. You would have much less triggers also. But I assume this is not simply the case. (Reviewing this text before posting, I read Simon goes even further along the same line. I'd take that as a good incentive to have a closer look at your schema.) This aside, if you have a problem with the time taken by establishing a new connection, then building on what I briefly suggested, I would pool threads without closing their connection when the thread is done with its work and ready to suspend until needed again. This way you would have the benefit of one connection per thread, without the full impact of having to re-open the database file each time a thread has got to do some work. It's a pool of both threads and connections, to gain on the two sides. A simple pool of pre-connected connections could be worth considering (probably what you had in mind) but I would again strongly advise you to refrain to share any of these connections between two (or more) running threads. You would have to either use SQLite in its mode of SERIALIZED (default unless changed at runtime or compile time) or add mutual exclusion between threads sharing a connection if using the MULTITHREAD mode. The MULTITHREAD mode is not a magic mode that 'makes it work' within threaded applications. To the contrary, it is meant for threaded applications which take the whole responsibility of knowing what they're doing. SQLite will not protect concurrent access to the connection state. So bad things will happen, unless very properly serialized. In either solution, threads sharing a same connection are now executing serialized. It might be equivalent and simpler to queue up the requests and process them one after each other through one single thread... If you can achieve/afford one connection per thread, MULTITHREAD mode is then easy to use without risks. Along with the database set for WAL journal mode, all threads doing reads will really have opportunities to work at the same time. Will the time needed to establish a new connection per each thread will kill the benefits of having threads which can actually work simultaneously? Only you will tell. (Sorry for these long answers.) Best, -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
