Re: [sqlite] Version 3.2.3

[Khamis Abuelkomboz]

Khamis Abuelkomboz wrote:


D. Richard Hipp wrote:


Version 3.2.3 of SQLite is now available on the website
http://www.sqlite.org/

In addition to fixing a lot of minor bugs, this release
adds a number of important new enhancements.  Most of
the enhancements are centered around the much-improved
query optimizer, but there are some unrelated additions.

 * CAST operators are now supported.
 * The order of tables in a join is adjusted automatically
   as needed to make better use of indices.
 * Some OR-connected terms in the WHERE clause can now
   make use of indices.
 * BETWEEN operators in the WHERE clause are able to use
   indices.
 * A new pragma "case_sensitive_like" turns case sensitivity
   for the LIKE operator on and off.
 * The GLOB operator can use indices if its right-hand side
   consists of text followed by a wildcard.  The LIKE operator
   does this too if case sensitivity is turned on.
 * There is a new ANALYZE command that gather statistics on
   indices and helps the optimizer make a better choice when
   it needs to choose between two or more indices.

As always, please let me know if you find any problems.
 


Hi D. Richard

thank you for the great work.

I used to use 3.0.8 and upgraded to this current release. It seems 
that with this release some sql statements get broken. the broken sql 
statements (selects) simply don't return any value, where they should. 
I would make an example, if I find a reproducable case.


khamis

this seems to have to do with ORDER BY clause. I get results when the 
clause is omitted.


However I got a case where I omitted the ORDER BY clause and the 
database went to use memory until I killed the process by virtual size 
of 2GB.


khamis

--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

Re: [sqlite] Version 3.2.3

[Khamis Abuelkomboz]

D. Richard Hipp wrote:


Version 3.2.3 of SQLite is now available on the website
http://www.sqlite.org/

In addition to fixing a lot of minor bugs, this release
adds a number of important new enhancements.  Most of
the enhancements are centered around the much-improved
query optimizer, but there are some unrelated additions.

 * CAST operators are now supported.
 * The order of tables in a join is adjusted automatically
   as needed to make better use of indices.
 * Some OR-connected terms in the WHERE clause can now
   make use of indices.
 * BETWEEN operators in the WHERE clause are able to use
   indices.
 * A new pragma "case_sensitive_like" turns case sensitivity
   for the LIKE operator on and off.
 * The GLOB operator can use indices if its right-hand side
   consists of text followed by a wildcard.  The LIKE operator
   does this too if case sensitivity is turned on.
 * There is a new ANALYZE command that gather statistics on
   indices and helps the optimizer make a better choice when
   it needs to choose between two or more indices.

As always, please let me know if you find any problems.
 


Hi D. Richard

thank you for the great work.

I used to use 3.0.8 and upgraded to this current release. It seems that 
with this release some sql statements get broken. the broken sql 
statements (selects) simply don't return any value, where they should. I 
would make an example, if I find a reproducable case.


khamis

--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

Re: [sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram

[Khamis Abuelkomboz]

D. Richard Hipp wrote:




I am running on linux, and I will need this to scale to at least
200,000 rows 
   



If you upgrade to the vary latest code in CVS (version 3.2.2+)
and you create an index like this:

  CREATE INDEX idx ON table(parent_name, name);

Then the query above should be very efficient.

You can try it creating the index above with version 2.x.  It might
work.  I do not remember how smart the optimizer was about using
indexes to optimize sorting in version 2.x (that was so long ago.)

If you really need to use 2.8, you could just create the index
above, make sure that it is the *only* index on the table, then
omit the ORDER BY clause all together.  When the index above is
used, things will come out sorted or by name just because of
the way they work in SQLite.  SQL does not guarantee that behavior
so it probably will not work that way on other database engines,
but it should work fine in SQLite.
 

I'm actually experiencing similar problem with sqlite3.0, when tables 
are empty, select and modify operations are very fast, but when the 
table gets more data the speed actually breaks suddenly down. My 
experience, it doesn't matter, witch page size I use. Latest I tried 
with page number size of 32000 and page size of 8192, but got the same 
results. Tempspace is set to memory.


To clarify what I mean, in my application the parsers scan files and 
produce cross-reference entries using inserts in two tables. In a 
project with about 3000 files, the first 500 files are done in about 
15sec. the latest 500 files take about 90sec.


Is this issue fixed in 3.3.2+ too? Does anyone have any idea, what could 
be wrong here?


thanks
khamis

--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

Re: [sqlite] Strange performance problem

[Khamis Abuelkomboz]

Hi Michael

I tried your example and have experienced the same results. Then I 
created the following two indices, now the two queries are same fast:


CN_execute "CREATE INDEX idx_t1_id ON t1 (id)"
CN_execute "CREATE INDEX idx_t2_id ON t2 (id)"

It seems that sqlite doesn't create an index for primary keys that are 
not "INTEGER" :-) But haven't verified it yet.


khamis

Michael Gross wrote:


Khamis Abuelkomboz wrote:


CREATE INDEX idx_t2_t1id ON t2 (t1id, deleted);


Doent not help.


whereas the following quere takes "no" time:
  SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE 
t1.deleted<>1



This is fast, because no entries has been found to be joined.


This is not true - "deleted" hast the value 0 in all rows - so
"t1.deleted=0" and "t1.deleted<>1" brings the same result.


And now - check this out - the following query ist fast too:

SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE NOT NOT
(t1.deleted=0)

Whereas

SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE (t1.deleted=0)

is slow. And as far as I undestand this should be the same, or do I miss
sth?

thx
  Michael





--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

Re: [sqlite] Strange performance problem

[Khamis Abuelkomboz]

Michael Gross wrote:


Hello
I use sqlite 3.2.2. I have a strange performance problem. I am able to 
solve the problem by a slight change in the query but I want to ask if 
somebody can explain this behavior to me:


I have two tables:
  CREATE TABLE t1 (id VARCHAR(40) NOT NULL PRIMARY KEY, deleted BIT);
  CREATE TABLE t2 (id VARCHAR(40) NOT NULL PRIMARY KEY, t1id 
VARCHAR(40), deleted BIT);

  CREATE INDEX idx_t1_deleted ON t1 (deleted);
  CREATE INDEX idx_t2_deleted ON t2 (deleted);
  CREATE INDEX idx_t2_t1id ON t2 (t1id);


try the following index

CREATE INDEX idx_t2_t1id ON t2 (t1id, deleted);


When I now insert 2500 times (%d is the incrementing variable):
  INSERT INTO t1 (id, deleted) VALUES ('%d', 0);
  INSERT INTO t2 (id, t1id, deleted) VALUES ('%d', '%d', 0);
  INSERT INTO t2 (id, t1id, deleted) VALUES ('xxx%d', '%d', 0);

The the following query takes about 1 minute:
  SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE t1.deleted=0


This is slow, because the join is not indexed


whereas the following quere takes "no" time:
  SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE t1.deleted<>1


This is fast, because no entries has been found to be joined.

Keep in mind, that only one index can be used for a query or subquery.



thx
  Michael





--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

Re: [sqlite] Index and General Optimization Question

[Khamis Abuelkomboz]

John Sample wrote:


Hello all.
I'm a new Sqlite user porting a large db for an open source project
from SQL Server.

I have a question regarding whether an index makes sense or will be
used for a certain scenario. Since the table in question is so large
it takes up to 8 hours for me to create new indexes, so I'd like to
see if I can get a best guess before trying.

A little background info:
This is for a GIS system. The Sqlite database is 2+ gigs at the moment.
The table in question has 17,000,000 rows and is performing well so far.
It has a multicolumn index on 4 columns which are always part of the queries.

NAME TYPE DIRP DIRS

There are 2 more columns that make up the rest of the select clause,
but it is an OR statement. A pseudo select clause would look like
this:

NAME='char' and TYPE='char' DIRP='char' DIRS='char' and (ZIPL =int OR ZIPR=int)

Now to the question:
Can/will a multicolumn index (ZIPL,ZIPR) be used for an OR clause?
If so, what if it was part of the other multicolumn index?

Thanks for any guidance!

 

A new index only on the fields ZIPL or ZIPR would propably make your 
queries even slower, because they are not part of the rest of fields.
Your query could be faster, if there are two many SIMILAR values for the 
tuppel (NAME,TYPE,DIRP,DIRS), in this case I would add the following two 
indices:


index1: NAME,TYPE,DIRP,DIRS,ZIPL
index2: NAME,TYPE,DIRP,DIRS,ZIPR

khamis

--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

Re: [sqlite] Index and General Optimization Question

[Khamis Abuelkomboz]

Kurt Welgehausen wrote:


Currently, indices are not used to look up either term
in an OR expression.

See .

You could build 2 separate 5-column indices and use a
union.

Regards

his is not true. databases split usually queries into several sub 
queres, if you use an OR clause in the middle of indexed fields. I don't 
know, if sqlite is doing somelike things.


As example, imagine you have two fields in an index (field1, field2)

select field1, field2 where field1 = 'value' and (field2 = 'value1' or 
field2 = 'value2')


the database will start two subqueris and make a unique union of both 
results:


select field1, field2 where field1 = 'value' and field2 = 'value1'
select field1, field2 where field1 = 'value' and field2 = 'value2'


--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

Re: [sqlite] query 31 days

[Khamis Abuelkomboz]

Khamis Abuelkomboz wrote:


Hi

How many records contain your table? Your select command scans all the 
table entries and I guess that the database uses the standard index. 
try the following command to tell the db which index it should use:


select distinct date from mytable where yearmonth = '200508' order by 
date



Sorry I meen

select distinct date from mytable where yearmonth = '200508' order by 
yearmonth , date

Re: [sqlite] query 31 days

[Khamis Abuelkomboz]

Hi

How many records contain your table? Your select command scans all the 
table entries and I guess that the database uses the standard index. try 
the following command to tell the db which index it should use:


select distinct date from mytable where yearmonth = '200508' order by date

Another possibility for this slowness is may be your database memory and 
temp store usage configuration. the standard memory configuration of 
release 2.8.16 is very slow, I had some performance problems until I had 
changed the memory usage configuration. I use the following configuration:


PRAGMA cache_size = 6000
PRAGMA default_synchronous = OFF
PRAGMA synchronous = OFF
PRAGMA default_temp_store = MEMORY
PRAGMA temp_store = MEMORY
PRAGMA show_datatypes = OFF


good luk
khamis

Tom Deblauwe wrote:


Hello,

I'm using SQLite 2.8.16, on an embedded system, and I have a question.
I have a database with one table, containing all my records.  Each has a
field 'yearmonth' which contains for example '200508', and also a field
'date', which indicates the day of that month.  I want now to make an
overview of a month and see which days contain a record.  I've created a
combined index on 'yearmonth' and 'date'.  My query to do this is:
'select distinct date from mytable where yearmonth = 200508;'  This
takes about 50 seconds, which is really slow to show my users(it is
displayed on a webpage).

I noticed that this takes a long time, because I guess almost all
records are checked.  My other idea was to do 31 queries(one for every
day), like this: 'select ID from mytable where yearmonth = 200508 and
date = 1 limit 1;'.  This second method does not give me anymore speed
improvement.

for example:

 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | ...
 ^   ^   ^   ^

the days marked with '^' contain records.  To check with the second
method for example day 7, it takes 2.6 seconds.  Day 4 takes less, and
from day 3 and lower, it is reacting immediately(like 40 ms).  Day 8 and
higher takes about 1 second each.

When I do a random query om my database with select, and want to return
10 results for example, it takes 2.8 seconds.  So getting 1 result, or
getting 10 results only differs about 0.2 seconds.  Is there anything I
can do so things go faster?  Maybe I should maintain a separate table
with the monthoverview, but if it is possible to do some other solution
it would be nicer ;-).

Thanks for your time,
kind regards,
Tom,








--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

re: [sqlite] Query locking up SQLite

[Khamis Abuelkomboz]

David Fowler wrote:

Query 2:
SELECT * FROM table1, table2
WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id)


> OR (table1.value LIKE "%different_value%" AND table1.table2_id = 
table2.id)); This query (and even more complex versions of it) works in 
MySQL (Haven't tried another DB yet) and I'm trying to migrate to > 
SQLite, this is really holding me back.



Hi David

Have you ever tried your statement as following:

SELECT * FROM table1, table2
WHERE table1.table2_id = table2.id
 and (table1.value LIKE "%value%" or table1.value LIKE "%different_value%");


khamis


--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

[sqlite] how to get last autoincrement value?

[Khamis Abuelkomboz]

Hi

I'm using SQLite in my application and am very excited about this little 
and fast database.


How can I retrieve the last created autoincrement value?
On MySQL and SQLServer or just the SQL standard I can fire the following 
select statement:

SELECT @@IDENTITY

Is there a similar way to do so in SQLite?

Thanks
Khamis

--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol