Re: [sqlite] Hints for the query planner
On Tuesday, September 10, 2013, Richard Hipp wrote: > SURVEY QUESTION: > > The question for today is what to call this magic hint function: > > (1) unlikely(EXPR) > (2) selective(EXPR) > (3) seldom(EXPR) > (4) seldom_true(EXPR) > (5) usually_not_true(EXPR) (1), on account of: it's already used in various programming languages. However, because o your point below i prefer "likely(, )", perhaps with the probability being required (can the probability be a non-constant expression?). Please feel free to suggest other names if you think of any. > > ADDITIONAL INFORMATION: > > The current implementation allows a second argument which must be a > floating point constant between 0.0 and 1.0, inclusive. The second argument > is an estimate of the probability that the expression in the first argument > will be true. The default is 0.05. Names like "unlikely" or "seldom" work > well when this probability is small, but if the second argument is close to > 1.0, then those names seem backwards. I don't know if this matters. The > optional second argument is not guaranteed to make it into an actually > release. > Right, but this is less annoying when the keyword name denotes likeliness: the probability argument is quite clearly a modifier of likelihood. That's another possible name: "likelihood()". Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.8.1 beta
Warren Young wrote: > > It can't build a shared library (DLL) on Cygwin any more: > > ./libtool --mode=link gcc -g -O2 -DSQLITE_OS_WIN=1 -I. -I./src > -I./ext/rtree -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG > -I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 > -o libsqlite3.la sqlite3.lo \ > -rpath "/usr/local/lib" -version-info "8:6:8" > libtool: link: warning: undefined symbols not allowed in i686-pc-cygwin > shared libraries > Are there any further details you could provide that would point us in the direction of which symbols are "undefined"? > > If new declarations sans definitions weren't actually added, perhaps > someone forgot to add: > > #ifdef _WIN32 > __declspec(dllexport) > #endif > It looks like you are compiling the amalgamation. What were the command line options used to generate it? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.8.1 beta
On 9/30/2013 07:39, Richard Hipp wrote: SQLite version 3.8.1 will be published before too much longer, probably. You can find beta versions at http://www.sqlite.org/download.html Comments, criticisms, and third-party testing of this beta is appreciated. It can't build a shared library (DLL) on Cygwin any more: ./libtool --mode=link gcc -g -O2 -DSQLITE_OS_WIN=1 -I. -I./src -I./ext/rtree -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -o libsqlite3.la sqlite3.lo \ -rpath "/usr/local/lib" -version-info "8:6:8" libtool: link: warning: undefined symbols not allowed in i686-pc-cygwin shared libraries If new declarations sans definitions weren't actually added, perhaps someone forgot to add: #ifdef _WIN32 __declspec(dllexport) #endif ...in front of some definition? Shouldn't SQLITE_API be defined to this on Windows anyway? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SqLITE .NET Question - Connection default timeout / dyingafter inactivity
Michael wrote: > > > 25/09/2013 09:28:01 :: SQLite error > > no such table: faxes > > Killing the program, then re-opening will let it run correctly for another > 24 hours. The function calls to generate the SqLITE connection on launch > and on resume are identical - same code is called, same parameters passed. > Unsure how to proceed from here - any ideas? > Are you able to examine the database file with the command line SQLite tool to determine if the table exists? Is the database located on a network file share? It sounds like there are two applications accessing the database. Which one are you having to kill? Do you have any sample code which would help us identify the problem? What version of SQLite is being used? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SqLITE .NET Question - Connection default timeout / dying after inactivity
We have a fairly simple communications server set up here. We recently
added electronic faxing. We're using a 3rd party component to handle that,
which saves the faxes it receives to a SqLITE database. To tie it to our
existing systems, we've hacked up a little synchronizer which scans the
SqLITE database for changes every couple of minutes, and pulls some records
across into our existing database.
During normal operations, the SqLITE connection is left open. This works
fine so long as the scanner is running. The system pauses itself between
11PM and 7AM. When that happens, the SqLITE connection is closed, and the
connection object in .net disposed. When the system resumes at 7AM, the
connection object is re-created. Connection string used is "Data
Source={0}; Version=3; FailIfMissing=True". The connection creates and
opens without error, but when we attempt to access the datatable, we get an
exception:
> 25/09/2013 09:28:01 :: SQLite error
> no such table: faxes
Killing the program, then re-opening will let it run correctly for another
24 hours. The function calls to generate the SqLITE connection on launch
and on resume are identical - same code is called, same parameters passed.
Unsure how to proceed from here - any ideas?
Thanks -
Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.8.1 beta
"Richard Hipp" wrote... SQLite version 3.8.1 will be published before too much longer, probably. You can find beta versions at http://www.sqlite.org/download.html Comments, criticisms, and third-party testing of this beta is appreciated. Installed it and tested our tool: it works as good as 3.8.0. :-) We have not added any of the new features, yet. jic ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] version 3.8 query planner doesn't find same or better plan for simple query over rtree tables
Hello , we are facing performance regression in sqlite version 3.8 and higher versions on queries over r-tree tables and are unable to solve them , so we are asking professionals to consider severity of described problem: description is little Littler but , contains everything relevat. we have 1 table containing set of nodes with their X,Y coordinates Base_node_set (ID,X,Y) Then we have r-tree table with bounding boxes BBox_set_r_tree (ID,MAX_X,MIN_X,MAX_Y,MIN_Y) then we want to select for all nodes all its bounding boxes , the node lies in we do it by executing this statement select * from base_node_set as base join BBox_set_r_tree as r_tree on r_tree.MIN_X <= base.X and r_tree.MAX_X >= base.X and r_tree.MIN_Y <= base.Y and r_tree.MAX_Y >= base.Y order by base.ID in prior versions of sqlite query planner query plan was: ORDER DETAIL 1.SCAN TABLE base_node_set AS base USING INTEGER PRIMARY KEY (~100 rows) 2.SCAN TABLE BBox_set_r_tree AS r_tree VIRTUAL TABLE INDEX 2:BaDbBc (~0 rows) On sqlite 3.8.0 and higher query plan of the same statement is little different order DETAIL 1. SCAN TABLE osm_road_nodes_r_tree AS r_tree VIRTUAL TABLE INDEX 2: 2. SCAN TABLE sp_house_numbers AS base 0. 0 USE TEMP B-TREE FOR ORDER BY does first plan say?: Scan nodes and for every node find its bboxes where the node lies (no step for order by is required because of use primary integer key in 1. scan) Does second one say? scan bboxes and for every bbox find nodes in it, then order result by nodes ID Do I interpret such query plan correctly? If so, I think that this is a bug in query planning with r_trees. I dont know how to force new version of sqlite query planner to use old version plan. Have tried to use CROSS JOIN instead of JOIN and the resulting plan was the same. we now use the newest 3.8.0.2 and compile time options are: -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_MEMORY_MANAGEMENT=1 -DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_STAT3=1 analyze has been run with no change to query plan, this both tables contain milions of entries, but is more often that the rtree table is much more larger than node set table, so it‘s our intention to reduce searches in r_tree table to minimum because of its size. We also ran such queries on less populated tables, but, the diference in query plans makes huge impact on performance on them too. I have noticed 3.8.1 core function unlikely() ,but in such simple query cant find out how the unlikely() function use, select from empty table and then cross join in right order from that tables did not changed query plan as well. What are other options to control query plans over rtree queries plans? I am asking because , we were forced to upgrade sqlite version in our application from 3.7.6 because of occurences of disk I/O error codes in execution of such statements on larger datasets, we run our application on windows OS, after upgrade to 3.8.0 disk I/O errors dissapeared, but now with latest 3.8.0.2 we have completely different logic of query plans over rtree tables and are unable to rewrite them to be executed like in former versions of sqlite. This is a case when NGQP find worst plan and execution time jumps from minutes to years. Can anyone help solve this problem? thank you for any reply Best regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
