RE: [sqlite] VFS in upcoming 3.5.0
You can create a 'shared memory VFS' to share a memory database against other thread / processes, and you can also 'copy' the RAW bytes of your memory with memcpy from/to another storage to accomplish the serialize / load you want to wire transfer SQLite memory databases. But this is not a simple code, may be after some development, it could be give to public domain and merged into SQLite if it prove to be robust and safe enough. -Original Message- From: Olaf Schmidt [mailto:[EMAIL PROTECTED] Sent: sábado, 1 de setembro de 2007 12:15 To: sqlite-users@sqlite.org Subject: [sqlite] VFS in upcoming 3.5.0 Hi, first - congrats to the planned changes in the new, upcoming version of SQLite. The new shared-cache behaviour sounds promising and also the new VFS-option. A few questions to VFS. 1. As known, an InMemory-DB is currently not (much) faster than working against a File. With the new VFS I think, that much faster InMemory- DB-Handling should be possible, is that right? 2. If so, is it planned, to automatically instantiate an appropriate (already built in) InMemory-VFS, if one sets the Filename-Param to ':memory:' in an Open-Call, so that InMemory DBs work against this implicite created MemVFS - meaning that the "Default-SQLite- engine" already implements such an "InMemory-VFS" for us "Wrapper- developers" (because you know best, how to do it in the fastest possible way and because of my following "feature-request" below)? ;-) 3. If you plan something like this, it would be very nice, if you could include (now that many new interfaces are coming in either way) an additional API-enhancement, wich would allow, to get the current "Byte-Content" of an InMemory-DB, wich makes use of this new (built in) InMemory-VFS? The background for these questions is, that we use SQLite behind an Appserver wich is currently able, to get Resultset- Objects at the serverside (done over our wrapper) and after retrieving such an Resultset, to serialize its "Query-Content" into a ByteArray, wich is then transferred over sockets back to the client. At the clientside we are able, to deserialize the Bytes and "materialize" a new Resultset-Object appropriately. With a built in InMemory-VFS (and its new "Dump-Interface") we could achieve many nice things in only one roundtrip. At the serverside we could attach an empty InMemory-DB (implicitely using the new MemVFS) to an already pooled SQLite-Connection and perform a bunch of "Insert Into-Queries", to create a small snapshot of e.g. a midsized "Master-Detail- Scenario". Now we could dump not only a "single-query-content" (as with our Resultsets currently), but could write a complete, related scenario (containing the prepared "InMemory-Tables") to a ByteArray and transfer *this* to the client. Now it would be great, if we could use the new MemVFS- interface, to create *and* initialize a new InMemory-DB at the clientside with the received ByteContent. This way, we could perform related queries (Joins, Filters, etc.) against the InMemory-DB (containing the midsize Master-Detail-Set) without doing any extra-roundtrips over the server. An already builtin InMemory-VFS would ease the burden of all wrapper-developers, to implement such kind of animal themselfes - and maybe such an implementation would help to cleanup (and speedup) the already contained InMemory-DB-Handling of the sqlite-engine too. What's your opinion on this feature-request? Best regards, Olaf Schmidt (developer of dhSQLite and dhRPCServer) -- View this message in context: http://www.nabble.com/VFS-in-upcoming-3.5.0-tf4364818.html#a12441170 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] VFS in upcoming 3.5.0
>> 1. As known, an InMemory-DB is currently not (much) faster >>than working against a File. >>With the new VFS I think, that much faster InMemory- >>DB-Handling should be possible, is that right? > I don't think so. The vfs is really an interface for moving > data (pages) in and out of the pager cache. Yep, so I understood them. > The way in-memory databases currently work, pages are > just kept in the page-cache all the time and never written > out to whatever system implements the vfs interface. Ah, ok. Somehow thought, that the InMemory-Handling was implemented, working against the b-trees, but then going *through* a "non-sized" pager-cache against "something behind it", wich was implemented using standard-fileio-apis, working against memory-mapped-files (so being system-dependent and not so performant like a "real selfwritten Filesystem"). >From my Insert-Tests (50,000 records on 6 mixed Columns): Filebased (sync=off, pagesize 4kB): ca. 0.7sec InMemory (pagesize 4kB): ca. 0.55sec (all done using direct bindings on the same Insert-statement) So it seems, that *not* seeing a huge performance-boost is more, because most of the "insert-time" is spent populating the B-tree, right? Anyway, an easy to use, already built in "Dump-Interface" for InMemory-DBs (able to be initialized with a given Byte- Content) would be nice. ;-) (Yes, I saw patches for this ask, but some time ago, not knowing if those work with the current pager. Simply thought, that this is a good time, to ask for an "officially supported" interface-enhancement regarding this functionality, mainly because of the new VFS and because I thought, you would use its capabilities in either way for InMemory-DBs). Regards, Olaf Schmidt -- View this message in context: http://www.nabble.com/VFS-in-upcoming-3.5.0-tf4364818.html#a12442415 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] VFS in upcoming 3.5.0
On Sat, 2007-09-01 at 08:15 -0700, Olaf Schmidt wrote: > Hi, > > first - congrats to the planned changes in the new, upcoming > version of SQLite. The new shared-cache behaviour sounds > promising and also the new VFS-option. > > A few questions to VFS. > 1. As known, an InMemory-DB is currently not (much) faster >than working against a File. >With the new VFS I think, that much faster InMemory- >DB-Handling should be possible, is that right? I don't think so. The vfs is really an interface for moving data (pages) in and out of the pager cache. The way in-memory databases currently work, pages are just kept in the page-cache all the time and never written out to whatever system implements the vfs interface. So there's not a lot you can do with the vfs interface to speed up in-memory databases. Years ago (sqlite v2) there was different data structure used for in-memory databases - "red-black balanced trees" or something instead of b-trees. But IIRC it wasn't all that much faster than the current approach - maybe 15-20% faster or thereabouts. And it didn't do nearly as good a job of using memory efficiently. Dan. > 2. If so, is it planned, to automatically instantiate an appropriate >(already built in) InMemory-VFS, if one sets the Filename-Param >to ':memory:' in an Open-Call, so that InMemory DBs work against >this implicite created MemVFS - meaning that the "Default-SQLite- >engine" already implements such an "InMemory-VFS" for us "Wrapper- >developers" (because you know best, how to do it in the fastest >possible way and because of my following "feature-request" below)? ;-) > > 3. If you plan something like this, it would be very nice, if >you could include (now that many new interfaces are coming in >either way) an additional API-enhancement, wich would allow, >to get the current "Byte-Content" of an InMemory-DB, wich >makes use of this new (built in) InMemory-VFS? > > The background for these questions is, that we use SQLite > behind an Appserver wich is currently able, to get Resultset- > Objects at the serverside (done over our wrapper) and after > retrieving such an Resultset, to serialize its "Query-Content" > into a ByteArray, wich is then transferred over sockets back > to the client. > > At the clientside we are able, to deserialize the Bytes > and "materialize" a new Resultset-Object appropriately. > > With a built in InMemory-VFS (and its new "Dump-Interface") > we could achieve many nice things in only one roundtrip. > At the serverside we could attach an empty InMemory-DB > (implicitely using the new MemVFS) to an already pooled > SQLite-Connection and perform a bunch of "Insert Into-Queries", > to create a small snapshot of e.g. a midsized "Master-Detail- > Scenario". > Now we could dump not only a "single-query-content" (as > with our Resultsets currently), but could write a complete, > related scenario (containing the prepared "InMemory-Tables") > to a ByteArray and transfer *this* to the client. > > Now it would be great, if we could use the new MemVFS- > interface, to create *and* initialize a new InMemory-DB at > the clientside with the received ByteContent. > This way, we could perform related queries (Joins, Filters, > etc.) against the InMemory-DB (containing the midsize > Master-Detail-Set) without doing any extra-roundtrips > over the server. > > An already builtin InMemory-VFS would ease the burden > of all wrapper-developers, to implement such kind of animal > themselfes - and maybe such an implementation would > help to cleanup (and speedup) the already contained > InMemory-DB-Handling of the sqlite-engine too. > > What's your opinion on this feature-request? > > Best regards, > > Olaf Schmidt > (developer of dhSQLite and dhRPCServer) > > - To unsubscribe, send email to [EMAIL PROTECTED] -
