Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > Yes. Makes sense (not to cache query results for embedded apps). > So what is cached. Just dirty pages? or are raw tables cached when > queried? SQLite implements a tables and indices as btrees over a pager layer. The pager layer caches pages. Various strategies are used to keep the page cache live as long as possible, including across transactions (if nobody modifies the database in between). Beyond that, most operating systems cache disk pages in memory. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
PostgreSQL has the capability of storing pre-compiled SQL so that it can be reused and have data bound to the compiled statement. I have not looked at the mechanics, but it would be of interest and educational for you to see the PostgreSQL approach. Sqlite does cache the results of a query. The persistence of that cache varies with the version of Sqlite. It has an optional shared cache mode which can lift performance in appropriate applications. The evolution of cache persistence and sharing in successive versions of Sqlite should give you an insight into the problems of implementing such features. Uma Krishnan wrote: Thanks John and Joe for your responses. As far as I know, Postgres does not have a virtual engine. I could be wrong. One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) Thanks Uma John Stanton <[EMAIL PROTECTED]> wrote: >Moreover, is it typical to have an implementation like VDBE in other databases as well? This is a common approach and has been used for a very long time. For example we used it in products produced during the 1980s because producing a virtual machine and a compiler for its application-specific instruction set was a far better solution than masses of procedural logic. At that time it was a time honored technique and not at all innovative. Look at how PostgreSQL compiles and stores SQL statements for background information on the concept. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried? Thanks Uma Scott Hess <[EMAIL PROTECTED]> wrote: On 10/17/07, Trevor Talbot wrote: > On 10/17/07, Uma Krishnan wrote: > > One other question, when a query is issued, does SQLite cache the results, > > so that future queries can be processed off the cache (I think not) > > Like the "query cache" in some other databases? No. > > SQLite does have a cache of database pages, but they mimic what's on > disk, not the results of a particular query. > > A query cache would not be very useful for an embedded database. If > you're caching results, you might as well do it in the application's > native form -- it's the same process after all. To add another nail, the reason a query cache is often useful in database servers is because you can usually share the cache across all the front-ends. Since SQLite effectively lives inside the front-end, this sharing goes away. Worse, any caching SQLite does is adding to the memory footprint of the containing app (or, put another way, stealing memory the app could use in other ways). -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
On 10/17/07, Trevor Talbot <[EMAIL PROTECTED]> wrote: > On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > > One other question, when a query is issued, does SQLite cache the results, > > so that future queries can be processed off the cache (I think not) > > Like the "query cache" in some other databases? No. > > SQLite does have a cache of database pages, but they mimic what's on > disk, not the results of a particular query. > > A query cache would not be very useful for an embedded database. If > you're caching results, you might as well do it in the application's > native form -- it's the same process after all. To add another nail, the reason a query cache is often useful in database servers is because you can usually share the cache across all the front-ends. Since SQLite effectively lives inside the front-end, this sharing goes away. Worse, any caching SQLite does is adding to the memory footprint of the containing app (or, put another way, stealing memory the app could use in other ways). -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > As far as I know, Postgres does not have a virtual engine. I could be wrong. It's not a virtual machine style, where it has a specific instruction set; instead it's more like a graph of operations. Execution means walking a graph instead of interpreting an instruction stream. It's still an abstract virtual engine, just implemented differently. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > One other question, when a query is issued, does SQLite cache the results, so > that future queries can be processed off the cache (I think not) Like the "query cache" in some other databases? No. SQLite does have a cache of database pages, but they mimic what's on disk, not the results of a particular query. A query cache would not be very useful for an embedded database. If you're caching results, you might as well do it in the application's native form -- it's the same process after all. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Regarding: "... when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) " ... = P.S. And I should certainly have mentioned the sqlite items below: http://sqlite.org/pragma.html PRAGMA cache_size= Number-of-pages; PRAGMA default_cache_size = Number-of-pages; PRAGMA page_size = bytes; - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Regarding: "... when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) " Hi Uma, In effect, powerful caching effects *do* occur because of the disk cache provided by modern operating systems. Since the hard disk operations are typically orders of magnitude longer than the sql engine's cpu work for a query, the fact that the disk sectors required by a recent query tend to hang around a bit makes for very efficient use of RAM memory -- probably much better for the system as a whole than if sqlite tried to reserve all this ram for itself. For some applications, it even makes sense to perform a command-line copy of the entire sqlite database to a NUL device, since this will pre-load the operating system cache. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Thanks John and Joe for your responses. As far as I know, Postgres does not have a virtual engine. I could be wrong. One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) Thanks Uma John Stanton <[EMAIL PROTECTED]> wrote: >Moreover, is it typical to have an implementation like VDBE in other databases as well? This is a common approach and has been used for a very long time. For example we used it in products produced during the 1980s because producing a virtual machine and a compiler for its application-specific instruction set was a far better solution than masses of procedural logic. At that time it was a time honored technique and not at all innovative. Look at how PostgreSQL compiles and stores SQL statements for background information on the concept. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Moreover, is it typical to have an implementation like VDBE in other databases as well? This is a common approach and has been used for a very long time. For example we used it in products produced during the 1980s because producing a virtual machine and a compiler for its application-specific instruction set was a far better solution than masses of procedural logic. At that time it was a time honored technique and not at all innovative. Look at how PostgreSQL compiles and stores SQL statements for background information on the concept. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
--- Uma Krishnan <[EMAIL PROTECTED]> wrote: > I'm a student trying to understand SQLite for my DB project. There are a > couple of aspects that > I don't quite understand: > 1) VDBE. I see how the vdbe stack is created using VDBEAddOp etc. But once > the code is > generated, I don't see when it's executed. Moreover, is it typical to have an > implementation > like VDBE in other databases as well? Search for sqlite3VdbeExec. It is called by sqlite3Step. The best way to understand the code is to step through sqlite3 example SQL statements with a debugger. Then follow up by reading the source files involved. I've heard that FoxPro used to JIT its queries in x86 prior to executing them, but I don't know if it's true. > 2) VIRTUAL TABLES. Why should the shared_cache be disabled when using > VIRTUAL TABLES? No idea. I've asked the same question myself. I would think it would be desirable to use FTS[123] with shared cache in a multi-threaded web server scenario. I suppose you could uncomment the code that prevents shared cache running with virtual tables, run it, and see what breaks. Need a vacation? Get great deals to amazing places on Yahoo! Travel. http://travel.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Hello, I'm a student trying to understand SQLite for my DB project. There are a couple of aspects that I don't quite understand: 1) VDBE. I see how the vdbe stack is created using VDBEAddOp etc. But once the code is generated, I don't see when it's executed. Moreover, is it typical to have an implementation like VDBE in other databases as well? 2) VIRTUAL TABLES. Why should the shared_cache be disabled when using VIRTUAL TABLES? Thanks Uma
