I presume only the data structure pointer is static -- the vtab/cursors still need to be dynamic ..
I do not know why access to multiple separate instances of a virtual table would be serialized ... -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Dimitris Bil > Sent: Tuesday, 4 July, 2017 09:19 > To: SQLite mailing list > Subject: Re: [sqlite] Concurrent reads for VTs with in-memory data > structures > > Keith, thanks for your answer. > > > I tried to implement it using multiple connections (each connection from a > different thread) from a single process, but not only all access seems to > be serialized, but the time spent when using threads is worse than the > total time if all queries were executed sequentially. For example, I am > testing the same query on an 8-core machine after I have created the > static in-memory data structures. If I use one thread, the query takes > about 20ms, whereas with two threads the time is about 60ms and with six > threads about 500ms. I am getting the same behavior if I just try to > execute a read from a regular table and also if I use the same connection > for all threads. I have tried different SQLITE_THREADSAFE values, and also > different values for PRAGMA synchronous and PRAGMA journal_mode, as well > as trying to open the connections in read only mode, but nothing seems to > work. > > > I would appreciate any idea on how to make this work properly. > > > ________________________________ > From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on > behalf of Keith Medcalf <kmedc...@dessus.com> > Sent: Monday, July 3, 2017 6:36 PM > To: SQLite mailing list > Subject: Re: [sqlite] Concurrent reads for VTs with in-memory data > structures > > > If it is singleton data the I suppose you could keep a static pointer to > the data structure, a static "use" counter, and a static mutex. > > Then, for each connection (xConnect), lock the mutex, if the static use > counter is zero then build the data structure and increment the use > counter, then set the new connection to use the shared data structure, > then release the mutex. > > For each xDisconnect, lock the mutex, release that set of virtual table > resources, decrement the counter and if the counter is zero, release the > static data, then release the mutex. > > This assumes that the data needs "reloading" after nothing is using it. > If this is not the case you do exactly the same thing except to never > rebuild the data structure after the first time, all you need (inside the > mutex) on the xConnect it to build the static data structure if it does > not exist, and on the xDisconnect simply release the virtual table > resources (but never the shared static data structure). > > This would mean, of course, that the data would be "static" across all > connections in a process, but different processes would have their own > static data. If you need to release and rebuild the data structure this > will happen only when no connection in the process is using connected to > the data. > > By diddling with the data attributes (basically making them all extern > references to a specially constructed data segment) you could make the > static data structure a singleton across multiple processes. However this > is not the default because static data segments are "process local" not > "library local" ... > > -- > ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > > > > -----Original Message----- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Dimitris Bil > > Sent: Monday, 3 July, 2017 11:14 > > To: sqlite-users@mailinglists.sqlite.org > > Subject: [sqlite] Concurrent reads for VTs with in-memory data > structures > > > > I have some virtual tables that keep in-memory data structures. Data > > loading is happening at table creation (xCreate and xConnect are the > same) > > and after that, during querying, only read access is needed. Queries do > > not access any other tables. Is there a way to achieve concurrent > > execution without having to keep multiple copies of each data structure? > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
