Re: [sqlite] beat 120,000 inserts/sec
On Fri, Apr 08, 2005 at 11:01:02PM -0400, Al Danial wrote: [...] > What kind of insert performance do you see on your machine? If it > is substantially better than 120 kinserts/s what kind of hardware > do you have? I'm especially interested in how much faster the > code runs on systems with multiple disks in a RAID 0 configuration. > Are there other tricks to speeding insert performance? Is it even > reasonable to ask for more? -- Al ./sqlite_insert 10 5 10 inserts to /tmp/a.db in 0.671 s = 149057.52 inserts/s ./sqlite_insert 200 2 200 inserts to /tmp/a.db in 14.437 s = 138535.38 inserts/s ./sqlite_insert 200 5 200 inserts to /tmp/a.db in 15.322 s = 130530.52 inserts/s Not substantially better. My configuration: AMD Athlon(tm) 64 Processor 3000+ 1GB RAM 2 x Maxtor 200GB SATA150 7200RPM 8MB HD I'm running OpenBSD 3.6 with a customer kernel compiled to support OpenBSD's software raid (RAIDFrame). I have the 2 drives arranged in a RAID 1 configuration. enjoy, -jeremy -- Jeremy Hinegardner [EMAIL PROTECTED]
Re: [sqlite] beat 120,000 inserts/sec
Al Danial wrote: The attached C program measures insert performance for populating a table with an integer and three random floating point values with user defined transaction size. Usage is: ./sqlite_insert All of these are on Gentoo, Athlon 64 3200+, running 64 bit. Writing to /tmp, which is ext3 (acl,user_xattr,usrquota) over RAID0 (two slave drives on different channels): $ ./sqlitetest 10 5 10 inserts to /tmp/a.db in 0.531 s = 188446.34 inserts/s $ ./sqlitetest 200 5 200 inserts to /tmp/a.db in 11.546 s = 173223.61 inserts/s I'm actually surprised to see this kind of performance. The drives are brand-new Maxtor 200GB parallel ATA. I imagine I could do better with usrquota off. reiserfs over RAID5 (hideous config with different types of drives, 3 master, 2 slave): $ ./sqlitetest 10 5 10 inserts to /home/foobar/a.db in 0.884 s = 113121.53 inserts/s $ ./sqlitetest 200 5 200 inserts to /home/foobar/a.db in 20.025 s = 99875.94 inserts/s xfs on the same array: $ ./sqlitetest 10 5 10 inserts to /data/stage/a.db in 0.777 s = 128684.23 inserts/s [EMAIL PROTECTED] tmp $ ./sqlitetest 200 5 200 inserts to /data/stage/a.db in 14.541 s = 137544.14 inserts/s Last but not least, reiserfs noatime over RAID1 (two drives, masters on different channels): $ ./sqlitetest 10 5 10 inserts to /usr/foobar/a.db in 0.700 s = 142824.48 inserts/s $ ./sqlitetest 200 5 200 inserts to /usr/foobar/a.db in 15.376 s = 130071.89 inserts/s For kicks, I tried tmpfs. It crashed (segfault in sqlite3_bind_int64 -- go figure). I'll submit a bug report if I can reproduce it sanely. So it looks like ext3 over RAID0 is good. FWIW, I can read 99.18 MB/sec off /dev/md3 (where /tmp lives). --Andy
Re: [sqlite] beat 120,000 inserts/sec
Al Danial wrote: > What kind of insert performance do you see on your machine? If it > is substantially better than 120 kinserts/s what kind of hardware > do you have? I'm especially interested in how much faster the > code runs on systems with multiple disks in a RAID 0 configuration. > Are there other tricks to speeding insert performance? Is it even > reasonable to ask for more? -- Al > sqlite 3.0.8 Fedora core 2, kernel 2.6.8-1.521 /proc/cpuinfo: model name : AMD Athlon(tm) 64 Processor 3200+ cpu MHz : 2043.192 cache size : 1024 KB On a Seagate 7200.7 200GB Parallel IDE drive with ext3: $ ./sqlite_insert 10 5 10 inserts to /tmp/a.db in 0.841 s = 118872.29 inserts/s $ ./sqlite_insert 10 2 10 inserts to /tmp/a.db in 0.848 s = 117858.23 inserts/s $ ./sqlite_insert 200 5 200 inserts to /tmp/a.db in 18.446 s = 108424.30 inserts/s Same box with RAID 0 configuration with 2 U160 SCSI Seagate 15000 RPM 18GB Cheetahs (2nd generation, purchased in 2001) with ext3: $ ./sqlite_insert 10 2 10 inserts to ./a.db in 1.177 s = 84977.14 inserts/s $ ./sqlite_insert 10 2 10 inserts to ./a.db in 1.146 s = 87230.96 inserts/s $ ./sqlite_insert 200 5 200 inserts to ./a.db in 20.287 s = 98584.18 inserts/s -- Dan Nuffer
Re: [sqlite] beat 120,000 inserts/sec
Al Danial wrote: A scientific application I work with has clumsy data retrieval options. I dumped the application's output--integer and floating point numbers--into an SQLite database and soon after began to enjoy the power of SQL to pull out interesting results. The main complaint for making the transfer to SQLite a permanent part of our solution is the time it takes to insert the numbers into a database. It takes about a minute to insert 24 million numbers into three tables. Most database people (including me) would be thrilled to see this kind of insert performance but my colleagues are asking if it can be sped up. Try setting PRAGMA synchronous=OFF and seeing how much improvement you get. If it's significant, you'll have to decide for yourself if you can live with the increased risk of corruption if there's a power failure during a batch load.
Re: [sqlite] beat 120,000 inserts/sec
Random thought: You might squeeze some more performance out by
trying a couple different filesystems. i.e. if you're using ext3,
try some different journaling options, or try ext2.
--- Al Danial <[EMAIL PROTECTED]> wrote:
> A scientific application I work with has clumsy data retrieval
> options. I dumped the application's output--integer and floating
> point numbers--into an SQLite database and soon after began to
> enjoy the power of SQL to pull out interesting results.
>
> The main complaint for making the transfer to SQLite a permanent
> part of our solution is the time it takes to insert the numbers
> into a database. It takes about a minute to insert 24 million
> numbers into three tables. Most database people (including me)
> would be thrilled to see this kind of insert performance but
> my colleagues are asking if it can be sped up.
>
> The attached C program measures insert performance for populating
> a table with an integer and three random floating point values with
> user defined transaction size. Usage is:
>
> ./sqlite_insert
>
> It writes to the hardcoded database file /tmp/a.db
> On my Dell Precision 360, Pentium4 3.0 GHz, 1 GB RAM, IDE disk
> drive, ext3, RHEL v3, Linux kernel 2.4.21, I peak out around 121,000
> inserts/second using a transaction size of 20,000:
> ./sqlite_insert 10 2
> 10 inserts to /tmp/a.db in 0.829 s = 120626.53 inserts/s
>
> Performance drops a bit when I increase the number of rows to
> two million (a typical size for my application):
>
> ./sqlite_insert 200 5
> 200 inserts to /tmp/a.db in 17.124 s = 116795.07 inserts/s
>
> What kind of insert performance do you see on your machine? If it
> is substantially better than 120 kinserts/s what kind of hardware
> do you have? I'm especially interested in how much faster the
> code runs on systems with multiple disks in a RAID 0 configuration.
> Are there other tricks to speeding insert performance? Is it even
> reasonable to ask for more? -- Al
> > /*
> [EMAIL PROTECTED]
>
> # sample build:
> gcc -o sqlite_insert sqlite_insert.c \
> -L/usr/local/sqlite-3.2.1/lib -lsqlite3
> -I/usr/local/sqlite-3.2.1/include
>
> # sample run:
> ./sqlite_insert 10 5
>
> */
>
>
> #include
> #include /* RAND_MAX */
> #include /* timeval, gettimeofday() */
>
>
> int sql_begin(sqlite3 *db) { /* {{{1 */
> char *errmsg;
> if (sqlite3_exec(db, "BEGIN TRANSACTION",
> NULL, NULL, ) != SQLITE_OK) {
> printf("couldn't begin transaction: %s\n", errmsg);
> return 0;
> } else {
> return 1;
> }
> } /* 1}}} */
> int sql_commit(sqlite3 *db) { /* {{{1 */
> char *errmsg;
> if (sqlite3_exec(db, "COMMIT TRANSACTION",
> NULL, NULL, ) != SQLITE_OK) {
> printf("couldn't commit transaction: %s\n", errmsg);
> return 0;
> } else {
> return 1;
> }
> } /* 1}}} */
> float elapsed(struct timeval start, struct timeval end) { /* {{{1 */
> return (float) (end.tv_sec - start.tv_sec ) +
>((float) (end.tv_usec - start.tv_usec)/100);
> } /* 1}}} */
>
>
> int main(int argc, char *argv[]) {
> const char *zLeftover;
> #define CMD_SIZE 1000
> char rm_command[CMD_SIZE],
> *errmsg, *dbfile = "/tmp/a.db";
>/* *dbfile = ":memory:" is faster, but not of interest */
> intrc, i, N, xact_size, n_this_xact = 0;
> double x, y, z;
> float delta_T;
> struct timeval start_time, end_time;
> sqlite3 *db;
> sqlite3_stmt *Stmt;
>
>
> if (argc < 3) {
> printf("\nUsage: %s\n\n", argv[0]);
> printf("\tInsert rows into a table of an SQLite database\n");
> printf("\tusing transaction sizes of .\n");
> printf("\tThe table has four columns of numeric data:\n;);
> printf("\t field_1 integer\n");
> printf("\t field_2 float\n");
> printf("\t field_3 float\n");
> printf("\t field_4 float\n");
> printf("\tThe integer field will have values 1.. while the\n");
> printf("\tdouble precision values are random on [-50.0, 50.0]\n");
> exit(0);
> }
> N = atoi(argv[1]);
> xact_size = atoi(argv[2]);
> snprintf(rm_command, CMD_SIZE-1, "rm -f %s", dbfile);
> system(rm_command); /* the database file must not exist before
> calling sqlite3_open() and trying to insert */
>
>
> gettimeofday(_time, 0);
>
[sqlite] beat 120,000 inserts/sec
A scientific application I work with has clumsy data retrieval
options. I dumped the application's output--integer and floating
point numbers--into an SQLite database and soon after began to
enjoy the power of SQL to pull out interesting results.
The main complaint for making the transfer to SQLite a permanent
part of our solution is the time it takes to insert the numbers
into a database. It takes about a minute to insert 24 million
numbers into three tables. Most database people (including me)
would be thrilled to see this kind of insert performance but
my colleagues are asking if it can be sped up.
The attached C program measures insert performance for populating
a table with an integer and three random floating point values with
user defined transaction size. Usage is:
./sqlite_insert
It writes to the hardcoded database file /tmp/a.db
On my Dell Precision 360, Pentium4 3.0 GHz, 1 GB RAM, IDE disk
drive, ext3, RHEL v3, Linux kernel 2.4.21, I peak out around 121,000
inserts/second using a transaction size of 20,000:
./sqlite_insert 10 2
10 inserts to /tmp/a.db in 0.829 s = 120626.53 inserts/s
Performance drops a bit when I increase the number of rows to
two million (a typical size for my application):
./sqlite_insert 200 5
200 inserts to /tmp/a.db in 17.124 s = 116795.07 inserts/s
What kind of insert performance do you see on your machine? If it
is substantially better than 120 kinserts/s what kind of hardware
do you have? I'm especially interested in how much faster the
code runs on systems with multiple disks in a RAID 0 configuration.
Are there other tricks to speeding insert performance? Is it even
reasonable to ask for more? -- Al
/*
[EMAIL PROTECTED]
# sample build:
gcc -o sqlite_insert sqlite_insert.c \
-L/usr/local/sqlite-3.2.1/lib -lsqlite3 -I/usr/local/sqlite-3.2.1/include
# sample run:
./sqlite_insert 10 5
*/
#include
#include /* RAND_MAX */
#include /* timeval, gettimeofday() */
int sql_begin(sqlite3 *db) { /* {{{1 */
char *errmsg;
if (sqlite3_exec(db, "BEGIN TRANSACTION",
NULL, NULL, ) != SQLITE_OK) {
printf("couldn't begin transaction: %s\n", errmsg);
return 0;
} else {
return 1;
}
} /* 1}}} */
int sql_commit(sqlite3 *db) { /* {{{1 */
char *errmsg;
if (sqlite3_exec(db, "COMMIT TRANSACTION",
NULL, NULL, ) != SQLITE_OK) {
printf("couldn't commit transaction: %s\n", errmsg);
return 0;
} else {
return 1;
}
} /* 1}}} */
float elapsed(struct timeval start, struct timeval end) { /* {{{1 */
return (float) (end.tv_sec - start.tv_sec ) +
((float) (end.tv_usec - start.tv_usec)/100);
} /* 1}}} */
int main(int argc, char *argv[]) {
const char *zLeftover;
#define CMD_SIZE 1000
char rm_command[CMD_SIZE],
*errmsg, *dbfile = "/tmp/a.db";
/* *dbfile = ":memory:" is faster, but not of interest */
intrc, i, N, xact_size, n_this_xact = 0;
double x, y, z;
float delta_T;
struct timeval start_time, end_time;
sqlite3 *db;
sqlite3_stmt *Stmt;
if (argc < 3) {
printf("\nUsage: %s\n\n", argv[0]);
printf("\tInsert rows into a table of an SQLite database\n");
printf("\tusing transaction sizes of .\n");
printf("\tThe table has four columns of numeric data:\n;);
printf("\t field_1 integer\n");
printf("\t field_2 float\n");
printf("\t field_3 float\n");
printf("\t field_4 float\n");
printf("\tThe integer field will have values 1.. while the\n");
printf("\tdouble precision values are random on [-50.0, 50.0]\n");
exit(0);
}
N = atoi(argv[1]);
xact_size = atoi(argv[2]);
snprintf(rm_command, CMD_SIZE-1, "rm -f %s", dbfile);
system(rm_command); /* the database file must not exist before
calling sqlite3_open() and trying to insert */
gettimeofday(_time, 0);
rc = sqlite3_open(dbfile, );
sql_begin(db);
rc = sqlite3_prepare(db, "create table table_name(field_1 integer primary key,"
"field_2 float, "
"field_3 float, "
"field_4 float)",
Re: [sqlite] create table question
> >
> > select_stmt = db.execute("SELECT * FROM people")
> > # use some, but not all of the rows in select_stmt
> > create_stmt = db.execute("CREATE TABLE other (a,b)") # error:
> > database table is locked
> >
> > Why does this happen?
> >
> > Anyway around this?
>
>
> You must finalize select_stmt before running again db.execute
> >>>
> >>>
> >>> Right. I have an instance where I would like to keep the
> >> select_stmt
> >>> _open_ (or not finalized) while I create a new table. Is
> >> this possible?
> >>
> >> While you are reding the DB, you can't update it, sqlite support many
> >> simultaneous readers but only one write; so you can't create
> >> a new table
> >> while your select statement is running.
but he has only one writer. A select is not a writer, the create statement is.
I couldn't get something like this to work either and ended up building a list
of updates in memory which I applied after the finalize of the select.
Re: [sqlite] create table question
On Apr 8, 2005, at 9:27 AM, Robert Simpson wrote:
Actually if you need to re-use the prepared statement, it's much more
efficient to simply call sqlite_reset() on it instead of finalize.
As long as the statement is not currently step()'ing and is in the
reset
state, the database will not be locked.
Example:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
-->db.reset(select_stmt)
create_stmt = db.execute("CREATE TABLE other (a,b)") # error:
--># step through the select_stmt again
Robert
Thank you for the responses.
I am confused why the following works:
###
db.do("create table people (name, bday)")
db.do("create table hey (a, b)")
db.do("insert into people values ('Bill', '5/5/1969')")
s = db.execute('select * from people')
db.do("insert into hey values (1,2)")
# insert works, but create table does not
s.finish # finalizes statement
###
db.do() just executes a statement and finalizes it right away.
-Charlie
-Original Message-
From: Paolo Vernazza [mailto:[EMAIL PROTECTED]
Sent: Friday, April 08, 2005 9:09 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] create table question
Charles Mills wrote:
I am trying to create a table after creating a select statement
which selects records from an existing table. Is this
possible? I
get a SQLITE_LOCKED / "database table is locked" error when I
attempt this.
In psuedo code the logic is as follows:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
create_stmt = db.execute("CREATE TABLE other (a,b)") # error:
database table is locked
Why does this happen?
Anyway around this?
You must finalize select_stmt before running again db.execute
Right. I have an instance where I would like to keep the
select_stmt
_open_ (or not finalized) while I create a new table. Is
this possible?
While you are reding the DB, you can't update it, sqlite support many
simultaneous readers but only one write; so you can't create
a new table
while your select statement is running.
I thin you must do somthing like this:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
sqltorun += "CREATE TABLE other (a,b);"
select_stmt.finalize()
create_stmt = db.execute(sqltorun)
Paolo
Re: [sqlite] malformed 2.1 db
> Unable to open database 1.db > now what? any suggestions? There's probably nothing you can do to fix the file. In the past, you could corrupt a database by vacuuming it in one process and then doing an insert in another process. This has been fixed in V3; I don't know if the bug even existed in V2. Most likely, you (or someone) did something to corrupt the file. See Section 6 of . Regards
RE: [sqlite] create table question
Actually if you need to re-use the prepared statement, it's much more
efficient to simply call sqlite_reset() on it instead of finalize.
As long as the statement is not currently step()'ing and is in the reset
state, the database will not be locked.
Example:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
-->db.reset(select_stmt)
create_stmt = db.execute("CREATE TABLE other (a,b)") # error:
--># step through the select_stmt again
Robert
> -Original Message-
> From: Paolo Vernazza [mailto:[EMAIL PROTECTED]
> Sent: Friday, April 08, 2005 9:09 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] create table question
>
> Charles Mills wrote:
>
> >>> I am trying to create a table after creating a select statement
> >>> which selects records from an existing table. Is this
> possible? I
> >>> get a SQLITE_LOCKED / "database table is locked" error when I
> >>> attempt this.
> >>>
> >>> In psuedo code the logic is as follows:
> >>>
> >>> select_stmt = db.execute("SELECT * FROM people")
> >>> # use some, but not all of the rows in select_stmt
> >>> create_stmt = db.execute("CREATE TABLE other (a,b)") # error:
> >>> database table is locked
> >>>
> >>> Why does this happen?
> >>>
> >>> Anyway around this?
> >>
> >>
> >> You must finalize select_stmt before running again db.execute
> >
> >
> > Right. I have an instance where I would like to keep the
> select_stmt
> > _open_ (or not finalized) while I create a new table. Is
> this possible?
>
> While you are reding the DB, you can't update it, sqlite support many
> simultaneous readers but only one write; so you can't create
> a new table
> while your select statement is running.
> I thin you must do somthing like this:
>
> select_stmt = db.execute("SELECT * FROM people")
> # use some, but not all of the rows in select_stmt
> sqltorun += "CREATE TABLE other (a,b);"
> select_stmt.finalize()
> create_stmt = db.execute(sqltorun)
>
> Paolo
>
Re: [sqlite] create table question
Charles Mills wrote:
I am trying to create a table after creating a select statement
which selects records from an existing table. Is this possible? I
get a SQLITE_LOCKED / "database table is locked" error when I
attempt this.
In psuedo code the logic is as follows:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
create_stmt = db.execute("CREATE TABLE other (a,b)") # error:
database table is locked
Why does this happen?
Anyway around this?
You must finalize select_stmt before running again db.execute
Right. I have an instance where I would like to keep the select_stmt
_open_ (or not finalized) while I create a new table. Is this possible?
While you are reding the DB, you can't update it, sqlite support many
simultaneous readers but only one write; so you can't create a new table
while your select statement is running.
I thin you must do somthing like this:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
sqltorun += "CREATE TABLE other (a,b);"
select_stmt.finalize()
create_stmt = db.execute(sqltorun)
Paolo
Re: [sqlite] create table question
On Apr 8, 2005, at 2:35 AM, Paolo Vernazza wrote:
Charles Mills wrote:
I am trying to create a table after creating a select statement which
selects records from an existing table. Is this possible? I get a
SQLITE_LOCKED / "database table is locked" error when I attempt this.
In psuedo code the logic is as follows:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
create_stmt = db.execute("CREATE TABLE other (a,b)") # error:
database table is locked
Why does this happen?
Anyway around this?
You must finalize select_stmt before running again db.execute
Right. I have an instance where I would like to keep the select_stmt
_open_ (or not finalized) while I create a new table. Is this
possible?
-Charlie
RE: [sqlite] Re: Index not used with "between"
Hi Cory, Regarding: Is there a reason [the sql command] BETWEEN can't use [an index]? Hmm... nobody has an answer? Or did my message get eaten by a spam filter :( I can't answer your question by providing you the reason, but it appears to be a fact that you will want to use separate comparisons and avoid BETWEEN if you wish an index to be used, see: http://www.mail-archive.com/sqlite-users@sqlite.org/msg05030.html Donald Griggs Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors.
[sqlite] malformed 2.1 db
Hi there, 1. i get the following error; c:\>sqlite.exe 1.db Unable to open database "1.db": database disk image is malformed so i can't execute pragma integrity_check. header: ** This file contains an SQLite 2.1 database ** 2. this is the corrupted file: 00: 2A 2A 20 54 68 69 73 20 ? 66 69 6C 65 20 63 6F 6E 10: 74 61 69 6E 73 20 61 6E ? 20 53 51 4C 69 74 65 20 20: 32 2E 31 20 64 61 74 61 ? 62 61 73 65 20 2A 2A 00 30: 28 75 E3 DA 00 00 00 00 ? 00 00 0C 00 00 00 04 00 40: 00 00 D0 07 00 00 02 00 ? 00 00 00 00 00 00 00 00 this is my normal db: 00: 2A 2A 20 54 68 69 73 20 ? 66 69 6C 65 20 63 6F 6E 10: 74 61 69 6E 73 20 61 6E ? 20 53 51 4C 69 74 65 20 20: 32 2E 31 20 64 61 74 61 ? 62 61 73 65 20 2A 2A 00 30: 28 75 E3 DA 00 00 00 00 ? 00 00 00 00 F6 03 00 00 40: 04 00 00 00 D0 07 00 00 ? 02 00 00 00 00 00 00 00 here after 0x34 it seemed to me that there is a 2 bytes shift left so i added 2 null bytes, but it didn't work. c:\>sqlite.exe 1.db Unable to open database 1.db now what? any suggestions? Mustafa TUFAN
Re: [sqlite] SELECT (...) GROUP BY not returns!
Adam Dziendziel <[EMAIL PROTECTED]> writes: > SELECT COUNT(*) FROM drzewo_towar WHERE lft > 13 AND rgt < 14 GROUP BY towar > ... > the engine doesn't return the value (the above query should return 0). COUNT(*) returns NULL if there are no records selected. Change the query like this to accomplish what you're looking for: SELECT COALESCE(COUNT(*), 0) FROM drzewo_towar WHERE lft > 13 AND rgt < 14 GROUP BY towar; Derrell
Re: [sqlite] Re: Index not used with "between"
> Hmm... nobody has an answer? Or did my message get eaten by a spam filter
> :(
>
> On Apr 4, 2005 8:02 PM, Cory Nelson <[EMAIL PROTECTED]> wrote:
>> Using the following:
>>
>> create table t_history(time real, ...);
>> create index i_time on t_history(time);
>> select count(*) from t_history where time between
>> julianday('2004-04-01','utc') and julianday('now');
>>
>> It seems the index is never hit. When using >= and <=, the index
>> works. Is there a reason between can't use it?
I don't know how sqlite does it, but most sql databases I know translate
'a between b and c' to '(b <= a and a <= c) or (c <= a and a <= b)'.
That means you would need a reverse index too and you would have to merge
the results. I don't know of a sql optimizer that can solve this, but
maybe somebody knows more.
Gerald
[sqlite] SELECT (...) GROUP BY not returns!
When I execute: SELECT COUNT(*) FROM drzewo_towar WHERE lft > 13 AND rgt < 14 GROUP BY towar on the table: CREATE TABLE drzewo_towar ( id INTEGER PRIMARY KEY, nazwa TINYTEXT, towar INTEGER, lft INTEGER, rgt INTEGER, dpt INTEGER ); INSERT INTO "drzewo_towar" VALUES(1, 'Wszystkie', NULL, 1, 16, 0); INSERT INTO "drzewo_towar" VALUES(2, 'Pusta', NULL, 2, 5, 1); INSERT INTO "drzewo_towar" VALUES(3, 'Z zawartoscia', NULL, 6, 15, 1); INSERT INTO "drzewo_towar" VALUES(4, 'Grupa 1', NULL, 7, 12, 2); INSERT INTO "drzewo_towar" VALUES(5, 'Grupa 2', NULL, 13, 14, 2); INSERT INTO "drzewo_towar" VALUES(8, NULL, 3, 8, 9, 3); INSERT INTO "drzewo_towar" VALUES(9, NULL, 5, 3, 4, 2); INSERT INTO "drzewo_towar" VALUES(10, NULL, 5, 10, 11, 3); the engine doesn't return the value (the above query should return 0). Why? -- Regards, Adam
[sqlite] Re: Index not used with "between"
Hmm... nobody has an answer? Or did my message get eaten by a spam filter :(
On Apr 4, 2005 8:02 PM, Cory Nelson <[EMAIL PROTECTED]> wrote:
> Using the following:
>
> create table t_history(time real, ...);
> create index i_time on t_history(time);
> select count(*) from t_history where time between
> julianday('2004-04-01','utc') and julianday('now');
>
> It seems the index is never hit. When using >= and <=, the index
> works. Is there a reason between can't use it?
>
> --
> Cory Nelson
> http://www.int64.org
>
--
Cory Nelson
http://www.int64.org
Re: Re: [sqlite] How do I efficiently copy tables to another db within dll (sqlite3+)
> Thanks much - one more question though... > > I'm attempting to copy data from an in memory database to > another db (file or otherwise). Is it possible to attach an > existing in-memory database to another existing db connection? > (attach database :MEMORY: would just seem to create another in > mem db). > > I'm trying to do this in hopes of being able to use the > destination dbs existing connection - otherwise i have to do a > lock/unlock close/open on the destination db - which i'd like to > avoid. I haven't used in memory databases. If it's loaded in memory I generally use data structures or STL objects. Anyway, I believe you can do this as long as all the in memory databases are owned by the same process. I don't believe an in memory database can be seen by any other process except the one that created it. Why don't you write some code and give it a try? That's the best way to be sure it works. __ Yahoo! Messenger Show us what our next emoticon should look like. Join the fun. http://www.advision.webevents.yahoo.com/emoticontest
[sqlite] Callback when content has changed
Is it possible for a C application to get a callback as soon as a database's content has changed? Best regards, Frank.
Re: [sqlite] create table question
Charles Mills wrote:
I am trying to create a table after creating a select statement which
selects records from an existing table. Is this possible? I get a
SQLITE_LOCKED / "database table is locked" error when I attempt this.
In psuedo code the logic is as follows:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
create_stmt = db.execute("CREATE TABLE other (a,b)") # error: database
table is locked
Why does this happen?
Anyway around this?
You must finalize select_stmt before running again db.execute
Paolo
