Re: [sqlite] Many many tables... performance wise?

On 16/10/2010 12:01 PM, Dustin Sallings wrote: > > ...but there will also be a unique index on rowid, which will get large > and need to be maintained. I'm concerned that this alone could be limiting > me somewhat. > > I have a similar application with a single table that I'd like

Re: [sqlite] Many many tables... performance wise?

This is basically where I am coming from, but deletion (of possibly millions of entries) is slow and ties up the system (see previous thread by someone some days back). In experimentation having dynamic tables and doing a drop table is proving a big winner, though so far only using about a

[sqlite] Problem with aggregate query

Hi all I found the following strange behavior. It's rather easy to stumble upon with: sqlite> create table a(id,name); sqlite> insert into a values (1,'name1'); sqlite> insert into a values (2,'name2'); sqlite> select * from a; 1|name1 2|name2 sqlite> select count(*), name from a; 2|name2

Re: [sqlite] Many many tables... performance wise?

On Oct 15, 2010, at 17:32, Scott Hess wrote: > Having a table with an owner_id, key, and value, with a unique index > on (owner_id, key) will probably be more efficient than having a > separate table per owner. Also, it will be easier to code safely, > because bind parameters don't work on

Re: [sqlite] Many many tables... performance wise?

Having a table with an owner_id, key, and value, with a unique index on (owner_id, key) will probably be more efficient than having a separate table per owner. Also, it will be easier to code safely, because bind parameters don't work on table names (I'm assuming you're using dynamic table names

Re: [sqlite] Many many tables... performance wise?

There are no schemas per se. Just key+blob. They are backup datasets. Nothing fancy databasey. Just wondering about the actual impact of having many tables. On 15/10/2010 6:54 PM, Simon Slavin wrote: > > On 15 Oct 2010, at 7:36am, Andrew Davison wrote: > >> What's the take on having hundreds of

Re: [sqlite] Comma-delimited field to rows (Once again)

On Sat, Oct 16, 2010 at 2:47 AM, Jim Morris wrote: > Not much help but this removes the multiplication: > SELECT B1.B + B2.B + B3.B + B4.B FROM > (SELECT 0 AS B UNION SELECT 1 AS B) AS B1, > (SELECT 0 AS B UNION SELECT 2 AS B) AS B2, > (SELECT 0 AS B UNION SELECT 4 AS B)

Re: [sqlite] Comma-delimited field to rows (Once again)

Not much help but this removes the multiplication: SELECT B1.B + B2.B + B3.B + B4.B FROM (SELECT 0 AS B UNION SELECT 1 AS B) AS B1, (SELECT 0 AS B UNION SELECT 2 AS B) AS B2, (SELECT 0 AS B UNION SELECT 4 AS B) AS B3, (SELECT 0 AS B UNION SELECT 8 AS B) AS B4 On 10/15/2010 3:00 PM, Max Vlasov

Re: [sqlite] error 14 : unable to open database file

Thanks man... I found I had forgot to fclose() one file after each iteration, so I'm assuming I was reaching some limit of max files opened by the same process (though it was the same file each time it still stacked up). Ill let it run for a while and see... Iterations were originally set to 10

[sqlite] Comma-delimited field to rows (Once again)

Hi, from time to time I try to solve well-known task of making rows from a comma-delimited list and the best I could do was this: - create a user-function returning zero-based Nth item from the list (let's call it GetItemFromSet) - make a complex query like this (this one allows up to 16 elements

Re: [sqlite] error 14 : unable to open database file

On 15 Oct 2010, at 8:11pm, Simon wrote: > The program is meant to loop forever, grab information from the > internet and store it in the database. The error happens after 12-24 > hours of execution. The database file is opened at the beginning of > execution and is never closed (it would if

Re: [sqlite] Duplicate results for a given primary key/row

On 15 Oct 2010, at 6:43pm, Jeff Flanigan wrote: > Cool, that definitely tells me the db is corrupt. Is there any way to recover > a corrupted db, or is it completely borked? http://www.sqlite.org/sqlite.html Use the command-line tool to dump the database as a text file (a long list of SQL

[sqlite] error 14 : unable to open database file

Hi guys, first time I see this, not much docs on the topic, or I couldn't find much on this one... The program is meant to loop forever, grab information from the internet and store it in the database. The error happens after 12-24 hours of execution. The database file is opened at the

Re: [sqlite] Duplicate results for a given primary key/row

This is most probably a corruption where index have some rowids not present in table. If nothing else is corrupted you can just drop the index and recreate it again. Pavel On Fri, Oct 15, 2010 at 1:43 PM, Jeff Flanigan wrote: > Cool, that definitely tells me the db is

Re: [sqlite] Duplicate results for a given primary key/row

Cool, that definitely tells me the db is corrupt. Is there any way to recover a corrupted db, or is it completely borked? - Original Message - From: "Simon Slavin" To: "General Discussion of SQLite Database" Sent: Friday, October 15, 2010

Re: [sqlite] Duplicate results for a given primary key/row

On 15 Oct 2010, at 6:11pm, Jeff Flanigan wrote: > My initial guess is this is due to some sort of database corruption, http://www.sqlite.org/pragma.html#pragma_integrity_check Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] GUI for data entry

Gabor, > If the purpose of this is teaching with R then the R package sqldf > lets you query all R data frames in your session using sql as if they > were one big giant database. No, nothing to do with teaching R, but still a useful point in terms of broadening the use of SQL with R. Thanks,

[sqlite] Duplicate results for a given primary key/row

I am receiving duplicate entries for a given 'row' in a select result. It looks like the select is finding multiple rows with different rowid (the built-in hidden column) for a given primary key. My initial guess is this is due to some sort of database corruption, but any insight would be

Re: [sqlite] GUI for data entry

On Fri, Oct 15, 2010 at 12:54 PM, Graham Smith wrote: > Tom, > > Thanks for this. > > My main reason for asking is because I am trying to encourage my > students and indeed clients to think "database" rather than > "spreadsheet". Most of the time these aren't big or complex

Re: [sqlite] Attach on disk database to memory database

Dan Kennedy-4 wrote: > > > On Oct 15, 2010, at 11:40 PM, Schoinya wrote: > >> >> Hello everybody >> >> I'm trying to attach on disk database to in memory database. >> >> But I get the strange error : SQLite error unrecognized token: ":" >> >> The following is the code: >> >>

Re: [sqlite] GUI for data entry

Tom, Thanks for this. My main reason for asking is because I am trying to encourage my students and indeed clients to think "database" rather than "spreadsheet". Most of the time these aren't big or complex data sets (normally records in the hundreds, sometimes the thousands) but still big

Re: [sqlite] Attach on disk database to memory database

On Oct 15, 2010, at 11:40 PM, Schoinya wrote: > > Hello everybody > > I'm trying to attach on disk database to in memory database. > > But I get the strange error : SQLite error unrecognized token: ":" > > The following is the code: > >SQLiteConnection connInMemory = new >

[sqlite] Attach on disk database to memory database

Hello everybody I'm trying to attach on disk database to in memory database. But I get the strange error : SQLite error unrecognized token: ":" The following is the code: SQLiteConnection connInMemory = new SQLiteConnection("Data Source=:memory:");

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

I also see where you can set the behavior using procnto -- I'll bet "procnto ~i" will make sqlite behave correctly. Though this is a global change. Anybody who depends on this zeroing though is nuts... http://www.qnx.com/developers/docs/6.4.0/neutrino/lib_ref/m/munmap_flags.html There are

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

The problem is the lack of the unmap call before the 2nd mmap. It's redundant on most systems but apparently needed to make QNX happy (including the flags we discussed before on both unmap and mmap). So we need ftruncate(fd, 32*1024); mmap(0, 32*1024, PROT_READ|PROT_WRITE, MAP_SHARED,

[sqlite] Re : restore function

thank you De : Igor Tandetnik À : sqlite-users@sqlite.org Envoyé le : Jeu 14 octobre 2010, 2h 28min 18s Objet : Re: [sqlite] restore function Roger MARTINEZ wrote: > restore function is not ok for me . > Before I

[sqlite] Re : restore function

thank you for answer Igor At once , i didn't knew if i could use restore function with sqlite corrupt database file .It seems that if i use corrupt file or empty file, restore function failed . With database file and table dropped it's ok Regards Roger

Re: [sqlite] GUI for data entry

Hi Graham, >> You don't need to create special tables for data entry. You can create views >> instead, coupled with "instead of" triggers. > >> I'll see if I can put together some SQL with a few examples of how a view >> updates several >related tables. > > As the OP, I would be very

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

On Oct 15, 2010, at 10:24 PM, Black, Michael (IS) wrote: > I'm not sure but I suspect sqlite is not calling unmap before > extending the area. > That would explain why it still gets zeroed out even with the flags. > > Put a break point in the unixShmUnmap call and see if it gets called >

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

I'm not sure but I suspect sqlite is not calling unmap before extending the area. That would explain why it still gets zeroed out even with the flags. Put a break point in the unixShmUnmap call and see if it gets called before mmap. May just need some QNX logic that says "if we're extending

Re: [sqlite] EXTERNAL:Re: How to optimize a multi-condition query

There are several conditions that should be met for walking the index to be faster than walking the table. 1) The most important one: index b-tree structure should be organized in such way that one can walk directly from one leaf to another thus traversing all leaves without touching interior

Re: [sqlite] TestFixture 3.7.2 - Some WAL tests fail on QNX OS

Hi Michael, Yes I added the "MAP_NOINIT" to mmap() and "UNMAP_INIT_OPTIONAL" flag to munmap_flags() call. Don't know where i might be going wrong in SQLite. As you suggested, I wrote a small application to check if this works. Fortunately it worked as desired (as given below). MAP_NOINIT When

Re: [sqlite] How to optimize a multi-condition query

Hilmar Berger wrote: > I used EXPLAIN QUERY PLAN on the query and it looks like it does not use > any index on b at all, only if I use hardcoded conditions like b > 0. > > It appears that the real problem is that SQlite does not use indices for > both tables For

Re: [sqlite] How to optimize a multi-condition query

Hilmar Berger wrote: > Indexes has been created for all fields in A and B (e.g. create index > name on A(chr, start, stop, strand)); > > The query is as follows: > > select * from > a, b > where a.chr = b.chr and a.strand = b.strand and a.start <= b.start

Re: [sqlite] How to optimize a multi-condition query

Black, Michael (IS) wrote: > I love simple examples like this can help people with understanding > things...so I tried this which I thought would do what Hilmar > wants...but alaswhat concept am I missing? > > SQLite version 3.7.2 > sqlite> create table c(achr

Re: [sqlite] Multiple prepared statements

Yup, my bad. Fixed. On 16/10/2010 12:03 AM, Andrew Davison wrote: > On 15/10/2010 11:49 PM, Pavel Ivanov wrote: >>> Now I decide that I want a second type of insert, so I try to use a >>> prepared statement for that as well. However it always fails. As long as >>> the other prepared statement is

Re: [sqlite] EXTERNAL:Re: How to optimize a multi-condition query

I see the difference now... So I take it that it's faster just to walk the table once rather than walk the index? Couldn't you just walk the index once? Smaller data space (quite likely), better caching? It might be a wash or worse with the potential of having to retreive other fields

Re: [sqlite] Multiple prepared statements

On 15/10/2010 11:49 PM, Pavel Ivanov wrote: >> Now I decide that I want a second type of insert, so I try to use a >> prepared statement for that as well. However it always fails. As long as >> the other prepared statement is hanging round I can't prepare a new one. >> Does this seem right or am I

Re: [sqlite] Multiple prepared statements

> Now I decide that I want a second type of insert, so I try to use a > prepared statement for that as well. However it always fails. As long as > the other prepared statement is hanging round I can't prepare a new one. > Does this seem right or am I really soing something wrong? You are doing

Re: [sqlite] Multiple prepared statements

On Fri, Oct 15, 2010 at 8:43 AM, Andrew Davison wrote: > In my database I do lots of inserts, of exactly the same nature so I use > a prepared statement, which I cache, always reseting after use. Works fine. > > Now I decide that I want a second type of insert, so I try

[sqlite] Multiple prepared statements

In my database I do lots of inserts, of exactly the same nature so I use a prepared statement, which I cache, always reseting after use. Works fine. Now I decide that I want a second type of insert, so I try to use a prepared statement for that as well. However it always fails. As long as the

Re: [sqlite] How to optimize a multi-condition query

> sqlite> create table c(achr char,bchr char); > sqlite> create index c_chr on c(achr,bchr); > sqlite> explain query plan select achr,bchr from c where achr=bchr; > 0|0|TABLE c > > Why no use of the index in this case? How do you think it should be used here? It's not that rows with the same

Re: [sqlite] How to optimize a multi-condition query

Hi, the condition in your query select achr,bchr from c where achr=bchr involves 2 columns of the table. In this case, an index is useless. If you do select achr,bchr from c where achr='foo' then the index will be used. Martin Am 15.10.2010 15:09, schrieb Black, Michael (IS): > Ok

Re: [sqlite] How to optimize a multi-condition query

Ok then... I added 67,600 records like this and still no index use. SQLite version 3.7.2 sqlite> select count(*) from c; 67600 sqlite> explain query plan select achr,bchr from c where achr=bchr; 0|0|TABLE c sqlite> create index c_chr on c(achr,bchr); sqlite> explain query plan select achr,bchr

Re: [sqlite] How to optimize a multi-condition query

On 15-10-10 14:34, Black, Michael (IS) wrote: > I love simple examples like this can help people with understanding > things...so I tried this which I thought would do what Hilmar wants...but > alaswhat concept am I missing? > > SQLite version 3.7.2 > sqlite> create table c(achr char,bchr

Re: [sqlite] How to optimize a multi-condition query

I love simple examples like this can help people with understanding things...so I tried this which I thought would do what Hilmar wants...but alaswhat concept am I missing? SQLite version 3.7.2 sqlite> create table c(achr char,bchr char); sqlite> create index c_chr on c(achr,bchr);

Re: [sqlite] How can I get the first 10 rows in sqlite db

On 14 October 2010 16:42, Kavita Raghunathan wrote: > Hello, > I’ve been adding and deleting rows from the sqlite database. Now the primary > ID is non-sequential. > > >  1.  How do I get the nth entry in the database >  2.  AND, How do I get the first n rows

Re: [sqlite] How to optimize a multi-condition query

On 15 October 2010 10:43, Hilmar Berger wrote: >  Thanks ! > > However, I tried what you suggested and there was no change. > > I used EXPLAIN QUERY PLAN on the query and it looks like it does not use > any index on b at all, only if I use hardcoded conditions like

[sqlite] How can I get the first 10 rows in sqlite db

Hello, I’ve been adding and deleting rows from the sqlite database. Now the primary ID is non-sequential. 1. How do I get the nth entry in the database 2. AND, How do I get the first n rows from the database ? Thanks, Kavita ___ sqlite-users

Re: [sqlite] How to optimize a multi-condition query

Thanks ! However, I tried what you suggested and there was no change. I used EXPLAIN QUERY PLAN on the query and it looks like it does not use any index on b at all, only if I use hardcoded conditions like b > 0. It appears that the real problem is that SQlite does not use indices for both

Re: [sqlite] Many many tables... performance wise?

On 15 Oct 2010, at 7:36am, Andrew Davison wrote: > What's the take on having hundreds of tables in a database? Generally not. A database should be designed. By a human. I don't know about you, but I can't hold hundreds of schema in my head at the same time. Rather than have two or more

Re: [sqlite] GUI for data entry

Tom > You don't need to create special tables for data entry. You can create views > instead, >coupled with "instead of" triggers. > I'll see if I can put together some SQL with a few examples of how a view > updates several >related tables. As the OP, I would be very interested in this. I

Re: [sqlite] GUI for data entry

On Wed, 13 Oct 2010 18:15:54 +0300 Mihai Militaru wrote: > But I got this idea: what if you create such "forms" yourself, using the > flexibility given by SQL? I don't know whether it would satisfy all your > requirements, but at least for duplication of data you can

[sqlite] Many many tables... performance wise?

What's the take on having hundreds of tables in a database? Any likely performance problems apart from first time a table is accessed? Does it affect the cache? Regards. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] gui for data entry

Mihal > But I got this idea: what if you create such "forms" yourself, using the > flexibility given by SQL? I don't know whether it would satisfy all > your requirements, but at least for duplication of data you can easily > use temporary "tool" tables with triggers, Thanks for this suggestion,