[sqlite] Speed comparison (was: [sqlite] Lock files....)
b.bum ([EMAIL PROTECTED]) wrote: > SQLite3 produces smaller data files thats true, > and is faster, in general, than did SQLite2. but this is not my experience. In my small applications (about 20 records) inserting and searching with SQLite 3.0 is slower than SQLite 2.8 (about 25%). Therefore I stay with 2.8. Hans-Jürgen
Re: [sqlite] A problem with the select statement
Yogesh Marwaha wrote: > Hi! > > I am using sqlite 3.05 beta. I have created a table containing a field named > 'title'. When I run the SQL Command : - > > select count(*) from master_table where title = "title" > > output is 885, i.e. count of all the rows in the table, whereas it should > have returned the number of rows in which value of the field title is "title" > (which is actually 10). Perhaps sqlite is comparing value of column named > title with the column itself. > A string in double-quotes resolves to the name of a column if such a column is available. Use single-quotes to force the string to be interpreted as a string. SELECT count(*) FROM master_table WHERE title='title'; ^ ^ | | ' not " here -^-' -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Index test on 3.0.7
Federico Granata wrote:
Hi, I'm testing index on sqlite ver 3.0.7.
I've create a really simple database
CREATE TABLE tbl1(rowid integer primary key,fld1 text,fld2 integer);
and fill it with 100 row like this
insert into tbl1(fld1,fld2) values("blablablablablablabla",759928);
Then I copy this test.db to test-index.db and add a index (on test-index.db)
CREATE INDEX ind1 on tbl1(fld2);
time sqlite3 test.db "select * from tbl1 where fld2>12345;" > /dev/null
real0m21.452s
user0m20.503s
sys 0m0.809s
and
time sqlite3 test-index.db "select * from tbl1 where fld2>12345;" > /dev/null
real1m15.840s
user0m54.799s
sys 0m16.131s
The fld2>12345 terms does not restrict the result very much - probably
most of your 100 records met this condition. So it is faster to
do a full table scan and discard the unwanted records (which is what the
first example does) rather than look up each record that has a value
of fld2>12345 using the index. A full table scan can do a linear search
through the database. An index lookup does a linear search through the
index, but for each index entry it has to do an O(NlogN) lookup of the
corresponding table entry.
A larger and more complex database engine might recognize this fact
and decide not to use the index in the second example. SQLite is more
simple minded. It always uses an index if one is available. You can
cause it to not use an index by changing the WHERE clause slightly,
to something like this perhaps:
SELECT * FROM tbl1 WHERE fld2-12345>0;
Note also that the query results are different. The first query above
outputs rows in ROWID order. The second query outputs rows in FLD2
order.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] A problem with the select statement
Hi! I am using sqlite 3.05 beta. I have created a table containing a field named 'title'. When I run the SQL Command : - select count(*) from master_table where title = "title" output is 885, i.e. count of all the rows in the table, whereas it should have returned the number of rows in which value of the field title is "title" (which is actually 10). Perhaps sqlite is comparing value of column named title with the column itself. Can somebody please help, Yogesh M Chandigarh, India http://mylinuxapps.tripod.com
[sqlite] Index test on 3.0.7
Hi, I'm testing index on sqlite ver 3.0.7.
I've create a really simple database
CREATE TABLE tbl1(rowid integer primary key,fld1 text,fld2 integer);
and fill it with 100 row like this
insert into tbl1(fld1,fld2) values("blablablablablablabla",759928);
Then I copy this test.db to test-index.db and add a index (on test-index.db)
CREATE INDEX ind1 on tbl1(fld2);
When I do something like
time sqlite3 test.db "select * from tbl1 where fld2=12345;" > /dev/null
real0m3.879s
user0m3.188s
sys 0m0.650s
and
time sqlite3 test-index.db "select * from tbl1 where fld2=12345;" > /dev/null
real0m0.019s
user0m0.006s
sys 0m0.006s
I'm happy... index work very well...
but...
time sqlite3 test.db "select * from tbl1 where fld2>12345;" > /dev/null
real0m21.452s
user0m20.503s
sys 0m0.809s
and
time sqlite3 test-index.db "select * from tbl1 where fld2>12345;" > /dev/null
real1m15.840s
user0m54.799s
sys 0m16.131s
make me very sad :(
It's a bug or mine mistake ?
P.S. Sorry for my english.
--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f
Sponsor:
Dai più energia al tuo sport! Rigenera il tuo corpo in maniera naturale
* Grazie agli integratori sport che trovi solo su Erboristeria.com
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=2379&d=25-9
Re: [sqlite] function of lemon and temp in Makefile?
Saturday, September 25, 2004, 10:42:26 AM, Daniel wrote: > While executing make install "./lemon" and "./temp" are exetuted. > Can anybody tell me, what they do exactly? ./lemon generates the C source code of the SQL parser from a grammar file You can get the generated parse.c and parse.h from the zip archives on the SQLite web site, which describes the archive... > This ZIP archive contains pure C source code for the SQLite library. > Unlike the tarballs below, all of the preprocessing and automatic > code generation has already been done on these C source code, so > they can be processed directly with any ordinary C compiler. This > file is provided as a service to MS-Windows users who lack the build > support infrastructure of Unix. ./temp generates config.h The file config.h simply tells SQLite the size of a pointer. On x86 WinXP this file has one line: #define SQLITE_PTR_SZ 4 So, you can just build config.h by hand a eliminate that target from the Makefile. e
[sqlite] function of lemon and temp in Makefile?
While executing make install "./lemon" and "./temp" are exetuted. Can anybody tell me,
what they do exactly?
Thanks,
Daniel
[EMAIL PROTECTED] build]# make install
sed -e s/--VERS--/`cat ./../VERSION`/ \
.
.
.
gcc -g -O2 -o temp temp.c
./temp >config.h
echo >>config.h
rm -f temp.c temp
echo '/* Automatically generated file. Do not edit */' >opcodes.h
grep '^case OP_' ./../src/vdbe.c | \
sed -e 's/://' | \
awk '{printf "#define %-30s %3d\n", $2, ++cnt}' >>opcodes.h
gcc -g -O2 -o lemon ./../tool/lemon.c
cp ./../tool/lempar.c .
cp ./../src/parse.y .
./lemon parse.y
./libtool --mode=compile gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./../src
-DNDEBUG -c ./../src/attach.c
.
.
.
_
Mit WEB.DE FreePhone? mit hochster Qualitat ab 0 Ct./Min.
weltweit telefonieren! http://freephone.web.de/?mc=021201
