Re: [sqlite] Join performance in SQLite

On Sat, May 30, 2009 at 07:01:31PM +0100, Simon Slavin wrote: > I'm interested in how sqlite works differently to the SQL systems > which keep a daemon running as a background task. One of the > advantages of having a daemon which persists between runs of an > application is that the daemon

Re: [sqlite] Feature request

I read through the header after Simon's request, and saw the data change counter he mentions. What I wasn't sure about is whether this number gets reset during a rollback. For example, you start a transaction, make some changes, some cache pages get flushed, and SQLite changes this number so

Re: [sqlite] Join performance in SQLite

SQLite has surprised me with its quick performance, not the other way around. In fact, I've implemented all kinds of lookup queries that I knew could be optimized by caching results so I didn't have to keep repeating the SQL query, but the performance was so good even repeating the queries that I

Re: [sqlite] Deleting database content : SQLite3 API

On Sat, May 30, 2009 at 1:20 PM, wrote: > Yes , I understand that. Infact I was doing that through a script during > system startup. I wanted to know whether SQLite provides any API to do the > same. No, and it doesn't provide any API for changing access permissions

Re: [sqlite] Feature request

On 23 May 2009, at 3:32pm, Filip Navara wrote: > PRAGMA schema_version ... for the second case. Okay. Given that this does exactly what I want from one of my requests, and given Jim Wilcoxson's point that adding a PRAGMA is better than adding a function to the library, I can simplify my

Re: [sqlite] Join performance in SQLite

I'm interested in how sqlite works differently to the SQL systems which keep a daemon running as a background task. One of the advantages of having a daemon which persists between runs of an application is that the daemon can keep its own list of ORDERs, and JOINs which are asked for

Re: [sqlite] Join performance in SQLite

D. Richard Hipp wrote: > There has been a recent flurry of comments about SQLite at > > http://www.reddit.com/r/programming/comments/8oed5/how_sqlite_is_tested/ > http://news.ycombinator.com/item?id=633151 > > One of the criticisms of SQLite is that it is slow to do joins. That > is

Re: [sqlite] Join performance in SQLite

Assuming memory is sufficiently inexpensive, I would think that it would almost always be useful to build an index for any field in a join rather than doing a full scan. (Or better yet, build a hash table if memory is sufficient.) Indices maintained in the database then become

Re: [sqlite] Deleting database content : SQLite3 API

What would be the point of a function which just performs a syste call like unlink? Bloat? souvik.da...@wipro.com wrote: > Yes , I understand that. Infact I was doing that through a script during > system startup. I wanted to know whether SQLite provides any API to do the > same. > > Thanks

Re: [sqlite] Join performance in SQLite

> Do other SQL database engines not have this same limitation? Are > MySQL and PostgreSQL and Firebird and MS-SQL and Oracle creating > phantom indices on-the-fly to help them do joins faster, for example? Sort of. There's 2 types of join methods in Oracle for this - Hash joins and Sort merge

Re: [sqlite] GROUPY BY alias backward incompatibility

> * The query that works on SQLite all versions fails on Oracle. False conclusion. Did you try to make only one row in t? > * Behaviour is inconsistent between MySQL and Oracle. I believe this conclusion is also false. Did you try several rows in t on MySQL? If it worked I wonder how it

[sqlite] Join performance in SQLite

There has been a recent flurry of comments about SQLite at http://www.reddit.com/r/programming/comments/8oed5/how_sqlite_is_tested/ http://news.ycombinator.com/item?id=633151 One of the criticisms of SQLite is that it is slow to do joins. That is true if SQLite is unable to figure

Re: [sqlite] Deleting database content : SQLite3 API

On Sat, 30 May 2009 17:50:36 +0530, wrote: > >Yes , I understand that. Infact I was doing that >through a script during system startup. I wanted >to know whether SQLite provides any API to do the same. No, it doesn't. You could write it yourself: foreach $name in \

Re: [sqlite] Deleting database content : SQLite3 API

On 30/05/2009 10:20 PM, souvik.da...@wipro.com wrote: [top-posting unscrambled] [first message] >> As a result , after finding that the >> database already exits at the system startup, I cannot just drop the >> tables. ( As the table which are present in the existing data base is >> not

Re: [sqlite] Deleting database content : SQLite3 API

Yes , I understand that. Infact I was doing that through a script during system startup. I wanted to know whether SQLite provides any API to do the same. Thanks and Regards, Souvik -Original Message- From: sqlite-users-boun...@sqlite.org on behalf of John Stanton Sent: Sat 5/30/2009

Re: [sqlite] Deleting database content : SQLite3 API

An Sqlite database is just a file. Delete the file when you start your program and when you open the database Sqlite will create a fresh dne, a very low overhead process.. . souvik.da...@wipro.com wrote: > Hello, > > Please let me know if we have an API in SQLite3 which allows me to >

[sqlite] Deleting database content : SQLite3 API

Hello, Please let me know if we have an API in SQLite3 which allows me to retain the database but delete it's content at runtime. The problem I am facing is that : Every time I restart my system , I need to create the database. If the database exits already it's contents need to be deleted. The

Re: [sqlite] GROUPY BY alias backward incompatibility

At 15:37 28.05.2009, D. Richard Hipp wrote: >Have you tried these two queries on other SQL database engines besides >SQLite? What do PostgreSQL and MySQL make of them? I could now run the queries on Oracle Database 10g Express Edition Release 10.2.0.1.0. Prepare the table: create table t

Re: [sqlite] Is this test failure serious: *malloc*.transient.42 failures (expected [1 1], got [0 {}])?

On Thu, May 28, 2009 at 04:38:50PM -0400, D. Richard Hipp wrote: > >> To debug, set a breakpoint on sqlite3Fault(). That routine is > >> called whenever an OOM fault is simulated. Run to the point of the > >> OOM fault that is causing the problem. Figure out which malloc() > >> is falling and

Re: [sqlite] Corruption of incremental_vacuum databases

On Sat, May 30, 2009 at 7:43 AM, John Machin wrote: > On 17/04/2009 1:39 AM, Filip Navara wrote: >> Hello, >> >> I have expected at least some reply. Oh well, new the corruption has happened >> again (on another different machine) and I have saved the database files. One >>