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

[Simon Slavin]

On 28 Jul 2015, at 8:25pm, rotaiv  wrote:

> From what I read, it was recommended to create indexes for any field used
> in the SELECT statement.  Is that correct?  Give my original query:
> 
> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath
> WHERE work.fpath IS NULL
> AND home.ftype = 'f';

I really must get around to writing a web page on this subject.  It's almost a 
decade since I decided to.

Ignore the idea of creating indexes for certain fields.  It will speed things 
up slightly but nowhere near as much as is possible.  A better approach is to 
look at each SELECT command (or WHERE clause) and to create an index ideally 
suited to speeding up that one thing.  Something to bear in mind is that each 
search or sort operation can only usefully use one index.  So for this SELECT:

SELECT * FROM myTable WHERE a = '123' ORDER BY b

there's no point in creating both these indexes

CREATE INDEX m_a ON myTable (a);
CREATE INDEX m_b ON myTable (b);

Because once SQL has used m_a to pick the right rows it is no longer looking at 
the whole table so it can't use index m_b to do the sorting.  However, this 
index

CREATE INDEX m_a_b ON myTable (a,b);

can be used to do both operations: it allows SQL to find the right rows in the 
right order, just by selecting a section of the index.  It is the ideal index 
for that SELECT.

Simon.

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

[Simon Slavin]

On 28 Jul 2015, at 7:55pm, rotaiv  wrote:

> That is the machine.  Originally. it was taking 40+ minutes.  I upgraded to
> the latest version and it decreased to 16 seconds.  With indexes, 5
> seconds. :-D

Ah, okay I get it.  In that case the index would probably have given you a time 
of 5 seconds even without the update.  Creating good indexes is key to fast 
searching times in SQL.

Simon.

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

[Simon Slavin]

On 28 Jul 2015, at 7:43pm, rotaiv  wrote:

> I create indexes and my original query time dropped from 16 seconds to this:
> 
> real  0m5.928s
> user  0m5.361s
> sys   0m0.565s

Is it worth checking on the machine you originally reported 40+ minutes for ?  
That one should be considerably faster.

Simon.

[sqlite] Numerics bigger than 32-bit and sqlite as 64-bit TCL extension

[Rafał Ponikwia]

On 28.07.2015 18:52, Richard Hipp wrote:
> I get the correct answer (2147483647 2147483649 2147483649) when I try
> this on 64-bit Ubuntu.
Yes, I've just tested it on Debian x64 and it works too, but I noticed 
that $l3 is stored as int, not as wideInt like on Windows:
% ::tcl::unsupported::representation $l3
value is a int with a refcount of 2, object pointer at 0x1776e40, 
internal representation 0x8001:(nil), string representation "2147483649"

[sqlite] Numerics bigger than 32-bit and sqlite as 64-bit TCL extension

[Rafał Ponikwia]

Hi,
I'm using sqlite3 TCL extension from Teapot (version 64-bit). When I try 
to insert number bigger than signed 32-bit it inserts wrong number to 
database.
Same code run with 32-bit version works fine.

Example in TCL console illustrating this issue:

% info patchlevel
8.6.4
% package require sqlite3
3.8.10.2
% set l1 [expr {2 ** 31 - 1}]
2147483647
% set l2 2147483649
2147483649
% set l3 [expr {$l1 + 2}]
2147483649
% ::tcl::unsupported::representation $l1
value is a int with a refcount of 3, object pointer at 006CEB10, 
internal representation 7FFF:, string 
representation "2147483647"
% ::tcl::unsupported::representation $l2
value is a pure string with a refcount of 4, object pointer at 
006CE930, string representation "2147483649"
% ::tcl::unsupported::representation $l3
value is a wideInt with a refcount of 2, object pointer at 
006CF2F0, internal representation 
8001:, string representation "2147483649"
% sqlite3 db C:/tmp/test.sqlite
% db eval {
 CREATE TABLE test (num NUMERIC);
 INSERT INTO test (num) VALUES ($l1), ($l2), ($l3);
 SELECT * FROM test;
}
2147483647 2147483649 -2147483647

Regards,
RP.

[sqlite] SQLite 3.8.11 don't compile in VS9 for CE6/CE7 RELEASE target

[Christian Nols]

Updating to sqlite3.c 3.8.11

The RELEASE target for CE6.0/CE7.0 don't build (Internal Compiler Error).

1>Internal Compiler Error in c:\Program Files (x86)\Microsoft Visual Studio 
9.0\VC\ce\bin\x86_arm\cl.exe.  You will be prompted to send an error report to 
Microsoft later.
1>Project : error PRJ0002 : Error result 1 returned from 'c:\Program Files 
(x86)\Microsoft Visual Studio 9.0\VC\ce\bin\x86_arm\cl.exe'.

Commenting
#include 
seems to work around it

C:\Program Files (x86)\Windows CE Tools\wce600\Windows CE 6 
SDK\include\ARMV4I\cmnintrin.h
C:\Program Files (x86)\Windows CE 
Tools\SDKs\WT41N0c70PSDK\Include\Armv4i\cmnintrin.h

Previous versions were OK
CE5.0/WM6 are OK
Christian Nols
Software Engineer

christian.nols at mcl-technologies.com
Tel : + 32 2 724 35 19

MCL
NYSDAM Building
avenue Reine Astrid 92, 3rd floor
1310 La Hulpe- Belgium
BCE registered number 2.087.817.310
Tel : + 32 2 724 35 00 - Fax : + 32 2 724 35 04
www.mcl-collection.com

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

[rotaiv]

Not quite.  The older version brought it down to 8 seconds (as compared to
5 seconds) but still a whole lot better than 40+ minutes.

It is very interesting to see an index can make such a remarkable
difference in that particular scenario.  This is my first time creating
indexes but I will definitely be doing that more in the future.

>From what I read, it was recommended to create indexes for any field used
in the SELECT statement.  Is that correct?  Give my original query:

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

What indexes should I create?  Seems like I only need an index on
home.fpath  and work.fpath but I wanted to make sure.

On Tue, Jul 28, 2015 at 3:13 PM, Simon Slavin  wrote:

>
> On 28 Jul 2015, at 7:55pm, rotaiv  wrote:
>
> > That is the machine.  Originally. it was taking 40+ minutes.  I upgraded
> to
> > the latest version and it decreased to 16 seconds.  With indexes, 5
> > seconds. :-D
>
> Ah, okay I get it.  In that case the index would probably have given you a
> time of 5 seconds even without the update.  Creating good indexes is key to
> fast searching times in SQL.
>
> Simon.
> ___
> 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]

That is the machine.  Originally. it was taking 40+ minutes.  I upgraded to
the latest version and it decreased to 16 seconds.  With indexes, 5
seconds. :-D

On Tue, Jul 28, 2015 at 2:53 PM, Simon Slavin  wrote:

>
> On 28 Jul 2015, at 7:43pm, rotaiv  wrote:
>
> > I create indexes and my original query time dropped from 16 seconds to
> this:
> >
> > real  0m5.928s
> > user  0m5.361s
> > sys   0m0.565s
>
> Is it worth checking on the machine you originally reported 40+ minutes
> for ?  That one should be considerably faster.
>
> Simon.
> ___
> 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]

I create indexes and my original query time dropped from 16 seconds to this:

real  0m5.928s
user  0m5.361s
sys   0m0.565s

However, when I use the query you suggested, it was a little slower:

real  0m9.827s
user  0m8.952s
sys   0m0.873s


On Mon, Jul 27, 2015 at 5:38 PM, Keith Medcalf  wrote:

>
> 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Numerics bigger than 32-bit and sqlite as 64-bit TCL extension

[Richard Hipp]

On 7/28/15, Rafa? Ponikwia  wrote:
> Hi,
> I'm using sqlite3 TCL extension from Teapot (version 64-bit). When I try
> to insert number bigger than signed 32-bit it inserts wrong number to
> database.
>> % db eval {
>  CREATE TABLE test (num NUMERIC);
>  INSERT INTO test (num) VALUES ($l1), ($l2), ($l3);
>  SELECT * FROM test;
> }
> 2147483647 2147483649 -2147483647
>

I get the correct answer (2147483647 2147483649 2147483649) when I try
this on 64-bit Ubuntu.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] How to use gdb for .test files in SQLite?

[Sairam Gaddam]

I was testing a custom SQLite with the test files and one of the
file(tclsqlite.test) gave a segmentation fault, when I used Valgrind, it
didn't give any stack trace but instead gave the following error.

==5697==
==5697== Process terminating with default action of signal 11 (SIGSEGV):
dumping core
==5697==  Access not within mapped region at address 0xFFE767F58
==5697==at 0x4A1CDC: sqlite3VdbeExec (stdio2.h:104)
==5697==  If you believe this happened as a result of a stack
==5697==  overflow in your program's main thread (unlikely but
==5697==  possible), you can try to increase the size of the
==5697==  main thread stack using the --main-stacksize= flag.
==5697==  The main thread stack size used in this run was 8388608.

Can anyone kindly tell what does above error mean?

I was not able to find the position of the segmentation fault, will gdb
help in this case?
Can anyone kindly tell how to use gdb for .test files in order to find
origin of the error?

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

[Simon Slavin]

On 28 Jul 2015, at 9:55am, Paolo Bolzoni  
wrote:

> From the C interface, the way to decide the directory is setting the
> value of sqlite3_temp_directory char pointer. As explained here:
> https://www.sqlite.org/c3ref/temp_directory.html
> This is also the first place sqlite3 checks.

The documentation is clear:

"Applications are strongly discouraged from using this global variable. It is 
required to set a temporary folder on Windows Runtime (WinRT). But for all 
other platforms, it is highly recommended that applications neither read nor 
write this variable. This global variable is a relic that exists for backwards 
compatibility of legacy applications and should be avoided in new projects."

Also, sqlite3_temp_directory is accessed by the VFS level, by choice of the 
VFS.  You might someday switch to another VFS which ignores it.

> From anywhere else sqlite3 checks the value of those env variables:
> SQLITE_TMPDIR, TMPDIR, TMP, TEMP, USERPROFILE.

All uses of SQLite are 'the C interface'.  That's all SQLite is: C code.  The C 
code checks the above places, depending on which VFS you're using, which 
usually comes down to which OS you're using.

The C code checks whatever environment variable the OS expects a user to use to 
set a default location for their temporary files.  (It also checks a lot of 
other places, but it will definitely check whatever the documentation for the 
OS says is the correct environment variable.)  Generally speaking you should 
let the computer's user or administrator set this variable, since they know 
which drives they have attached to the computer, and which one they want 
temporary files on. You're just a programmer of one application, running on a 
computer you've never seen.

The exceptions are for embedded controllers, where the programmer is the 
administrator, and that on all platforms SQLite can create huge temporary files 
and some systems don't have that much space available for temporary files.

Simon.

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

[Simon Slavin]

On 28 Jul 2015, at 10:49am, Paolo Bolzoni  
wrote:

> I guess it is not really a problem, but it means that a part of the
> deprecated pragma there is no fully portable way?

There cannot be a fully portable way, because path specifications are not 
portable.  For instance, "C:\temp" means nothing to a Unix computer because it 
does not name drives after letters of the alphabet.

Simon.

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

[Paolo Bolzoni]

I guess it is not really a problem, but it means that a part of the
deprecated pragma there is no fully portable way?

On Tue, Jul 28, 2015 at 10:56 AM, Clemens Ladisch  wrote:
> Simon Slavin wrote:
>> both platforms use whatever the expected variable name was for that OS.
>
> Unix:
> 1. #pragma temp_store_directory
> 2. getenv("SQLITE_TMPDIR")
> 3. getenv("TMPDIR")
> 4. /var/tmp
> 5. /usr/tmp
> 6. /tmp
>
> Cygwin:
> 1. #pragma temp_store_directory
> 2. getenv("SQLITE_TMPDIR")
> 3. getenv("TMPDIR")
> 4. getenv("TMP")
> 5. getenv("TEMP")
> 6. getenv("USERPROFILE")
> 7. /var/tmp
> 8. /usr/tmp
> 9. /tmp
>
> Windows:
> 1. #pragma temp_store_directory
> 2. GetTempPath(), which is documented to return:
>a. getenv("TMP")
>b. getenv("TEMP")
>c. getenv("USERPROFILE")
>d. the Windows directory
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

Simon Slavin wrote:
> both platforms use whatever the expected variable name was for that OS.

Unix:
1. #pragma temp_store_directory
2. getenv("SQLITE_TMPDIR")
3. getenv("TMPDIR")
4. /var/tmp
5. /usr/tmp
6. /tmp

Cygwin:
1. #pragma temp_store_directory
2. getenv("SQLITE_TMPDIR")
3. getenv("TMPDIR")
4. getenv("TMP")
5. getenv("TEMP")
6. getenv("USERPROFILE")
7. /var/tmp
8. /usr/tmp
9. /tmp

Windows:
1. #pragma temp_store_directory
2. GetTempPath(), which is documented to return:
   a. getenv("TMP")
   b. getenv("TEMP")
   c. getenv("USERPROFILE")
   d. the Windows directory


Regards,
Clemens

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

[Paolo Bolzoni]

So, just be sure we are in the same page:

>From the C interface, the way to decide the directory is setting the
value of sqlite3_temp_directory char pointer. As explained here:
https://www.sqlite.org/c3ref/temp_directory.html
This is also the first place sqlite3 checks.

>From anywhere else sqlite3 checks the value of those env variables:
SQLITE_TMPDIR, TMPDIR, TMP, TEMP, USERPROFILE. And if everything
fails, sqlite3 goes in the standard system location for temporary
files (e.g., /tmp in Linux).
It is the case?

I agree with Zsb?n Ambrus that it should be documented.

On Mon, Jul 27, 2015 at 11:30 PM, Simon Slavin  wrote:
>
>> 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dan Kennedy]

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] 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