Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Keith Medcalf
You also need to make sure the "no hit" does not degenerate into a table scan. RTree works well for this but is overall significantly slower than not using RTree since the purpose of RTree is to find the "small number of candidate records" that could possibly satisfy the query out of a

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread E.Pasma
About the rtree extension, which was the first idea. The extension appears available without any special installation option. This is easier than what is mentioned in https://sqlite.org/rtree.html chapter 2: "Compiling The R*Tree Module". This chapter may as

Re: [sqlite] Exception handling during create table from a virtual one

2018-10-26 Thread Max Vlasov
On Fri, Oct 26, 2018 at 5:50 PM Clemens Ladisch wrote: > > Аfter the exception is thrown > > If I continue the execution > > You must return from the callback function normally, or abort the process. > Anything else will corrupt SQLite's internal state. > > Thanks, probably it's better

Re: [sqlite] Regarding CoC

2018-10-26 Thread Petite Abeille
> On Oct 26, 2018, at 3:21 PM, Thomas Kurz wrote: > >> What'da ya think? > > That's a great idea. I've already had some concerns that SQLite development > might cease now. Hoping for great new features in the next release :-) MERGE! :D ___

Re: [sqlite] Invalid results on join and duplicate where clause (in-memory db only)

2018-10-26 Thread Richard Hipp
On 10/26/18, Sebastian Zwack wrote: > > Out of curiosity, why do in-memory and file based differ here? Are these > query optimizations only done for the in-memory db? > I observe the problem regardless of whether the database is on-disk or in-memory. Perhaps your system is somehow using a

Re: [sqlite] Invalid results on join and duplicate where clause (in-memory db only)

2018-10-26 Thread Sebastian Zwack
On Thu, Oct 25, 2018, at 16:20, Richard Hipp wrote: > Sebastian: Presumably this problem arose for you in a much larger and > more complex application. Can you please apply the patch from the > check-in above (or recompile using the latest trunk version of SQLite) > and verify for us that this

Re: [sqlite] Exception handling during create table from a virtual one

2018-10-26 Thread Clemens Ladisch
Max Vlasov wrote: > I have a virtual table that raises an unhandled exception during a create > table SQLite is written in C. The C language does not have exceptions. > Аfter the exception is thrown > If I continue the execution You must return from the callback function normally, or

Re: [sqlite] Using SQLite in C# without System.Data.SQLite

2018-10-26 Thread Bernd Lehmkuhl
Thank you so much for your reply. I sill surely look at the options you gave me. I ask because I run into errors a lot, using the System.Data.SQLite libraries. It easily crashes, trhows exceptions and similar issues... I found out that it matters which target platform is selected... So

Re: [sqlite] Regarding CoC

2018-10-26 Thread Thomas Kurz
> What'da ya think? That's a great idea. I've already had some concerns that SQLite development might cease now. Hoping for great new features in the next release :-) -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

Re: [sqlite] Regarding CoC

2018-10-26 Thread Richard Hipp
Hey, Y'all: Thanks for all the input. But we've strayed off-topic. In keeping with both the Code of Conduct and the Code of Ethics, maybe its time we shut this discussion down (or move it to some other venue) and get back to talking about the worlds greatest embedded database. What'da ya

Re: [sqlite] Regarding CoC

2018-10-26 Thread Philip Warner
On 26/10/2018 11:02 PM, Gary R. Schmidt wrote: On 26/10/2018 14:12, Philip Warner wrote: I agree with the humour. As a satirical statement on the horrors of building a good CoC/CoE it is quite effective. But...if it is a CoC/E, then I think it would be beneficial to have one that many

Re: [sqlite] Regarding CoC

2018-10-26 Thread Gary R. Schmidt
On 26/10/2018 14:12, Philip Warner wrote: On 25/10/2018 9:59 PM, Mike King wrote: I’m a good atheist but I love the CoC. Not bothered by the religious bits but I get the sentiment. I guess it appeals to my British sense of irony and odd sense of humour :) I agree with the humour. As a

[sqlite] Exception handling during create table from a virtual one

2018-10-26 Thread Max Vlasov
Hi, I have a virtual table that raises an unhandled exception during a create table ... as select * from {myvirtualtable} command, inside xColumn callback. I already fixed the error inside my library to handle the situation reasonably, but there's something I noticed for the non-handled exception

Re: [sqlite] Regarding CoC

2018-10-26 Thread Keith Medcalf
On Friday, 26 October, 2018 03:57, Petite Abeille wrote: >> On Oct 26, 2018, at 5:12 AM, Philip Warner >wrote: >> knowingly and deliberately ignoring large chunks, and broadly >> disagreeing with even more, and laughing at the rest. > Bah… Everything Is Amazing And Nobody Is Happy: >

Re: [sqlite] Regarding CoC

2018-10-26 Thread Petite Abeille
> On Oct 26, 2018, at 5:12 AM, Philip Warner wrote: > > knowingly and deliberately ignoring large chunks, and broadly disagreeing > with even more, and laughing at the rest. Bah… Everything Is Amazing And Nobody Is Happy: https://www.youtube.com/watch?v=nUBtKNzoKZ4

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Keith Medcalf
Limit 1 says to stop after returning 1 row. If the "first row" being searched is not the one containing "the answer" then the search will continue until the row that does not match the index constraint is hit, after which it is known that no answer is possible (without returning a row). ---

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Keith Medcalf
Based on your assumptions being correct (a) start is unique (b) start end ranges do not overlap create table ranges ( start integer primary key, stop integer not null, value integer not null ); INSERT INTO ranges values (1, 10, 5); INSERT INTO ranges values (15, 29, 8); INSERT INTO

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Olivier Mascia
> Le 26 oct. 2018 à 09:27, siscia a écrit : > > thanks for your suggestions, unfortunately, I already tried all of them, > except for the rtrees. > > Actually, my request for help wasn't complete. > > The ranges I am storing in the table are not overlapping. > > To make an example in SQL. >

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread siscia
Sorry, I was a little too optimistic. Making the starts unique does help only for some queries, not for all. Why? Cheers, Simone -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread siscia
Ok, after the message I thought a little bit more. And it turns out that in the database the `start`s are not unique how they should. Making them unique, seems to solve the performance problem completely. However, still, I am not sure why the `LIMIT 1` does not help at all. Can you guys shed

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Dan Kennedy
On 10/26/2018 02:27 PM, siscia wrote: Hi all, thanks for your suggestions, unfortunately, I already tried all of them, except for the rtrees. Actually, my request for help wasn't complete. The ranges I am storing in the table are not overlapping. To make an example in SQL. The following can

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread siscia
Hi all, thanks for your suggestions, unfortunately, I already tried all of them, except for the rtrees. Actually, my request for help wasn't complete. The ranges I am storing in the table are not overlapping. To make an example in SQL. The following can be in the dataset: INSERT INTO