Re: [sqlite] System.Data.SQLite version 1.0.89.0 released

[Joe Mistachkin]

Jan Slodicka wrote:
>
> bool exists;
> exists = idxs.TryGetValue("Abc", out i);// true => ok
> exists = idxs.TryGetValue("abc", out i);// false => BUG
>

Thanks, fixed on trunk.  SQLiteDataReader now uses the built-in
StringComparer class instead.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Igor Tandetnik]

On 11/28/2013 2:40 AM, Tristan Van Berkom wrote:

I.e. is the statement logically the same ?


Yes, I do believe that the two queries shown in your original post are 
logically equivalent.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite version 1.0.89.0 released

[Jan Slodicka]

This looks like a bug (ColumnNameComparer is taken from SqliteDataReader
1.0.89):

var idxs = new Dictionary(new ColumnNameComparer());
idxs.Add("Xyz", 1);
idxs.Add("Abc", 2);

bool exists;
exists = idxs.TryGetValue("Abc", out i);// true => ok
exists = idxs.TryGetValue("abc", out i);// false => BUG

Note also that idxs.Add(null, 3) throws an exception. It means null string
can't be added to the dictionary. (Which seems to be a reason behind using
ColumnNameComparer.)

In my opinion ColumnnNameComparer should be replaced by
StringComparer.OrdinalIgnoreCase.


Jan Slodicka



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/System-Data-SQLite-version-1-0-89-0-released-tp71904p72636.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] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Tristan Van Berkom]

On Thu, 2013-11-28 at 12:19 +, Simon Slavin wrote:
> On 28 Nov 2013, at 11:22am, Tristan Van Berkom  
> wrote:
> 
> > Yes, I definitely agree that on a conceptual level, I should not
> > have to consider the pre-optimization of my own query before
> > launching it. As a functional language, I should only have to
> > describe the query and let SQLite do a better optimization.
> 
> While you're concerned about optimization, you might look at your column 
> definitions.  For instance, you are storing email addresses in a column.  
> Since email addresses are not case sensitive you might want to define this 
> column as
> 
> COLLATE NOCASE
> 
> This will mean that any indexing on this column will ignore case, and any 
> comparisons of text with text in this column will ignore case by default.  It 
> can simplify all other SQL statements that refer to that column.
> 

That's not an issue, our matching API is completely case insensitive,
and all data which we insert into columns for searches is normalized
in advance (excepting the vCard UID field)... or normalized and reversed
in the case we configure for quick suffix matching.

Thanks for the tip anyway, though :)

Cheers,
-Tristan



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Simon Slavin]

On 28 Nov 2013, at 11:22am, Tristan Van Berkom  wrote:

> Yes, I definitely agree that on a conceptual level, I should not
> have to consider the pre-optimization of my own query before
> launching it. As a functional language, I should only have to
> describe the query and let SQLite do a better optimization.

While you're concerned about optimization, you might look at your column 
definitions.  For instance, you are storing email addresses in a column.  Since 
email addresses are not case sensitive you might want to define this column as

COLLATE NOCASE

This will mean that any indexing on this column will ignore case, and any 
comparisons of text with text in this column will ignore case by default.  It 
can simplify all other SQL statements that refer to that column.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Tristan Van Berkom]

On Thu, 2013-11-28 at 12:11 +0100, Clemens Ladisch wrote:
> Tristan Van Berkom wrote:
> > Are the JOIN statements equal to the logical AND statements,
> 
> Yes.
> 

Thank you.

> > for all practical purposes ?
> 
> If you drop all those superfluous LEFT OUTER and IS NOT NULL parts,
> the database will be able to optimize the first query (the one
> without subqueries) better.

Yes, I definitely agree that on a conceptual level, I should not
have to consider the pre-optimization of my own query before
launching it. As a functional language, I should only have to
describe the query and let SQLite do a better optimization.

My skills in writing SQL are (already admittedly) lacking and
so, after months (of myself, and other competent engineers
involved), I ended up with this convoluted nested select thing.

I will definitely try this solution as it will significantly
simplify my query generator, but I'll need to benchmark it
with big data sets before I'm satisfied, of course.

Thanks for your help,

-Tristan


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Clemens Ladisch]

Tristan Van Berkom wrote:
> Are the JOIN statements equal to the logical AND statements,

Yes.

> for all practical purposes ?

If you drop all those superfluous LEFT OUTER and IS NOT NULL parts,
the database will be able to optimize the first query (the one
without subqueries) better.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Tristan Van Berkom]

On Thu, 2013-11-28 at 09:43 +0100, Clemens Ladisch wrote:
> Tristan Van Berkom wrote:
> > When using an INNER join, the engine does something like this:
> >
> >   o Create a data set that is table_1 * table_2 * table_3 rows
> > large
> >
> >   o Run the constraints on what might be multiple matching rows
> > in the resulting huge data set (even if I nest the selects,
> > there can be other constraints to sort out on the main table).
> 
> This is wrong; constraints on the outer table are checked before records
> from the inner table are searched.
> 
> > This bug comment has a good detailed description of the reason
> > why we shifted from regular joins to LEFT OUTER joins:
> > https://bugzilla.gnome.org/show_bug.cgi?id=699597#c6
> 
> That query was slow because it did not do any join to begin with,
> ,not even with "a.id=b.id" in the WHERE clause; instead, lots of
> constraints were combined with OR.
> 
> > If I were to create indexes on the uid column of the auxiliary
> > tables, would that cause the INNER join to not create such a
> > huge dataset before checking the constraints ?
> 
> I might or might not make a difference; check with EXPLAIN QUERY PLAN.

Yes I will have to do more research, we'll see.

> 
> >> WHERE phone_list.value LIKE '%0505'
> 
> In theory, you could enable index usage by using:
> 
>WHERE phone_list.value_reversed LIKE '5050%'
> 
> Not sure if this would be worth the effort.

It is, and we store data in reverse to speed up suffix
searches in some cases (if configured to do so).

> 
> >> Normally, you need case-sensitive LIKE in order to use the index, unless
> >> the index is created with COLLATE NOCASE.
> 
> Also, the column must have text affinity.
> 
> > LIKE is case insensitive by default and we override that indeed, using
> > "PRAGMA case_sensitive_like=ON" at initialization time.
> 
> To avoid that, you could use "GLOB 'foo*'" instead of "LIKE 'foo%'".

However I found the escape sequences to be more easy with LIKE
statements, so better not to avoid that.

Again, while I appreciate the comments about how we think the
best way SQLite should work in the most optimal way, does anyone
have an answer to the question ?

Are the JOIN statements equal to the logical AND statements,
for all practical purposes ?

Best Regards,
-Tristan




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

[Clemens Ladisch]

Tristan Van Berkom wrote:
> When using an INNER join, the engine does something like this:
>
>   o Create a data set that is table_1 * table_2 * table_3 rows
> large
>
>   o Run the constraints on what might be multiple matching rows
> in the resulting huge data set (even if I nest the selects,
> there can be other constraints to sort out on the main table).

This is wrong; constraints on the outer table are checked before records
from the inner table are searched.

> This bug comment has a good detailed description of the reason
> why we shifted from regular joins to LEFT OUTER joins:
> https://bugzilla.gnome.org/show_bug.cgi?id=699597#c6

That query was slow because it did not do any join to begin with,
,not even with "a.id=b.id" in the WHERE clause; instead, lots of
constraints were combined with OR.

> If I were to create indexes on the uid column of the auxiliary
> tables, would that cause the INNER join to not create such a
> huge dataset before checking the constraints ?

I might or might not make a difference; check with EXPLAIN QUERY PLAN.

>> WHERE phone_list.value LIKE '%0505'

In theory, you could enable index usage by using:

   WHERE phone_list.value_reversed LIKE '5050%'

Not sure if this would be worth the effort.

>> Normally, you need case-sensitive LIKE in order to use the index, unless
>> the index is created with COLLATE NOCASE.

Also, the column must have text affinity.

> LIKE is case insensitive by default and we override that indeed, using
> "PRAGMA case_sensitive_like=ON" at initialization time.

To avoid that, you could use "GLOB 'foo*'" instead of "LIKE 'foo%'".


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users