[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Zsbán Ambrus]

On Mon, Jul 27, 2015 at 9:35 PM, Simon Slavin  wrote:
> On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus  wrote:
> I tried this once a couple of years ago, and both platforms use whatever the 
> expected variable name was for that OS.  In other words, a native programmer 
> to that OS would get whatever behaviour they expected.

Hopefully that means TMPDIR on unix and TEMP on windows, which seem to
be the most widely used environment variables for this.

Sadly, programs aren't uniform in what environment variable(s) they
respect for this.  I think something on some system uses the TMP or
TMP_DIR variables. Some simply don't respect anything and just put
temporary files in the current directory or your home directory
regardless.

>From the source code, it seems that for at least some operations,
sqlite3 checks the following environment variables: SQLITE_TMPDIR,
TMPDIR, TMP, TEMP, USERPROFILE.

-- Zsb?n Ambrus

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Simon Slavin]

> On 27 Jul 2015, at 10:18pm, Zsb?n Ambrus  wrote:
> 
> On Mon, Jul 27, 2015 at 9:35 PM, Simon Slavin  wrote:
>> On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus  wrote:
>> I tried this once a couple of years ago, and both platforms use whatever the 
>> expected variable name was for that OS.  In other words, a native programmer 
>> to that OS would get whatever behaviour they expected.
> 
> Hopefully that means TMPDIR on unix and TEMP on windows, which seem to
> be the most widely used environment variables for this.
> 
> [snip]
> 
> From the source code, it seems that for at least some operations,
> sqlite3 checks the following environment variables: SQLITE_TMPDIR,
> TMPDIR, TMP, TEMP, USERPROFILE.

Sorry, I got a detail wrong.  I tested Mac, Windows and Linux, not Unix.  On 
all platforms I checked to see that changing the expected documented 
environment variable had the right effect.  I didn't test what happened if you 
changed another variable.

Simon.

[sqlite] index for OR clause

[Sylvain Pointeau]

On Mon, Jul 27, 2015 at 8:27 PM, R.Smith  wrote:

>
>
> On 2015-07-27 08:09 PM, Simon Slavin wrote:
>
>> On 27 Jul 2015, at 6:58pm, Sylvain Pointeau 
>> wrote:
>>
>>  create table TEST (
>>> a TEXT NOT NULL,
>>> a2 TEXT NOT NULL,
>>> b TEXT NOT NULL,
>>> c TEXT NOT NULL
>>> );
>>>
>>> create index IDX_TEST_1 on TEST(a,a2,b,c);
>>>
>>> insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
>>> insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
>>> insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
>>> insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');
>>>
>>> sqlite> explain query plan select * from TEST where (a = '123' or
>>> a2='1234') and b = '456' and c='PP';
>>>
>>> 0|0|0|SCAN TABLE TEST
>>>
>>> whereas
>>>
>>> sqlite> explain query plan select * from TEST where a = '123' and b =
>>> '456'
>>> and c='PP';
>>>
>>> 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
>>>
>>> how to make this query using an index? is this possible?
>>>
>> That is a good job of investigation you have done there.  What you didn't
>> take into account is that the order of columns in an index matters.
>>
>> Your example has known precise values for b and c, and sometimes but not
>> always knows a and a2.  So b and c should be up front in your index:
>>
>> create index IDX_TEST_2 on TEST(b,c,a,a2);
>>
>
> Quite, but there is also another consideration: Cardinality.  Now it might
> be that this data extract is not representative of the whole table at all,
> but from the looks of it, those columns b and c offer severely low
> cardinality, perhaps in the order of more than 30% of the table in
> duplications.
>
> If this is the case, an index on c or b will amount to pretty much a
> table-scan still. You really want to hit those high-cardinality columns in
> your leading indexer (which is why Richard rightly suggested the double
> index).
>
> I'd almost think having the less-expensive set of these:
> create index IDX_TEST_1 on TEST(a);
> create index IDX_TEST_2 on TEST(a2);
> would produce very near the same speeds for that query if the cardinality
> of columns b and c remain low for populated data.
>
> If however the cardinality scales up pretty well, then Richard's
> suggestion is much better, and if the cardinality will be even higher in b
> and c than in the a's, then Simon's suggestion is best.
>

in my case, c cardinality was low, (a,b) and (a2,b) almost unique

I suppose that in my case an index on c and b would give good result :-)

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Zsbán Ambrus]

On Mon, Jul 27, 2015 at 12:28 PM, Richard Hipp  wrote:
> On 7/27/15, Paolo Bolzoni  wrote:
>> I found the temp_store_directory, but it is deprecated. So I was wondering, 
>> what is the suggested alternative?
>
> Set the TEMP environment variable to the location of your temporary
> storage area.

Thank you, I was wondering on this too.  Can you please document this
right at that pragma on the pragmas page
"http://sqlite.org/pragma.html#pragma_temp_store_directory"; ?

Does this work with the same environment variable name on both unix
and windows?  I'm asking because unix and windows programs each use
different conventions for what environment variable to care about when
determining the temporary directory, and these conventions differ on
unix and windows, though I believe TEMP is the customary variable on
windows (it comes from back when the DOS shell stored pipe data
there).

Tangentially related, is there a way to set a limit the size of
temporary files sqlite3 will create, such as with a pragma or
sqlite3_limit call?  I've asked this already around 2015-05-18, but I
don't think I've got an answer.

-- Zsb?n Ambrus

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Simon Slavin]

On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus  wrote:

> Does this work with the same environment variable name on both unix
> and windows?  I'm asking because unix and windows programs each use
> different conventions for what environment variable to care about when
> determining the temporary directory, and these conventions differ on
> unix and windows, though I believe TEMP is the customary variable on
> windows

I tried this once a couple of years ago, and both platforms use whatever the 
expected variable name was for that OS.  In other words, a native programmer to 
that OS would get whatever behaviour they expected.

If it's going to be documented, it would also be nice to see it on



which, according to my browser, doesn't mention the word 'environment'.

Simon.

[sqlite] index for OR clause

[R.Smith]

On 2015-07-27 08:09 PM, Simon Slavin wrote:
> On 27 Jul 2015, at 6:58pm, Sylvain Pointeau  
> wrote:
>
>> create table TEST (
>> a TEXT NOT NULL,
>> a2 TEXT NOT NULL,
>> b TEXT NOT NULL,
>> c TEXT NOT NULL
>> );
>>
>> create index IDX_TEST_1 on TEST(a,a2,b,c);
>>
>> insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
>> insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
>> insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
>> insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');
>>
>> sqlite> explain query plan select * from TEST where (a = '123' or
>> a2='1234') and b = '456' and c='PP';
>>
>> 0|0|0|SCAN TABLE TEST
>>
>> whereas
>>
>> sqlite> explain query plan select * from TEST where a = '123' and b = '456'
>> and c='PP';
>>
>> 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
>>
>> how to make this query using an index? is this possible?
> That is a good job of investigation you have done there.  What you didn't 
> take into account is that the order of columns in an index matters.
>
> Your example has known precise values for b and c, and sometimes but not 
> always knows a and a2.  So b and c should be up front in your index:
>
> create index IDX_TEST_2 on TEST(b,c,a,a2);

Quite, but there is also another consideration: Cardinality.  Now it 
might be that this data extract is not representative of the whole table 
at all, but from the looks of it, those columns b and c offer severely 
low cardinality, perhaps in the order of more than 30% of the table in 
duplications.

If this is the case, an index on c or b will amount to pretty much a 
table-scan still. You really want to hit those high-cardinality columns 
in your leading indexer (which is why Richard rightly suggested the 
double index).

I'd almost think having the less-expensive set of these:
create index IDX_TEST_1 on TEST(a);
create index IDX_TEST_2 on TEST(a2);
would produce very near the same speeds for that query if the 
cardinality of columns b and c remain low for populated data.

If however the cardinality scales up pretty well, then Richard's 
suggestion is much better, and if the cardinality will be even higher in 
b and c than in the a's, then Simon's suggestion is best.

Tool for the job etc.

Cheers,
Ryan

[sqlite] index for OR clause

[Sylvain Pointeau]

Yes, having:

create table TEST (
 a TEXT NOT NULL,
 a2 TEXT NULL,
 b TEXT NOT NULL,
 c TEXT NOT NULL
);

create index IDX_TEST_1 on TEST(c,b,a,a2);
create index IDX_TEST_2 on TEST(c,b,a);
create index IDX_TEST_3 on TEST(c,b,a2);

sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';

0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (c=? AND b=?)


but isn't the 2 indices giving best best response time?


create index IDX_TEST_2 on TEST(a,b,c);
create index IDX_TEST_3 on TEST(a2,b,c);

sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';

0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_2 (a=? AND b=? AND c=?)

0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_3 (a2=? AND b=? AND c=?)

I guess you will say that it depends on the data set?
but in a general case, I think the 2 indices version is faster, do you
agree?

[sqlite] index for OR clause

[Sylvain Pointeau]

ha yes thank you

create index IDX_TEST_2 on TEST(a,b,c);
create index IDX_TEST_3 on TEST(a2,b,c);

sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';

0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_2 (a=? AND b=? AND c=?)

0|0|0|SEARCH TABLE TEST USING INDEX IDX_TEST_3 (a2=? AND b=? AND c=?)

thank you again

On Mon, Jul 27, 2015 at 8:05 PM, Richard Hipp  wrote:

> On 7/27/15, Sylvain Pointeau  wrote:
> > Hello,
> >
> > I would like to know if this is possible to use an index in the following
> > case:
> >
> > select * from TEST where (a = '123' or a2='1234') and b = '456' and
> c='PP';
> >
> >
> > given the table:
> >
> > create table TEST (
> >  a TEXT NOT NULL,
> >  a2 TEXT NOT NULL,
> >  b TEXT NOT NULL,
> >  c TEXT NOT NULL
> > );
> >
> > create index IDX_TEST_1 on TEST(a,a2,b,c);
>
> You want the following two indexes:
>
>create index IDX_TEST_1 on TEST(a,b,c);
>create index IDX_TEST_2 on TEST(a2,b,c);
>
> >
> > insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
> > insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
> > insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
> > insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');
> >
> >
> > sqlite> explain query plan select * from TEST where (a = '123' or
> > a2='1234') and b = '456' and c='PP';
> >
> > 0|0|0|SCAN TABLE TEST
> >
> >
> > whereas
> >
> > sqlite> explain query plan select * from TEST where a = '123' and b =
> '456'
> > and c='PP';
> >
> > 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
> >
> > how to make this query using an index? is this possible?
> >
> > Best regards,
> > Sylvain
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Simon Slavin]

On 27 Jul 2015, at 7:32pm, rotaiv  wrote:

> Originally, I copied the database file from my PC to my Linux server.  I
> thought it might be a carriage return or line feed issue so I recreated the
> database from scratch on my Linux server.  Didn't change anything.

SQLite is completely platform agnostic.  A database file created on any 
platform works on any platform.  It doesn't care about endline sequences or 
where the high and low bytes go.

Simon.

[sqlite] index for OR clause

[Sylvain Pointeau]

Hello,

I would like to know if this is possible to use an index in the following
case:

select * from TEST where (a = '123' or a2='1234') and b = '456' and c='PP';


given the table:

create table TEST (
 a TEXT NOT NULL,
 a2 TEXT NOT NULL,
 b TEXT NOT NULL,
 c TEXT NOT NULL
);

create index IDX_TEST_1 on TEST(a,a2,b,c);

insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');


sqlite> explain query plan select * from TEST where (a = '123' or
a2='1234') and b = '456' and c='PP';

0|0|0|SCAN TABLE TEST


whereas

sqlite> explain query plan select * from TEST where a = '123' and b = '456'
and c='PP';

0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)

how to make this query using an index? is this possible?

Best regards,
Sylvain

[sqlite] index for OR clause

[Simon Slavin]

On 27 Jul 2015, at 7:34pm, Drago, William @ CSG - NARDA-MITEQ  wrote:

> If case is not important would adding COLLATE NOCASE to column c improve 
> performance?

Depends whether it would reduce the number of different 'chunks'.  In other 
words whether there really were any examples of the column having more than one 
of 'abd' and 'Abc' or 'ABC'.

But since people generally want non-case-sensitive searching when they search, 
it might be a good idea in any ... erm ... case.

Simon.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[R.Smith]

On 2015-07-27 05:48 PM, Marc L. Allen wrote:
> When would that specific LEFT JOIN ever do anything except return NULLs for 
> the right table?  It only accepts rows from work where fpath is null, and 
> only joins those rows where fpath = home.fpath.  Since fpath must be null, 
> home.fpath should never be equal.

TLDR: Just a quick simple explain of the left joining phenomenon in case 
anyone needed it (Sometimes the basics elude me), feel free to skip this 
post if you know how it works - thanks.


Left join is an outer join, meaning simply it iterates all rows in the 
joined table and adds all positive matches, duplicating as needed but 
never culling any rows from the originating tuple set (the LEFT table of 
the Join).

So if I ask an SQL engine (any of them) to list me the numbers from 1 to 
10 and LEFT JOIN onto that (ON) the word 'Odd' for numbers that are odd, 
it won't remove any of the original listing, it will simply add the 
fields to the lines where matches exist, and add NULL fields where no 
matches are found. A normal (Inner) join will only show positive matches 
found in both tables.

Some SQLite-flavoured SQL to point out the above:
(Note: this math works in SQLite because of how INT division is handled, 
it may not work exactly like this in other engines, but the idea is 
universal)


CREATE TABLE Nums(x INT);
CREATE TABLE Kinds(r INT, k TEXT);

WITH CN(x) AS (
SELECT 1 UNION ALL SELECT x+1 FROM CN LIMIT 10
)
INSERT INTO Nums SELECT x from CN;

INSERT INTO Kinds VALUES(1,'Odd');



-- Example 1:  This Query uses a standard inner join to match rows in
-- two tables and culls the rows from the first (LEFT) table
-- that doesn't have an entry in the other (RIGHT) table.

SELECT Nums.x, Kinds.k
   FROM Nums
   JOIN Kinds ON Kinds.r=(Nums.x-((Nums.x / 2) * 2));

   --   x  |   k
   --  | -
   --   1  |  Odd
   --   3  |  Odd
   --   5  |  Odd
   --   7  |  Odd
   --   9  |  Odd



--  Example 2:  This Query is word-for-word the exact same but uses a
-- LEFT join to match rows in two tables and add the rows
--  from the second (RIGHT) table to the first (LEFT) table
--  if a match exists, if it doesn't, then it adds NULL, but
-- it never culls the LEFT table in any way:

SELECT Nums.x, Kinds.k
FROM Nums
LEFT JOIN Kinds ON Kinds.r=(Nums.x-((Nums.x / 2) * 2));

   --   x  | k
   --  | --
   --   1  | Odd
   --   2  | Null
   --   3  | Odd
   --   4  | Null
   --   5  | Odd
   --   6  | Null
   --   7  | Odd
   --   8  | Null
   --   9  | Odd
   --  10  | Null



-- Example 3:  So, if we do not really care about whether to DISPLAY the
-- oddness of the values, but only wish to list the EVEN 
numbers
-- (aka the non-Odd numbers), I could use that LEFT JOIN and
-- check for those NULLS in ANY of the joined columns, even the
-- same that were used in the ON clause:
-- (this kind of represents what the OP's query did):

SELECT Nums.x
FROM Nums
   LEFT JOIN Kinds ON Kinds.r=(Nums.x-((Nums.x / 2) * 2))
  WHERE Kinds.r IS NULL;

   --   x
   -- 
   --   2
   --   4
   --   6
   --   8
   --  10




Hope that helps someone - Cheers,
Ryan.

[sqlite] index for OR clause

[Simon Slavin]

On 27 Jul 2015, at 6:58pm, Sylvain Pointeau  
wrote:

> create table TEST (
> a TEXT NOT NULL,
> a2 TEXT NOT NULL,
> b TEXT NOT NULL,
> c TEXT NOT NULL
> );
> 
> create index IDX_TEST_1 on TEST(a,a2,b,c);
> 
> insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
> insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
> insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
> insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');
> 
> sqlite> explain query plan select * from TEST where (a = '123' or
> a2='1234') and b = '456' and c='PP';
> 
> 0|0|0|SCAN TABLE TEST
> 
> whereas
> 
> sqlite> explain query plan select * from TEST where a = '123' and b = '456'
> and c='PP';
> 
> 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
> 
> how to make this query using an index? is this possible?

That is a good job of investigation you have done there.  What you didn't take 
into account is that the order of columns in an index matters.

Your example has known precise values for b and c, and sometimes but not always 
knows a and a2.  So b and c should be up front in your index:

create index IDX_TEST_2 on TEST(b,c,a,a2);

For real-world data you should also do an ANALYZE after putting some realistic 
data into the table.  But with only a few rows it won't make any difference.

Simon.

[sqlite] index for OR clause

[Drago, William @ CSG - NARDA-MITEQ]

If case is not important would adding COLLATE NOCASE to column c improve 
performance?

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of R.Smith
> Sent: Monday, July 27, 2015 2:27 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] index for OR clause
>
>
>
> On 2015-07-27 08:09 PM, Simon Slavin wrote:
> > On 27 Jul 2015, at 6:58pm, Sylvain Pointeau
>  wrote:
> >
> >> create table TEST (
> >> a TEXT NOT NULL,
> >> a2 TEXT NOT NULL,
> >> b TEXT NOT NULL,
> >> c TEXT NOT NULL
> >> );
> >>
> >> create index IDX_TEST_1 on TEST(a,a2,b,c);
> >>
> >> insert into TEST(a,a2,b,c) values ('123','1234','456','PP'); insert
> >> into TEST(a,a2,b,c) values ('234','2345','456','PP'); insert into
> >> TEST(a,a2,b,c) values ('456','4567','456','PP'); insert into
> >> TEST(a,a2,b,c) values ('0123','10456','678','DP');
> >>
> >> sqlite> explain query plan select * from TEST where (a = '123' or
> >> a2='1234') and b = '456' and c='PP';
> >>
> >> 0|0|0|SCAN TABLE TEST
> >>
> >> whereas
> >>
> >> sqlite> explain query plan select * from TEST where a = '123' and b
> = '456'
> >> and c='PP';
> >>
> >> 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
> >>
> >> how to make this query using an index? is this possible?
> > That is a good job of investigation you have done there.  What you
> didn't take into account is that the order of columns in an index
> matters.
> >
> > Your example has known precise values for b and c, and sometimes but
> not always knows a and a2.  So b and c should be up front in your
> index:
> >
> > create index IDX_TEST_2 on TEST(b,c,a,a2);
>
> Quite, but there is also another consideration: Cardinality.  Now it
> might be that this data extract is not representative of the whole
> table at all, but from the looks of it, those columns b and c offer
> severely low cardinality, perhaps in the order of more than 30% of the
> table in duplications.
>
> If this is the case, an index on c or b will amount to pretty much a
> table-scan still. You really want to hit those high-cardinality columns
> in your leading indexer (which is why Richard rightly suggested the
> double index).
>
> I'd almost think having the less-expensive set of these:
> create index IDX_TEST_1 on TEST(a);
> create index IDX_TEST_2 on TEST(a2);
> would produce very near the same speeds for that query if the
> cardinality of columns b and c remain low for populated data.
>
> If however the cardinality scales up pretty well, then Richard's
> suggestion is much better, and if the cardinality will be even higher
> in b and c than in the a's, then Simon's suggestion is best.
>
> Tool for the job etc.
>
> Cheers,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[R.Smith]

On 2015-07-27 04:29 PM, rotaiv wrote:
>> ?WOW! That is an amazing difference. Makes me glad that I'm OCD about
>> staying relatively current. And please ignore my previous post. I somehow
>> managed to not register that you would doing a LEFT join, an equi-join.?
>>
> I am also OCD about updates.  I installed Sqlite using yum right before I
> tried this test.  I guess CentOS release 6.6 is not using the latest
> version just yet.  I would never have believed a new version would have had
> such a dramatic difference.

It's not the older SQLite taking so much longer, it's simply that the 
file created with a newer SQLite probably contains a SCHEMA or objects 
not readily understood or able to be optimized by the older engine. if 
the file was made with the old engine it would perform perfectly well in 
both the old and new engines.

Either that, or a bug that's been fixed since then.

Have a great day,
Ryan

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Reinhard Max]

On Mon, 27 Jul 2015 at 17:48, Marc L. Allen wrote:

> When would that specific LEFT JOIN ever do anything except return 
> NULLs for the right table?  It only accepts rows from work where 
> fpath is null, and only joins those rows where fpath = home.fpath. 
> Since fpath must be null, home.fpath should never be equal.

You're assuming that "LEFT JOIN ... ON ..." behaves the same as "JOIN 
... WHERE ...", which is not the case as it would defeat the purpose 
of a left join.

sqlite> create table home (fpath text);
sqlite> create table work (fpath text);
sqlite> insert into home values ('path1');
sqlite> insert into home values ('path2');
sqlite> insert into work values ('path2');
sqlite> insert into work values ('path3');
sqlite> SELECT home.fpath
...> FROM home
...> LEFT JOIN work ON work.fpath = home.fpath
...> WHERE work.fpath IS NULL;
path1

See, it returns the rows of home.fpath that don't have a match in 
work.fpath.

cu
Reinhard

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Reinhard Max]

On Mon, 27 Jul 2015 at 17:34, Marc L. Allen wrote:

> Sorry.. confused does sqlite allow comparison between NULLs?

No.

> LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL
>
> Should never match anything... it checks to see if work.fpath IS 
> NULL and if the same work.fpath is equal to something.

It is a LEFT (OUTER) join, which NULLs the columns of the right thable 
in the result set for rows in the left table that don't have a match.

cu
Reinhard

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Keith Medcalf]

Or even better:

select fpath
  from home
 where not exists (select 1 from work where work.fpath=home.fpath and 
work.ftype=home.ftype)
   and ftype = 'f'

with a unique index on home (ftype, fpath) and a unique index on work (ftype, 
fpath) of course.

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Monday, 27 July, 2015 10:04
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort
> after 40+ minutes on other machines
> 
> On 7/27/2015 9:58 AM, Simon Slavin wrote:
> > So you're checking both to see that [work.fpath = home.fpath ] and to
> see that [work.fpath IS NULL].  This looks weird to me.
> 
> That's a common technique with LEFT JOIN - it's selecting home records
> that lack a corresponding work record. In other words, it's equivalent to
> 
> SELECT fpath
> FROM home
> WHERE fpath NOT IN (SELECT fpath FROM work)
> AND home.ftype = 'f?;
> 
> --
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Reinhard Max]

On Mon, 27 Jul 2015 at 16:24, rotaiv wrote:

> I am more than open to a more logical and/or more efficient query if 
> anyone has suggestions.

One alternative was posted by Igor, but it might end up as the same 
query plan after the optimizer has done its thing (EXPLAIN will tell 
you).

Adding indexes to the columns you are JOINing on might also help, if 
you haven't done already, but you'll have to test whether index 
creation time pays off in saved query time.

cu
Reinhard

[sqlite] fts5 module does not build from the 3.8.11 release's source tarball

[Hinrichsen, John]

This was the error I got:

fts5_main.c:30: error: redefinition of typedef 'Fts5Global'

On Mon, Jul 27, 2015 at 4:00 PM, Dan Kennedy  wrote:

> On 07/28/2015 02:55 AM, Hinrichsen, John wrote:
>
>> Hi,
>>
>> I was not able to get the fts5 module to build from the versioned source
>> tarball for this release (
>> http://www.sqlite.org/2015/sqlite-src-3081100.zip
>> ).
>>
>
> Which step failed?
>
>
>
>
>> I was able to 'make fts5.c' following the instructions that reference the
>> "trunk" tarball.
>>
>> Regards,
>> John Hinrichsen
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee, you should not 
disseminate, distribute, alter or copy this e-mail. Please notify the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.

[sqlite] fts5 module does not build from the 3.8.11 release's source tarball

[Hinrichsen, John]

Hi,

I was not able to get the fts5 module to build from the versioned source
tarball for this release (http://www.sqlite.org/2015/sqlite-src-3081100.zip
).

I was able to 'make fts5.c' following the instructions that reference the
"trunk" tarball.

Regards,
John Hinrichsen

-- 

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee, you should not 
disseminate, distribute, alter or copy this e-mail. Please notify the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Simon Slavin]

On 27 Jul 2015, at 3:04pm, Igor Tandetnik  wrote:

> That's a common technique with LEFT JOIN

Thanks, Igor.


On 27 Jul 2015, at 1:44pm, rotaiv  wrote:

> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath IS NULL
> AND home.ftype = 'f?;

Can you verify that you have an index on work.fpath ?  16 seconds is still a 
little slower than it should be.

Simon.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Tim Streater]

On 27 Jul 2015 at 13:44, rotaiv  wrote:

> Where is the resource bottleneck that causes a simple query to never
> complete after 40 minutes
>
> I am using sqlite to synchronize files on my home PC and my work PC.  I
> created a file listing of each computer and imported into separate tables.

And how are you doing this?

> Then I used the following query to locate missing files.  The ?ftype? is
> simply ?F? for file or ?D? for directory.

Have you written a program to do this or are you using the sqlite3 shell 
program? You're presumably working with one database that you move between 
machines so that you can import the listings created on each machine. Is this 
correct? What is the schema for your database?

--
Cheers  --  Tim

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Andy Ling]

> > That's a common technique with LEFT JOIN
> 
> Thanks, Igor.
>

A bit MySQL specific, but shows some options

http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Simon Slavin]

On 27 Jul 2015, at 1:44pm, rotaiv  wrote:

> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath IS NULL
> AND home.ftype = 'f?;

Are you sure you got this right ?  It uses work.fpath twice:

> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath IS NULL

So you're checking both to see that [work.fpath = home.fpath ] and to see that 
[work.fpath IS NULL].  This looks weird to me.

Simon.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[rotaiv]

> It's not the older SQLite taking so much longer, it's simply that the file
> created with a newer SQLite probably contains a SCHEMA or objects not
> readily understood or able to be optimized by the older engine. if the file
> was made with the old engine it would perform perfectly well in both the
> old and new engines.
>

Originally, I copied the database file from my PC to my Linux server.  I
thought it might be a carriage return or line feed issue so I recreated the
database from scratch on my Linux server.  Didn't change anything.


> Either that, or a bug that's been fixed since then.
>

I'm thinking a bug or they significantly improved the selection statement
code!

[sqlite] index for OR clause

[Richard Hipp]

On 7/27/15, Sylvain Pointeau  wrote:
> Hello,
>
> I would like to know if this is possible to use an index in the following
> case:
>
> select * from TEST where (a = '123' or a2='1234') and b = '456' and c='PP';
>
>
> given the table:
>
> create table TEST (
>  a TEXT NOT NULL,
>  a2 TEXT NOT NULL,
>  b TEXT NOT NULL,
>  c TEXT NOT NULL
> );
>
> create index IDX_TEST_1 on TEST(a,a2,b,c);

You want the following two indexes:

   create index IDX_TEST_1 on TEST(a,b,c);
   create index IDX_TEST_2 on TEST(a2,b,c);

>
> insert into TEST(a,a2,b,c) values ('123','1234','456','PP');
> insert into TEST(a,a2,b,c) values ('234','2345','456','PP');
> insert into TEST(a,a2,b,c) values ('456','4567','456','PP');
> insert into TEST(a,a2,b,c) values ('0123','10456','678','DP');
>
>
> sqlite> explain query plan select * from TEST where (a = '123' or
> a2='1234') and b = '456' and c='PP';
>
> 0|0|0|SCAN TABLE TEST
>
>
> whereas
>
> sqlite> explain query plan select * from TEST where a = '123' and b = '456'
> and c='PP';
>
> 0|0|0|SEARCH TABLE TEST USING COVERING INDEX IDX_TEST_1 (a=?)
>
> how to make this query using an index? is this possible?
>
> Best regards,
> Sylvain
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Scott Robison]

On Mon, Jul 27, 2015 at 1:00 PM, Simon Slavin  wrote:

>
> On 27 Jul 2015, at 7:32pm, rotaiv  wrote:
>
> > Originally, I copied the database file from my PC to my Linux server.  I
> > thought it might be a carriage return or line feed issue so I recreated
> the
> > database from scratch on my Linux server.  Didn't change anything.
>
> SQLite is completely platform agnostic.  A database file created on any
> platform works on any platform.  It doesn't care about endline sequences or
> where the high and low bytes go.
>

This assumes the means of copying the file didn't result in CRLF
translation of some sort. Still, if the copy process did modify the file in
some way, I suspect the problem would have been a corrupt file error, not a
slow running query.
-- 
Scott Robison

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Paolo Bolzoni]

Dear list,

I have a somewhat large sqlite3 db (about 120GB) and I need to create
some indexes on it, but I get  "database or disk is full" while I have
still about 300GB of free disk space.

However the /tmp directory is only 5GB so I suspect that sqlite3 has
not enough space there. I found the temp_store_directory, but it is
deprecated. So I was wondering, what is the suggested alternative?
Why it is deprecated?


Your faithfully,
Paolo

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Marc L. Allen]

Oh, jeeze.  Wow.

And I know all that... I cleverly managed to ignore the "WHERE" and think it 
was an AND because it was one the same line.

Duh.

Thanks.


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Reinhard Max
Sent: Monday, July 27, 2015 12:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort after 
40+ minutes on other machines


On Mon, 27 Jul 2015 at 17:48, Marc L. Allen wrote:

> When would that specific LEFT JOIN ever do anything except return 
> NULLs for the right table?  It only accepts rows from work where fpath 
> is null, and only joins those rows where fpath = home.fpath.
> Since fpath must be null, home.fpath should never be equal.

You're assuming that "LEFT JOIN ... ON ..." behaves the same as "JOIN ... WHERE 
...", which is not the case as it would defeat the purpose of a left join.

sqlite> create table home (fpath text);
sqlite> create table work (fpath text);
sqlite> insert into home values ('path1'); insert into home values 
sqlite> ('path2'); insert into work values ('path2'); insert into work 
sqlite> values ('path3'); SELECT home.fpath
...> FROM home
...> LEFT JOIN work ON work.fpath = home.fpath
...> WHERE work.fpath IS NULL;
path1

See, it returns the rows of home.fpath that don't have a match in work.fpath.

cu
Reinhard
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] CVE-2015-3659

[Reinhard Max]

Hi,

as the maintainer of the SQLite RPMs on SUSE, I am currently faced 
with a bug report concerning CVE-2015-3659[0].

>From the CVE's description it looks to me like the bug was in Apple's 
authorizer callback rather than SQLite's authorization mechanism, can 
anyone confirm this?

Thanks,
Reinhard

[0] http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2015-3659

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Marc L. Allen]

When would that specific LEFT JOIN ever do anything except return NULLs for the 
right table?  It only accepts rows from work where fpath is null, and only 
joins those rows where fpath = home.fpath.  Since fpath must be null, 
home.fpath should never be equal.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Reinhard Max
Sent: Monday, July 27, 2015 11:40 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort after 
40+ minutes on other machines



On Mon, 27 Jul 2015 at 17:34, Marc L. Allen wrote:

> Sorry.. confused does sqlite allow comparison between NULLs?

No.

> LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL
>
> Should never match anything... it checks to see if work.fpath IS NULL 
> and if the same work.fpath is equal to something.

It is a LEFT (OUTER) join, which NULLs the columns of the right thable in the 
result set for rows in the left table that don't have a match.

cu
Reinhard
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Marc L. Allen]

Sorry.. confused does sqlite allow comparison between NULLs?

I mean...

LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL

Should never match anything... it checks to see if work.fpath IS NULL and if 
the same work.fpath is equal to something.  

I get the weird feeling that I'm missing something stupid, so massive apologies 
in advance.  Maybe I came in late and this is some clever way of using an index?

Marc

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, July 27, 2015 10:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort after 
40+ minutes on other machines


On 27 Jul 2015, at 3:04pm, Igor Tandetnik  wrote:

> That's a common technique with LEFT JOIN

Thanks, Igor.


On 27 Jul 2015, at 1:44pm, rotaiv  wrote:

> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL AND 
> home.ftype = 'f?;

Can you verify that you have an index on work.fpath ?  16 seconds is still a 
little slower than it should be.

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



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[rotaiv]

> ?WOW! That is an amazing difference. Makes me glad that I'm OCD about
> staying relatively current. And please ignore my previous post. I somehow
> managed to not register that you would doing a LEFT join, an equi-join.?
>

I am also OCD about updates.  I installed Sqlite using yum right before I
tried this test.  I guess CentOS release 6.6 is not using the latest
version just yet.  I would never have believed a new version would have had
such a dramatic difference.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[rotaiv]

> Also, I agree with Simon that the query itself, and it's use of NULL, looks
> weird. And I don't see where it could find any matches because the "ON
> work.fpath = home.fpath" should never match if either fpath is NULL.
>
>
I confess, I understand basic SQL but it is not one of my strengths at
all.  I found an example of this query via Google, tried it, and it
worked.  I manually manipulated the data source to generate matches based
on the criteria I was looking for.  The results were completely as
expected.

I am more than open to a more logical and/or more efficient query if anyone
has suggestions.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[rotaiv]

Problem solved.  I updated the version on the server and it completes in 16
seconds.

To answer your question, I am using the find command to created a delimited
text file.  I copy that file to my home PC then importing that file into a
sqlite database table.  I use the same process to create a text file on my
home PC then import that as well.   The tables are very simple.  Just
filename, type, date, time and epoch seconds (easy comparison).

On Mon, Jul 27, 2015 at 10:16 AM, Tim Streater  wrote:

> On 27 Jul 2015 at 13:44, rotaiv  wrote:
>
> > Where is the resource bottleneck that causes a simple query to never
> > complete after 40 minutes
> >
> > I am using sqlite to synchronize files on my home PC and my work PC.  I
> > created a file listing of each computer and imported into separate
> tables.
>
> And how are you doing this?
>
> > Then I used the following query to locate missing files.  The ?ftype? is
> > simply ?F? for file or ?D? for directory.
>
> Have you written a program to do this or are you using the sqlite3 shell
> program? You're presumably working with one database that you move between
> machines so that you can import the listings created on each machine. Is
> this correct? What is the schema for your database?
>
> --
> Cheers  --  Tim
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[rotaiv]

Thank for reminding me to check the obvious.  The server was using version
3.6.20.  I manually upgraded to version 3.8.10.2 and the query completed in
16 seconds.  Still not as fast as my home PC but given the age of the
hardware, this is understandable.  I will take 16 seconds as opposed to 40+
minutes any day!

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[rotaiv]

That is correct - I am looking for records that are empty.  I tried this
version of the SQL query as well and during my testing, the first example
seemed to be more efficient.

Also, I have version 3.8.10.2 on my home PC and version 3.6.20 on my server
at work. I am going to update the server version and see if that makes a
difference.

On Mon, Jul 27, 2015 at 10:04 AM, Igor Tandetnik  wrote:

> On 7/27/2015 9:58 AM, Simon Slavin wrote:
>
>> So you're checking both to see that [work.fpath = home.fpath ] and to see
>> that [work.fpath IS NULL].  This looks weird to me.
>>
>
> That's a common technique with LEFT JOIN - it's selecting home records
> that lack a corresponding work record. In other words, it's equivalent to
>
> SELECT fpath
> FROM home
> WHERE fpath NOT IN (SELECT fpath FROM work)
> AND home.ftype = 'f?;
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Igor Tandetnik]

On 7/27/2015 9:58 AM, Simon Slavin wrote:
> So you're checking both to see that [work.fpath = home.fpath ] and to see 
> that [work.fpath IS NULL].  This looks weird to me.

That's a common technique with LEFT JOIN - it's selecting home records 
that lack a corresponding work record. In other words, it's equivalent to

SELECT fpath
FROM home
WHERE fpath NOT IN (SELECT fpath FROM work)
AND home.ftype = 'f?;

-- 
Igor Tandetnik

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Richard Hipp]

On 7/27/15, rotaiv  wrote:
>
> My question is ...  What is about my machine at home
> that allows Sqlite to query 2 million files so quickly?
>

Probably a newer version of SQLite.  You didn't tell us the version
numbers for SQLite on the two machines.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[John McKown]

On Mon, Jul 27, 2015 at 9:21 AM, rotaiv  wrote:

> Problem solved.  I updated the version on the server and it completes in 16
> seconds.
>

?WOW! That is an amazing difference. Makes me glad that I'm OCD about
staying relatively current. And please ignore my previous post. I somehow
managed to not register that you would doing a LEFT join, an equi-join.?



>
> To answer your question, I am using the find command to created a delimited
> text file.  I copy that file to my home PC then importing that file into a
> sqlite database table.  I use the same process to create a text file on my
> home PC then import that as well.   The tables are very simple.  Just
> filename, type, date, time and epoch seconds (easy comparison).
>
> --

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[John McKown]

On Mon, Jul 27, 2015 at 8:42 AM, Richard Hipp  wrote:

> On 7/27/15, rotaiv  wrote:
> >
> > My question is ...  What is about my machine at home
> > that allows Sqlite to query 2 million files so quickly?
> >
>

?I missed the initial post, somehow.?

?Could there be an index on something on the home machine which does not
exist on the work machine? Can you do an EXPLAIN on both to see what the
query plan is??


?Perhaps another major reason would be if the home machine hardware is
faster than the other machine?. An extreme example: at work my machine is a
very old 2 GiB Pentium system running at 1.8 GiHz with a normal 5400 RPM
hard drive. My home machine is a 16 GiB, 3.7 GiHz Xeon with an 320 GiB SSD
+ 2 TiB 7200 RPM HD. Need I mention how much faster my home machine is? The
software configuration is almost identical: Fedora 22 x86_64.

I also wonder if the work PC is running other "server" type applications
which use up CPU and RAM. Also, is the work PC running 64 bit or 32 bit
Linux? How about Windows at home (I assume 64 bit here)?

But, in any case, 40 minutes seems really poor performance, unless there is
some hardware issue.

Also, I agree with Simon that the query itself, and it's use of NULL, looks
weird. And I don't see where it could find any matches because the "ON
work.fpath = home.fpath" should never match if either fpath is NULL.



>
> Probably a newer version of SQLite.  You didn't tell us the version
> numbers for SQLite on the two machines.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[rotaiv]

Where is the resource bottleneck that causes a simple query to never
complete after 40 minutes

I am using sqlite to synchronize files on my home PC and my work PC.  I
created a file listing of each computer and imported into separate tables.
Then I used the following query to locate missing files.  The ?ftype? is
simply ?F? for file or ?D? for directory.

SELECT home.fpath
FROM home
LEFT JOIN work ON work.fpath = home.fpath
WHERE work.fpath IS NULL
AND home.ftype = 'f?;


I started with a small dataset as proof of concept and it worked as
expected.  I have used MySQL in the past for various projects but this is
my first time using Sqlite.  I decided to stress test the query and see how
it performs with large datasets.

I create a file listing of my archive drive that has over 2 million files.
When I ran the query on my desktop at home it completed in about 8
seconds.  When I tried the same query on my sever at work, I aborted after
40 minutes.  I tried it on a second machine with the same results.  I tried
a much smaller dataset and it worked as expected, just much, much slower.

To be fair, I just replaced my computer at home with a custom built
machine.  It has lots of RAM and a modern, fast quad core processor.
However, the server I tried at work has the same amount of RAM and an older
8 core processor.  I expected there might be a noticeable difference but 8
seconds compared to 40+ minutes was a surprise.

Other caveats are my PC at home is Windows 8.1 with Cygwin + Sqlite.  My
server at work is Redhat Linux + Sqlite via yum.

My question is where is the bottleneck?  What is about my machine at home
that allows Sqlite to query 2 million files so quickly?

I am happy to provide specific hardware and software specifications if that
is helpful.

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Richard Hipp]

On 7/27/15, Paolo Bolzoni  wrote:
> Dear list,
>
> I have a somewhat large sqlite3 db (about 120GB) and I need to create
> some indexes on it, but I get  "database or disk is full" while I have
> still about 300GB of free disk space.
>
> However the /tmp directory is only 5GB so I suspect that sqlite3 has
> not enough space there. I found the temp_store_directory, but it is
> deprecated. So I was wondering, what is the suggested alternative?

Set the TEMP environment variable to the location of your temporary
storage area.

> Why it is deprecated?

The temp_store_directory pragma is not threadsafe and can cause
incorrect results if it is changed in the middle of a query.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] CVE-2015-3659

[Richard Hipp]

On 7/27/15, Reinhard Max  wrote:
> Hi,
>
> as the maintainer of the SQLite RPMs on SUSE, I am currently faced
> with a bug report concerning CVE-2015-3659[0].
>
> From the CVE's description it looks to me like the bug was in Apple's
> authorizer callback rather than SQLite's authorization mechanism, can
> anyone confirm this?
>

Your email was the first time we (the SQLite developers) have heard of
this issue.  We have no additional information.

It sounds, as you say, like Apple's callback was misimplemented and
that this is not a fault within SQLite.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Auto: CVE-2015-3659

[bwill...@seacorp.com]

I am out of the office until 8/3/2015
If you have an urgent issue, please contact cabatecola at seacorp.com
Thanks,
Brian Willner