Re: [sqlite] Slow sqlite3_open() - possible culprits?
If you're attempting to open a read only SQLite database, it seems as though it will take that time to validate write permissions. I don't know what CEROD is. In that thread, to which I was a part of, you'll notice that when I changed just the basic file attribute to read only, even the CLI paused for the 2 second period. I've NEVER looked at, which inherently means I've never traced into, SQLites code to validate what I've found. But with my Delphi SQLite wrapper, and the SQLite CLI, I get the same delayed results. On Thu, Mar 14, 2013 at 12:34 AM, Mohit Sindhwani m...@onghu.com wrote: Hi, we are using SQLite3 + CEROD for a number of databases in an embedded systems application running on Windows CE. We're finding unexpectedly long time to open the database (0.5s ~ 2.8sec). Maybe, these times are reasonable, but they seem long to us. We are using: sqlite3_open_v2(sFilePath, db, SQLITE_OPEN_READONLY, NULL); What are the kinds of things that would slow down opening the database? Database size? Database schema? CEROD? If someone in the know can throw some light on this, we can try to see how to mitigate the timing. I know there was a recent thread on sqlite3_open taking 1.5s under IIS and that was a permissions issue. That is not the case for us (since we are using a read-only CEROD database) Thanks Best Regards, Mohit. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow sqlite3_open() - possible culprits?
Hi Stephen, Thanks for the reply! On 14/3/2013 2:20 PM, Stephen Chrzanowski wrote: If you're attempting to open a read only SQLite database, it seems as though it will take that time to validate write permissions. By read only, I meant that the database is for reading only. It doesn't have a read only attribute set on the file system. Further, the open API is called with READ_ONLY specified as a parameter. I'll double check the file attributes on the disk. I don't know what CEROD is. CEROD is an SQLite extension from HWACI for Compressed Encrypted Read-Only Database - it encrypts and compresses the database after which it is much smaller and safe from prying eyes, but can only be read from, not written to. Our database is a database of reference information, so that works very well for our application. In that thread, to which I was a part of, you'll notice that when I changed just the basic file attribute to read only, even the CLI paused for the 2 second period. I've NEVER looked at, which inherently means I've never traced into, SQLites code to validate what I've found. But with my Delphi SQLite wrapper, and the SQLite CLI, I get the same delayed results. Thanks for the details. I'll double check the file attributes once to be sure, but since we're opening it with READ_ONLY flag specified in the API, I would expect that the problem may be elsewhere. Best Regards, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL Test failures on ARM
Appreciate if someone can point out what’s going wrong with these tests -- View this message in context: http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67707.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL Test failures on ARM
On 03/13/2013 01:03 PM, bkk wrote:
Hi,
Below is the code sequence after making a breakpoint at robust_ftruncate()
i could see that robust_ftruncate and ts_ftruncate is called couple of
times before the error is given to the user
Breakpoint 2, ts_ftruncate (fd=5, n=297136) at ./src/test_syscall.c:273
273 if( tsIsFailErrno(ftruncate) ){
(gdb) break robust_ftruncate
Breakpoint 3 at 0x66b14: file sqlite3.c, line 23589.
(gdb) continue
Continuing.
Breakpoint 3, robust_ftruncate (h=6, sz=0) at sqlite3.c:23589
23589 do{ rc = osFtruncate(h,sz); }while( rc0 errno==EINTR );
The test was supposed to inject an error into osFtruncate() so
that the first time it is called it returns a non-zero value
and sets errno to EINTR. Then the second time to return zero
(success). What is actually happening?
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL Test failures on ARM
Does it have anything to do with platform ? i am not sure though , because i am new to ARM and sqlite. Thanks Brijesh -- View this message in context: http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67709.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] problem with journal_mode memory to wal
Hello, I'm using windows 7 (64 bits) with sqlite 3.7.15.2 and I can't change journal_mode to WAL, it always return memory mode. what I can do? Many thanks, Vitor. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] set journal off working witth SQLiteConfig?
Hello,
I am using the sqlite-jdbc-3.7.8-20111025.014814-1.jar , trying to set the
journaling off on my 'main' only opened db.
SQLiteConfig config = new SQLiteConfig();
config.setJournalMode(SQLiteConfig.JournalMode.OFF);
writeLog(Timestamp properties);
Properties propSqlite = config.toProperties();
When I run, I see from my properties display that the journaling is off, but
I still see a journal file being created when I run it.
{open_mode=6, journal_mode=OFF)
Does anyone have experience with turning this off thru the SqlLiteConfig
object? Any tips on usage?
The reason I'm tring this is that we keep running out of memory during the
processing of a large transaction (about 52,000 updates). I try changing cache
size and ournal size limits, but I'm not sure if it's really taking affect.
If anyone has some tips on settings for large transactions like this, please
share!
Thanks,
Doug
Doug Crites
Sr Software Engineer
doug.cri...@asg.commailto:doug.cri...@asg.com
The Commons
708 Goodlette Road N
Naples, FL 34102
Tel: 239.435.2293
Fax: 239.213.3501
Toll-Free: 800.932.5536 USA Only
www.asg.comhttp://www.asg.com/
ASG-CloudFactory - The Single Solution to Build, Deploy Manage your Cloud
Environments
Click here for more information on the
ASG-CloudFactoryhttp://www.asg.com/cloudfactory
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow sqlite3_open() - possible culprits?
On Thu, Mar 14, 2013 at 12:34 AM, Mohit Sindhwani m...@onghu.com wrote: Hi, we are using SQLite3 + CEROD for a number of databases in an embedded systems application running on Windows CE. We're finding unexpectedly long time to open the database (0.5s ~ 2.8sec). Maybe, these times are reasonable, but they seem long to us. Did you read this thread: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg75761.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.7.16 beta
The draft website for SQLite version 3.7.16 can be seen on the http://www.sqlite.org/draft/ page and its descendents. The status board for 3.7.16 (http://www.sqlite.org/checklists/3071600) is now all green, meaning that all test cases have passed. We are simply waiting to give beta testers a few more days to check things out before we release the official version 3.7.16. If you have not done so already, please download either the canonical source code, or an amalgamation snapshot from http://www.sqlite.org/draft/download.html and try out SQLite version 3.7.16 in your application. Let us know if you encounter any problems. Please do so soon as we will cut the 3.7.16 release very early on Monday. Thank you for help. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] set journal off working witth SQLiteConfig?
On Wed, Mar 13, 2013 at 1:29 PM, Doug Crites doug.cri...@asg.com wrote:
Hello,
I am using the sqlite-jdbc-3.7.8-20111025.014814-1.jar , trying to set the
journaling off on my 'main' only opened db.
SQLiteConfig config = new SQLiteConfig();
config.setJournalMode(SQLiteConfig.JournalMode.OFF);
writeLog(Timestamp properties);
Properties propSqlite = config.toProperties();
When I run, I see from my properties display that the journaling is off,
but I still see a journal file being created when I run it.
{open_mode=6, journal_mode=OFF)
Does anyone have experience with turning this off thru the SqlLiteConfig
object? Any tips on usage?
I know nothing about the Java SQLiteConfig object.
Did you know you can change the journal mode directly using a pragma? Have
you tried:
PRAGMA journal_mode=OFF;
To see if that works for you?
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with journal_mode memory to wal
On Wed, Mar 13, 2013 at 12:45 PM, Vitor Rosa v.r...@campus.fct.unl.ptwrote: Hello, I'm using windows 7 (64 bits) with sqlite 3.7.15.2 and I can't change journal_mode to WAL, it always return memory mode. what I can do? For an in-memory database, the only allowed journal modes are OFF and MEMORY. Are you trying to change the journal mode to WAL on an in-memory database? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with journal_mode memory to wal
Hello, After reading more carefully the documentation I found that, but I want to create a db where I'm having just one writer and multiple readers, but I need that the readers never get db locked when they are reading so I thought using WAL journal_mode. But how can I create a WAL db? Many thanks, Vitor. Vitor Rosa Corporate Relations Local BEST Group Almada Faculdade de Ciências e Tecnologia da Universidade Nova de Lisboa +351 965 707 240 2013/3/14 Richard Hipp d...@sqlite.org On Wed, Mar 13, 2013 at 12:45 PM, Vitor Rosa v.r...@campus.fct.unl.pt wrote: Hello, I'm using windows 7 (64 bits) with sqlite 3.7.15.2 and I can't change journal_mode to WAL, it always return memory mode. what I can do? For an in-memory database, the only allowed journal modes are OFF and MEMORY. Are you trying to change the journal mode to WAL on an in-memory database? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with journal_mode memory to wal
On Thu, Mar 14, 2013 at 9:40 AM, Vitor Rosa v.r...@campus.fct.unl.ptwrote: Hello, After reading more carefully the documentation I found that, but I want to create a db where I'm having just one writer and multiple readers, but I need that the readers never get db locked when they are reading so I thought using WAL journal_mode. But how can I create a WAL db? You cannot have an in-memory WAL database (or at least not without writing your own custom in-memory VFS). If you want WAL, put the database on disk. Then everything should work just fine for you. If you do not care about persistence, set PRAGMA synchronous=OFF. Many thanks, Vitor. Vitor Rosa Corporate Relations Local BEST Group Almada Faculdade de Ciências e Tecnologia da Universidade Nova de Lisboa +351 965 707 240 2013/3/14 Richard Hipp d...@sqlite.org On Wed, Mar 13, 2013 at 12:45 PM, Vitor Rosa v.r...@campus.fct.unl.pt wrote: Hello, I'm using windows 7 (64 bits) with sqlite 3.7.15.2 and I can't change journal_mode to WAL, it always return memory mode. what I can do? For an in-memory database, the only allowed journal modes are OFF and MEMORY. Are you trying to change the journal mode to WAL on an in-memory database? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query optimizer suboptimal planning with virtual tables
Hi,
I have came across a glitch with how SQLite's query optimizer plans
virtual tables. Example follows:
I have a virtual table function named range that produces all numbers
from 1 to range's arg. This virtual table does not have any index
functionality.
With this i'll create the virtual table instance t1:
create virtual table t1 using range('100');
select * from t1;
1
2
...
99
100
--Column names--
C1
Let's create a real table now:
create table t2 as select * from t1;
The plan that the optimizer will produce when i join these two tables is
this:
explain query plan select * from t1, t2 where t1.c1=t2.c1;
0 |0 |1 | SCAN TABLE t2 (~100 rows)
0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows)
Even thought, i have put VT t1 first in the join list, SQLite will do a
nested loop join (putting it on the right).
Wouldn't it had made more sense for SQLite to create an automatic index
on the real table t2 and do the join as such?
0 |0 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows)
0 |1 |1 | SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX idx (C1=?) (~10
rows)
Putting the VT on the right by default, doesn't make much sense to me,
since it cannot create an automatic index on it.
In general it seems to me to be a better default to always have the non
automatic indexable SQLite entities (views, virtual tables) on the left
of the joins and what can be automatically indexed on the right of the
joins.
Also, i think, that it would be even better if SQLite had the ability to
scan the virtual table and build a temporary automatic covering index on
it to do the join (why isn't this case allowed?).
Thank you,
lefteris.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer suboptimal planning with virtual tables
Eleytherios Stamatogiannakis wrote: explain query plan select * from t1, t2 where t1.c1=t2.c1; 0 |0 |1 | SCAN TABLE t2 (~100 rows) 0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows) Even thought, i have put VT t1 first in the join list, SQLite will do a nested loop join (putting it on the right). How have you defined index 0 of your virtual table? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer suboptimal planning with virtual tables
On 14/03/13 17:05, Clemens Ladisch wrote: Eleytherios Stamatogiannakis wrote: explain query plan select * from t1, t2 where t1.c1=t2.c1; 0 |0 |1 | SCAN TABLE t2 (~100 rows) 0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows) Even thought, i have put VT t1 first in the join list, SQLite will do a nested loop join (putting it on the right). How have you defined index 0 of your virtual table? The INDEX 0: is always there (even when no index is defined in the VT function). I don't know what it means. l. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query optimizer bug?
Hi all, I'm running sqlite-3.7.13 on cygwin. Playing around with various TPC-H queries with my class recently, I hit a strangely slow query and don't understand why it's so slow. The schema and dataset generator are available at tpc.org, and end of this message has instructions to replicate my setup quickly. Short version for the impatient: running a particular query with selective predicates on the first of many tables to be joined, those predicates aren't applied until after the last join... even though the query uses an index on the predicate column to access the offending table. I would have expected the index probe to have the effect of pushing down the predicate, but pushing down predicates manually makes the query run ~20x faster. Long version follows... The offending query (slightly modified version of Q7: select count(*) from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'ALGERIA' and n2.n_name = 'EGYPT') or (n1.n_name = 'EGYPT' and n2.n_name = 'ALGERIA') ) and l_shipdate between '1995-01-01' and '1996-12-31'; The query counts 561 rows and takes 4.9 seconds to execute with a warm page cache (pragma cache_size = - 256000). Note that this query is a pain to optimize: the intuitive dataflow takes a v-shape, with lineitem at the point and the two nations at the ends (nation - customer - orders - lineitem - supplier - nation); the predicates are highly selective but involve nation (both ends of the chain) and lineitem (in the middle). The optimizer chooses this plan: 0|0|4|SCAN TABLE nation AS n1 (~25 rows) 0|1|5|SEARCH TABLE nation AS n2 USING INDEX nni (N_NAME=?) (~1 rows) 0|1|5|SEARCH TABLE nation AS n2 USING INDEX nni (N_NAME=?) (~1 rows) 0|2|0|SEARCH TABLE supplier USING INDEX snki (S_NATIONKEY=?) (~40 rows) 0|3|3|SEARCH TABLE customer USING INDEX cnki (C_NATIONKEY=?) (~600 rows) 0|4|2|SEARCH TABLE orders USING INDEX ocki (O_CUSTKEY=?) (~15 rows) 0|5|1|SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?) (~2 rows) Dropping index nni and disabling automatic indexing improves things a bit (3.3 s, 50% speedup): 0|0|4|SCAN TABLE nation AS n1 (~25 rows) 0|1|0|SEARCH TABLE supplier USING INDEX snki (S_NATIONKEY=?) (~40 rows) 0|2|1|SEARCH TABLE lineitem USING INDEX lski (L_SUPPKEY=?) (~300 rows) 0|3|2|SEARCH TABLE orders USING INDEX opki (O_ORDERKEY=?) (~1 rows) 0|4|3|SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?) (~1 rows) 0|5|5|SEARCH TABLE nation AS n2 USING INDEX npki (N_NATIONKEY=?) (~1 rows) Removing predicates on n1 yields ~15k rows in 3.4s, with the following plan: sqlite explain query plan select count(*) from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n2.n_name = 'ALGERIA') or (n2.n_name = 'EGYPT') ) and l_shipdate between '1995-01-01' and '1996-12-31'; 0|0|4|SCAN TABLE nation AS n1 (~25 rows) 0|1|0|SEARCH TABLE supplier USING INDEX snki (S_NATIONKEY=?) (~40 rows) 0|2|1|SEARCH TABLE lineitem USING INDEX lski (L_SUPPKEY=?) (~300 rows) 0|3|2|SEARCH TABLE orders USING INDEX opki (O_ORDERKEY=?) (~1 rows) 0|4|3|SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?) (~1 rows) 0|5|5|SEARCH TABLE nation AS n2 USING INDEX npki (N_NATIONKEY=?) (~1 rows) Presumably it's slow because predicates don't really hit until after all the joins finish. Removing predicates on n2 uses almost the same plan, and yields nearly the same row count (~14k rows), but executes in 0.25 s because the predicate applies before the first join: sqlite explain query plan select count(*) from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'ALGERIA' ) or (n1.n_name = 'EGYPT') ) and l_shipdate between '1995-01-01' and '1996-12-31'; 0|0|4|SCAN TABLE nation AS n1 (~25 rows) 0|1|0|SEARCH TABLE supplier USING INDEX snki (S_NATIONKEY=?) (~40 rows) 0|2|1|SEARCH TABLE lineitem USING INDEX lski (L_SUPPKEY=?) (~300 rows) 0|3|2|SEARCH TABLE orders USING INDEX opki (O_ORDERKEY=?) (~1 rows) 0|4|3|SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?) (~1 rows) 0|5|5|SEARCH TABLE nation AS n2 USING COVERING INDEX npki (N_NATIONKEY=?) (~1 rows) Working from that observation, I changed the query to manually push down predicates, which reduces the runtime to 0.25 s (~20x speedup): sqlite explain query plan select count(*) from supplier, lineitem, orders, customer, ( select * from nation where n_name = 'ALGERIA' or n_name = 'EGYPT' ) n1, nation n2 where s_suppkey =
[sqlite] Announcing Zumero (commercial venture, built on SQLite)
I'll keep this short and low-key: Zumero is a sync solution for SQLite, designed for mobile devices. http://zumero.com/ Here's my blog post on it: http://ericsink.com/entries/announcing_zumero.html -- E ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer bug?
Nitpick: A bug means it gets the wrong answer, which is not the case here. What you are reporting here is not a bug but an optimization opportunity. On Thu, Mar 14, 2013 at 2:07 PM, Ryan Johnson ryan.john...@cs.utoronto.cawrote: The offending query (slightly modified version of Q7: select count(*) from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'ALGERIA' and n2.n_name = 'EGYPT') or (n1.n_name = 'EGYPT' and n2.n_name = 'ALGERIA') ) and l_shipdate between '1995-01-01' and '1996-12-31'; The optimizer chooses this plan: 0|0|4|SCAN TABLE nation AS n1 (~25 rows) 0|1|5|SEARCH TABLE nation AS n2 USING INDEX nni (N_NAME=?) (~1 rows) 0|1|5|SEARCH TABLE nation AS n2 USING INDEX nni (N_NAME=?) (~1 rows) 0|2|0|SEARCH TABLE supplier USING INDEX snki (S_NATIONKEY=?) (~40 rows) 0|3|3|SEARCH TABLE customer USING INDEX cnki (C_NATIONKEY=?) (~600 rows) 0|4|2|SEARCH TABLE orders USING INDEX ocki (O_CUSTKEY=?) (~15 rows) 0|5|1|SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?) (~2 rows) Dropping index nni and disabling automatic indexing improves things a bit (3.3 s, 50% speedup): 0|0|4|SCAN TABLE nation AS n1 (~25 rows) 0|1|0|SEARCH TABLE supplier USING INDEX snki (S_NATIONKEY=?) (~40 rows) 0|2|1|SEARCH TABLE lineitem USING INDEX lski (L_SUPPKEY=?) (~300 rows) 0|3|2|SEARCH TABLE orders USING INDEX opki (O_ORDERKEY=?) (~1 rows) 0|4|3|SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?) (~1 rows) 0|5|5|SEARCH TABLE nation AS n2 USING INDEX npki (N_NATIONKEY=?) (~1 rows) SQLite version 3.7.16 chooses the second plan regardless. So that much has already been addressed. Presumably it's slow because predicates don't really hit until after all the joins finish. Sort of. SQLite does evaluate predicates as soon as it can. It doesn't wait until after the inner join finishes to evaluate the predicates. As soon as all information needed for a predicate is available, it is evaluated. The problem is that SQLite does not do a lot of algebraic manipulation of predicates to try to factor out terms that can be evaluated early. So the predicate: (n1.n_name='ALGERIA' and n2.n_name='EGYPT') OR (n1.n_name='EGYPT' and n2.n_name='ALGERIA') is treated as a unit and cannot be evaluated until both n1 and n2 are available. If SQLite were to be enhanced to deal with this case, what it would need to do is factor this into three separate conjuncts, as follows: (n1.n_name='ALGERIA' AND n2.n_name='EGYPT') OR (n1.n_name='EGYPT' and n2.n_name='ALGERIA') AND (n1.n_name='ALGERIA OR n1.n_name='EGYPT') AND (n2.n_name='EGYPT' OR n2.n_name='ALGERIA') The second two are entirely redundant in the sense that if the first is true then the second two are also true. (SQLite has a method of marking them so and making sure they are not evaluated if the first is evaluated. Similar auxiliary conjuncts are used to help optimize LIKE, GLOB, and BETWEEN operators) But the second two conjuncts also depend on just a single table, so they have the option of being evaluated early whereas the first must wait until both tables have been evaluated. If you augment the WHERE clause of your query by adding AND (n1.n_name='ALGERIA' OR n1.n_name='EGYPT') you get the observed speedup. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer suboptimal planning with virtual tables
Eleytherios Stamatogiannakis wrote: On 14/03/13 17:05, Clemens Ladisch wrote: Eleytherios Stamatogiannakis wrote: explain query plan select * from t1, t2 where t1.c1=t2.c1; 0 |0 |1 | SCAN TABLE t2 (~100 rows) 0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows) Even thought, i have put VT t1 first in the join list, SQLite will do a nested loop join (putting it on the right). How have you defined index 0 of your virtual table? The INDEX 0: is always there (even when no index is defined in the VT function). I don't know what it means. Well, what does your xBestIndex callback return? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer bug?
On 14/03/2013 3:09 PM, Richard Hipp wrote: Nitpick: A bug means it gets the wrong answer, which is not the case here. What you are reporting here is not a bug but an optimization opportunity. Oops... you're right. Sorry about that. On Thu, Mar 14, 2013 at 2:07 PM, Ryan Johnson ryan.john...@cs.utoronto.cawrote: The offending query (slightly modified version of Q7: select count(*) from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'ALGERIA' and n2.n_name = 'EGYPT') or (n1.n_name = 'EGYPT' and n2.n_name = 'ALGERIA') ) and l_shipdate between '1995-01-01' and '1996-12-31'; The optimizer chooses this plan: 0|0|4|SCAN TABLE nation AS n1 (~25 rows) 0|1|5|SEARCH TABLE nation AS n2 USING INDEX nni (N_NAME=?) (~1 rows) 0|1|5|SEARCH TABLE nation AS n2 USING INDEX nni (N_NAME=?) (~1 rows) 0|2|0|SEARCH TABLE supplier USING INDEX snki (S_NATIONKEY=?) (~40 rows) 0|3|3|SEARCH TABLE customer USING INDEX cnki (C_NATIONKEY=?) (~600 rows) 0|4|2|SEARCH TABLE orders USING INDEX ocki (O_CUSTKEY=?) (~15 rows) 0|5|1|SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?) (~2 rows) Dropping index nni and disabling automatic indexing improves things a bit (3.3 s, 50% speedup): 0|0|4|SCAN TABLE nation AS n1 (~25 rows) 0|1|0|SEARCH TABLE supplier USING INDEX snki (S_NATIONKEY=?) (~40 rows) 0|2|1|SEARCH TABLE lineitem USING INDEX lski (L_SUPPKEY=?) (~300 rows) 0|3|2|SEARCH TABLE orders USING INDEX opki (O_ORDERKEY=?) (~1 rows) 0|4|3|SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?) (~1 rows) 0|5|5|SEARCH TABLE nation AS n2 USING INDEX npki (N_NATIONKEY=?) (~1 rows) SQLite version 3.7.16 chooses the second plan regardless. So that much has already been addressed. Great! I'll give it a try. Presumably it's slow because predicates don't really hit until after all the joins finish. Sort of. SQLite does evaluate predicates as soon as it can. It doesn't wait until after the inner join finishes to evaluate the predicates. As soon as all information needed for a predicate is available, it is evaluated. The problem is that SQLite does not do a lot of algebraic manipulation of predicates to try to factor out terms that can be evaluated early. So the predicate: (n1.n_name='ALGERIA' and n2.n_name='EGYPT') OR (n1.n_name='EGYPT' and n2.n_name='ALGERIA') is treated as a unit and cannot be evaluated until both n1 and n2 are available. That's what I figured; part of the lite in sqlite. If SQLite were to be enhanced to deal with this case, what it would need to do is factor this into three separate conjuncts, as follows: (n1.n_name='ALGERIA' AND n2.n_name='EGYPT') OR (n1.n_name='EGYPT' and n2.n_name='ALGERIA') AND (n1.n_name='ALGERIA OR n1.n_name='EGYPT') AND (n2.n_name='EGYPT' OR n2.n_name='ALGERIA') The second two are entirely redundant in the sense that if the first is true then the second two are also true. (SQLite has a method of marking them so and making sure they are not evaluated if the first is evaluated. Similar auxiliary conjuncts are used to help optimize LIKE, GLOB, and BETWEEN operators) But the second two conjuncts also depend on just a single table, so they have the option of being evaluated early whereas the first must wait until both tables have been evaluated. If you augment the WHERE clause of your query by adding AND (n1.n_name='ALGERIA' OR n1.n_name='EGYPT') you get the observed speedup. I guess that feature got added after 3.7.13; it has no impact on 3.7.13, with or without the n_name index present, but a home-brew 3.7.16 beta build handles it fine. Actually, I get 0.21 s response time for even the original, unmodified query using 3.7.16. None of this explains what purpose all those index probes on nation.n_name were doing if it wasn't applying predicates, but I guess it doesn't matter since the problem has been fixed in later versions. Thanks, Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL Test failures on ARM
Hi, can any one tel me how can i fix this issue ? Thank you Brijesh -- View this message in context: http://sqlite.1065341.n5.nabble.com/TCL-Test-failures-on-ARM-tp67612p67727.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
