Re: [sqlite] random rows

[Jay Kreibich]

I’m not entirely sure your solution will have an even distribution.  It depends 
a lot on how many times random() is called (once per row vs once per sort 
operation), and how the sort algorithm works.  I might do this instead:

SELECT * FROM (SELECT random(), t.* FROM t) ORDER BY 1 LIMIT 200;

As this makes sure random() is only called once per row.  I’m pretty sure this 
is actually equivalent to yours, but it makes things a bit more explicit.



If you only needed one, I’d do something like:

SELECT * FROM table ORDER BY rowid LIMIT 1 OFFSET abs(random()) % (SELECT 
count(*) FROM table);

Or even just call that multiple times if you need a moderate number.  The ORDER 
BY should be free, but it could also be eliminated.

  -j



> On May 31, 2018, at 7:12 PM, Torsten Curdt  wrote:
> 
> I need to get some random rows from a large(ish) table.
> 
> The following seems to be the most straight forward - but not the fastest.
> 
>  SELECT * FROM table ORDER BY random() limit 200
> 
> Is there a faster/better approach?
> 
> cheers,
> Torsten
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] random rows

[Abroży Nieprzełoży]

hmm... i think:

begin transaction;
query 'select count(*) from tab' to get total number of records, name it N
randomly select 200 integers from the range 0 to N-1
for each number query 'select * from tab limit 1 offset ?' with '?'
bound to selected number
end transaction;



2018-06-01 2:12 GMT+02:00, Torsten Curdt :
> I need to get some random rows from a large(ish) table.
>
> The following seems to be the most straight forward - but not the fastest.
>
>   SELECT * FROM table ORDER BY random() limit 200
>
> Is there a faster/better approach?
>
> cheers,
> Torsten
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Roger Binns]

On 31/05/18 10:15, Richard Hipp wrote:
> Size is still important.  But having useful features is important too.
> I'm continuing to work to find the right balance between these
> competing goals.

A pattern used in other projects is to have standard downloads, as well
as custom ones.  With the latter you can include or exclude additional
components and features.  You can already do this with SQLite, but it
requires several more command line tools, programming languages, and
comprehensive reading of the documentation.

Perhaps a custom download web page that gives you some presets
(smallest, default, everything) or lets you choose your own settings.
It would then produce known good source files, and users would be happy.

Here is an example page for a Javascript project:

  https://jqueryui.com/download/

On the balance side, STAT4 is a good example.  I think it would benefit
the majority of SQLite users if it was enabled by default.  But making
only that change could change query plans for existing users.  (Many
users also don't compile SQLite itself - they get binaries from the
platform, or language bindings.)

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] random rows

[Torsten Curdt]

I need to get some random rows from a large(ish) table.

The following seems to be the most straight forward - but not the fastest.

  SELECT * FROM table ORDER BY random() limit 200

Is there a faster/better approach?

cheers,
Torsten
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create Index after Data Populated

[Simon Slavin]

On 31 May 2018, at 8:59pm, Andrew Stewart  wrote:

>I have a database that is populated with data (and more being 
> added) and I have realized that I need an extra index to speed up access.  
> How long should it take to update the Index.  There are about 1billion rows 
> in the database

Depends on what data is in the columns you're indexing.  Fixed-length numeric 
data can be fast.  Variable-length text of BLOB data can take longer.  But it 
depends more on the medium the database is stored on.  If that database is 
stored on (actual spinning rust) hard drives, the limiting speed is how fast 
the drive is turning, and SQLite will be spending most of its time waiting for 
data-exchange with the hard disk.

Could be an hour.  Could be ten hours or more.  Whichever it is, I would 
declare "down for maintenance" and do it overnight or on a weekend.

Do think carefully about the index you're making.  Does it satisfy a particular 
WHERE clause, with or without an ORDER BY ?  Would it be better to make it a 
covering index ?

Make sure that either you're working on a copy or have taken a backup, of 
course.

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

Re: [sqlite] Size of the SQLite library

[Keith Medcalf]

>On Thursday, 31 May, 2018 10:19, Dominique Devienne said:

>Given where the conversation is going, let me point out that many do
>not care one bit about the lib's size :)

>I'd much rather have an SQLite with tons of features, than forego
>those in the name saving a few bytes, to save a few bucks on the 
>embedded chip and flash for commercial products that don't even 
>pay for SQLite.

>SQLite is already amazingly small for the value it brings. And if
>people want "smaller", they can still stick
>with older leaner versions of SQLite too. My $0.02... --DD

The custom version of the library that I build which contains *all* features 
and extensions built-in, and then some, compiled with MinGW-w64 GCC 7.1.0 on 
Windows using -m64 -O3, and statically linked with all the GCC libraries 
(__float128, runtime, threading, etc., so no dependancies other than to the 
subsystem runtime (MSVCRT) and the Windows DLLs actually used) comes in around 
2 MB ... APSW a wee bit bigger ...

2018-05-30  16:40 2,173,456 sqlite3.dll
2018-05-30  16:43 2,326,544 apsw.cp36-win_amd64.pyd

Of course, this includes almost all the extensions, all the math library, many 
Windows APIs, and a bunch of aggregates/functions/collations to handle IP 
Addresses (v4 and v6), some running statistics, proper rounding (Half-Even), 
all the hash functions 
(MD4/MD5/SHA/SHA1/SHA2(256/384/512)/SHA3(224/256/384/512)) and a few other odds 
and sods.

If it were for a computer with "limited resources" I would par it down a lot, 
but having everything automatically available is very nice ... and I am not 
CPU/Memory/IO constained (though having all NVMe drives does make me have to 
"fix" things from time to time to stay within the constraints of spinning rust 
(3 GB/s I/O can become very addictive).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

[sqlite] Create Index after Data Populated

[Andrew Stewart]

Hi,
I have a database that is populated with data (and more being 
added) and I have realized that I need an extra index to speed up access.  How 
long should it take to update the Index.  There are about 1billion rows in the 
database

Thanks,
Andrew Stewart
Software Designer

ARGUS CONTROLS
18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada
t +1.604.538.3531  ext. 108 | +1.800.667.2090 | f +1.604.538.4728
www.arguscontrols.com

Notice: This electronic transmission contains confidential information, 
intended only for the person(s) named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or any other use of this email is strictly prohibited. If you have received 
this transmission by error, please notify us immediately by return email and 
destroy the original transmission immediately and all copies thereof.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

[Shevek]

SQL overall is phenomenally ambiguous about group-by:

1) select a0 + 1 as a0 from a group by a0

has a different behaviour in different SQL implementations, depending on 
whether the group-by prefers the underlying column or the projection alias.


2) As you rightly point out, group by integer has a different behaviour 
in different implementations, depending on how they range-check for "is 
a projection reference" - is a valid integer, is out of range, ...


3) group by expression has several of the above ambiguities, when it's 
legal.


4) Everything else is AFU'd too.

S.

On 05/31/2018 09:49 AM, Mark Brand wrote:
At first I  wasn't sure what you meant by "column identifier". It's what 
the documentation calls "column-alias". But this also qualifies as an 
"other expression" doesn't it? A column alias can appear pretty much 
anywhere any expression can, except in the same column list where it is 
defined.


Mark


On 31/05/18 18:27, Hick Gunter wrote:

Ponder the following select fragment

SELECT name, year * 100 + month as period, day + hour / 24.0 from 
some_table


Then you can have

ORDER BY 1,2,3 -- integer output column numbers

which is equivalent to

ORDER BY name, period, day + hour / 24.0 -- output column identifier 
x2 + "any other expression"


-Original Message-
From: sqlite-users 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of 
Mark Brand

Sent: Mittwoch, 30. Mai 2018 16:22
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks for the clarification.


You have constant integers, output column identifiers and "any other 
expression" as terms for GROUP BY.
Just to make sure I'm not missing something subtle: I understand the 
"constant integer" is what gets interpreted as a result column number. 
What is an "output column identifier" then? Isn't it already covered 
by the broader category "any other expression"?


It's still a pretty astonishing language feature(!?) that an integer 
numeric-literal, which in every other column-like context represents 
its integer value, gets interpreted after GROUP BY or ORDER BY as a 
result column number. Fortunately, SQLite isn't to blame for designing 
this.


By the way, this feature is documented for ORDER BY, but I don't see 
it for GROUP BY.


Mark

On 30/05/18 13:28, Hick Gunter wrote:
You have constant integers, output column identifiers and "any other 
expression" as terms for GROUP BY. If the expression evalutes to a 
constant value, you will have only one output row.


-Ursprüngliche Nachricht-
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 12:11
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks. I had forgotten that GROUP BY considers a literal integer in 
this context to be a column number, a feature I don't use.


These, on the other hand, work as I would have expected:

sqlite> select 0 group by cast (0 as int);
0
sqlite> select 0 group by (select 0);
0

Mark


On 30/05/18 12:00, Hick Gunter wrote:
Yes. If the expression is a constant integer K, then it is 
considered an alias for the K-th column of the result set. Columns 
are ordered from left to right starting with 1.


There is no 0-th column, so GROUP BY 0 is "out of range", just the 
same as "SELECT 0 GROUP BY 31" would be.


-Ursprüngliche Nachricht-
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 11:32
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"

Hi,

Is there a good reason for this error:

sqlite> SELECT  0 GROUP BY 0;
Error: 1st GROUP BY term out of range - should be between 1 and 1
sqlite> SELECT 0 GROUP BY 1;
0

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


___
    Gunter Hick | Software Engineer | Scientific Games International
GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the 
addressee.

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

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


___
   Gunter Hick | Software Engineer | Scientific Games International
GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not 

Re: [sqlite] Size of the SQLite library

[Thomas Kurz]

I totally agree with that. On most systems it is much more important to have a 
feature-rich library than a very small one. Any application where a few bytes 
more or less matter should be written in pure assembler anyway.


- Original Message - 
From: Dominique Devienne 
To: General Discussion of SQLite Database 
Sent: Thursday, May 31, 2018, 18:18:51
Subject: [sqlite] Size of the SQLite library

On Thu, May 31, 2018 at 3:44 PM Richard Hipp  wrote:

> For many years, we have boasted that the size of the SQLite library is
> "less than half a megabyte".


Given where the conversation is going, let me point out that many do not
care one bit about the lib's size :)

I'd much rather have an SQLite with tons of features, than forego those in
the name saving a few bytes,
to save a few bucks on the embedded chip and flash for commercial products
that don't even pay for SQLite.

SQLite is already amazingly small for the value it brings. And if people
want "smaller", they can still stick
with older leaner versions of SQLite too. My $0.02... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Size of the SQLite library

[Richard Hipp]

On 5/31/18, Simon Slavin  wrote:
>
> Did you know that less than half of SQLite installations are on desktop
> computers ?  My guess is that mobile phones are now the biggest category of
> devices.  They run off battery power.  They have firmware on chips.  The
> more chips they have to keep powered-up, the more battery power they use,
> the physically bigger the phone has to be to hold not just the chips but the
> bigger battery, the heavier and more expensive it is.
>

Back in the day, Motorola and Symbian used to really pressure us to
keep the library footprint down.  Every byte mattered.

But more recently, mobile phone designers are telling me things like
"try to keep the size under 5 megabytes, if you can, please."

Based on those more recent conversations, I'm thinking that we have
more headroom that we have had historically, and so I have recently
been allowing new features to start creeping into the core.

Size is still important.  But having useful features is important too.
I'm continuing to work to find the right balance between these
competing goals.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

[Mark Brand]

At first I  wasn't sure what you meant by "column identifier". It's what 
the documentation calls "column-alias". But this also qualifies as an 
"other expression" doesn't it? A column alias can appear pretty much 
anywhere any expression can, except in the same column list where it is 
defined.


Mark


On 31/05/18 18:27, Hick Gunter wrote:

Ponder the following select fragment

SELECT name, year * 100 + month as period, day + hour / 24.0 from some_table

Then you can have

ORDER BY 1,2,3 -- integer output column numbers

which is equivalent to

ORDER BY name, period, day + hour / 24.0 -- output column identifier x2 + "any other 
expression"

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Mark Brand
Sent: Mittwoch, 30. Mai 2018 16:22
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks for the clarification.



You have constant integers, output column identifiers and "any other 
expression" as terms for GROUP BY.

Just to make sure I'm not missing something subtle: I understand the "constant integer" is what 
gets interpreted as a result column number. What is an "output column identifier" then? Isn't it 
already covered by the broader category "any other expression"?

It's still a pretty astonishing language feature(!?) that an integer 
numeric-literal, which in every other column-like context represents its 
integer value, gets interpreted after GROUP BY or ORDER BY as a result column 
number. Fortunately, SQLite isn't to blame for designing this.

By the way, this feature is documented for ORDER BY, but I don't see it for 
GROUP BY.

Mark

On 30/05/18 13:28, Hick Gunter wrote:

You have constant integers, output column identifiers and "any other 
expression" as terms for GROUP BY. If the expression evalutes to a constant value, 
you will have only one output row.

-Ursprüngliche Nachricht-
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 12:11
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks. I had forgotten that GROUP BY considers a literal integer in this 
context to be a column number, a feature I don't use.

These, on the other hand, work as I would have expected:

sqlite> select 0 group by cast (0 as int);
0
sqlite> select 0 group by (select 0);
0

Mark


On 30/05/18 12:00, Hick Gunter wrote:

Yes. If the expression is a constant integer K, then it is considered an alias 
for the K-th column of the result set. Columns are ordered from left to right 
starting with 1.

There is no 0-th column, so GROUP BY 0 is "out of range", just the same as "SELECT 0 
GROUP BY 31" would be.

-Ursprüngliche Nachricht-
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 11:32
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"

Hi,

Is there a good reason for this error:

sqlite> SELECT  0 GROUP BY 0;
Error: 1st GROUP BY term out of range - should be between 1 and 1
sqlite> SELECT 0 GROUP BY 1;
0

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


___
Gunter Hick | Software Engineer | Scientific Games International
GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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


___
   Gunter Hick | Software Engineer | Scientific Games International
GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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


___
  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please 

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[dmp]

Scott Robinson wrote:

> What version of SQLite are you using for this? I just did the
> following and do not see the string quoted values you are describing:

I'm sorry, the example given was just the current one I was
using to test the fix. In general I found the application was
quoting all Numbers and I was WRONG about the meta data stored
in the db file. It seems in almost all cases Sqlite is doing
a very good job of storing meta data I think.

Too clarify my application is coded in Java and is using a JDBC.

The example belows indicates that even when a mistake is made for
instance in declaring a type of TINYIN, the SQL type stored or
derived from the JDBC for the meta data is, 4, INTEGER. Good
guess!

CREATE TABLE exnumeric(
weight TINYNT NOT NULL,
speed DOUBLE);

NameJava Class   SQLTypeSQL Type Name
weight  java.lang.Object4   TINYNT
speed   java.lang.Object8   DOUBLE

In general if a table type is declared outside the defined SQL
types it still, for example (weight, kilograms), is defined as
TEXT it seems.

The issue I was having had to do with keying off only the Java
Class which in all cases is java.lang.Object. Most other databases
return for example the speed column, as java.lang.Double
from a Java ResultSetMetaData.

danap.

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

Re: [sqlite] Size of the SQLite library

[Simon Slavin]

On 31 May 2018, at 5:18pm, Dominique Devienne  wrote:

> On Thu, May 31, 2018 at 3:44 PM Richard Hipp  wrote:
> 
>> For many years, we have boasted that the size of the SQLite library is
>> "less than half a megabyte".
> 
> Given where the conversation is going, let me point out that many do not
> care one bit about the lib's size :)

Did you know that less than half of SQLite installations are on desktop 
computers ?  My guess is that mobile phones are now the biggest category of 
devices.  They run off battery power.  They have firmware on chips.  The more 
chips they have to keep powered-up, the more battery power they use, the 
physically bigger the phone has to be to hold not just the chips but the bigger 
battery, the heavier and more expensive it is.

Not to mention a whole fleet of handheld safety-testing equipment with 
different configurations for different installations, and those gadgets parking 
inspectors carry around to keep track of which cars parked when.

Might be interesting to find out what proportion of SQLite devices are 
mains-powered vs. battery-powered.  Although whether one should class an Airbus 
A350 XWB as "battery-powered" I am not certain.

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

Re: [sqlite] Size of the SQLite library

[J. King]

On May 31, 2018 12:18:51 PM EDT, Dominique Devienne  wrote:
>On Thu, May 31, 2018 at 3:44 PM Richard Hipp  wrote:
>
>> For many years, we have boasted that the size of the SQLite library
>is
>> "less than half a megabyte".
>>
>
>Given where the conversation is going, let me point out that many do
>not
>care one bit about the lib's size :)
>
>I'd much rather have an SQLite with tons of features, than forego those
>in
>the name saving a few bytes,
>to save a few bucks on the embedded chip and flash for commercial
>products
>that don't even pay for SQLite.
>
>SQLite is already amazingly small for the value it brings. And if
>people
>want "smaller", they can still stick
>with older leaner versions of SQLite too. My $0.02... --DD
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I agree with this sentiment. I mostly use SQLite in PHP, where it is awkward to 
customize SQLite, and all but impossible to rely on features not included in 
the standard build when distributing to others. A more powerful default 
configuration would be very beneficial, and a less powerful one possibly 
crippling. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

[Hick Gunter]

Ponder the following select fragment

SELECT name, year * 100 + month as period, day + hour / 24.0 from some_table

Then you can have

ORDER BY 1,2,3 -- integer output column numbers

which is equivalent to

ORDER BY name, period, day + hour / 24.0 -- output column identifier x2 + "any 
other expression"

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Mark Brand
Sent: Mittwoch, 30. Mai 2018 16:22
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks for the clarification.


> You have constant integers, output column identifiers and "any other 
> expression" as terms for GROUP BY.

Just to make sure I'm not missing something subtle: I understand the "constant 
integer" is what gets interpreted as a result column number. What is an "output 
column identifier" then? Isn't it already covered by the broader category "any 
other expression"?

It's still a pretty astonishing language feature(!?) that an integer 
numeric-literal, which in every other column-like context represents its 
integer value, gets interpreted after GROUP BY or ORDER BY as a result column 
number. Fortunately, SQLite isn't to blame for designing this.

By the way, this feature is documented for ORDER BY, but I don't see it for 
GROUP BY.

Mark

On 30/05/18 13:28, Hick Gunter wrote:
> You have constant integers, output column identifiers and "any other 
> expression" as terms for GROUP BY. If the expression evalutes to a constant 
> value, you will have only one output row.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Mark Brand
> Gesendet: Mittwoch, 30. Mai 2018 12:11
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"
>
> Thanks. I had forgotten that GROUP BY considers a literal integer in this 
> context to be a column number, a feature I don't use.
>
> These, on the other hand, work as I would have expected:
>
> sqlite> select 0 group by cast (0 as int);
> 0
> sqlite> select 0 group by (select 0);
> 0
>
> Mark
>
>
> On 30/05/18 12:00, Hick Gunter wrote:
>> Yes. If the expression is a constant integer K, then it is considered an 
>> alias for the K-th column of the result set. Columns are ordered from left 
>> to right starting with 1.
>>
>> There is no 0-th column, so GROUP BY 0 is "out of range", just the same as 
>> "SELECT 0 GROUP BY 31" would be.
>>
>> -Ursprüngliche Nachricht-
>> Von: sqlite-users
>> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
>> Mark Brand
>> Gesendet: Mittwoch, 30. Mai 2018 11:32
>> An: SQLite mailing list 
>> Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"
>>
>> Hi,
>>
>> Is there a good reason for this error:
>>
>> sqlite> SELECT  0 GROUP BY 0;
>> Error: 1st GROUP BY term out of range - should be between 1 and 1
>> sqlite> SELECT 0 GROUP BY 1;
>> 0
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___
>>Gunter Hick | Software Engineer | Scientific Games International
>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>> 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not the addressee.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>   Gunter Hick | Software Engineer | Scientific Games International
> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Size of the SQLite library

[Dominique Devienne]

On Thu, May 31, 2018 at 3:44 PM Richard Hipp  wrote:

> For many years, we have boasted that the size of the SQLite library is
> "less than half a megabyte".
>

Given where the conversation is going, let me point out that many do not
care one bit about the lib's size :)

I'd much rather have an SQLite with tons of features, than forego those in
the name saving a few bytes,
to save a few bucks on the embedded chip and flash for commercial products
that don't even pay for SQLite.

SQLite is already amazingly small for the value it brings. And if people
want "smaller", they can still stick
with older leaner versions of SQLite too. My $0.02... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Chris Brody]

On Thu, May 31, 2018 at 11:38 AM, Richard Hipp  wrote:
> [...]
> By using multiple SQLITE_OMIT compile-time options to leave out
> features, I can get the size down to 308,189 bytes using gcc-7 -Os
> -m32.

@Richard can you elaborate some more on how you make this kind of a build?

I wouldn't mind if we drop some more less-used options from the
default build to keep the standard size "less than half a megabyte".
Also -1 for kibibyte/mebibyte wording on my part.

On Thu, May 31, 2018 at 11:57 AM, Christian Schmitz
 wrote:
> [...]
> Maybe your graph should have three lines.

+1 would be nice, not major though (I think)

On Thu, May 31, 2018 at 11:58 AM, R Smith  wrote:
> [...]
> Towards my point though, both Bob and Vance, would you be especially swayed
> if the marketing slogan had said "under a megabyte" as opposed to "under
> half a megabyte"?

I think Vance already gave the answer (negative). Would it be an idea
to have size slogans for both regular and embedded builds?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[R Smith]

On 2018/05/31 5:17 PM, ven...@intouchmi.com wrote:

I have to agree with Bob!

We have considered SQLITE for our project.  Going over 500Kbytes puts it
just beyond the size of our Flash - the current Firmware.


I stand corrected! It seems the embedded systems with still an extremely 
limited memory footprint size may not be as thin on the ground as I 
imagined, and I regret trying to categorize all embedded systems under 
the same ideal - apologies for that.


Towards my point though, both Bob and Vance, would you be especially 
swayed if the marketing slogan had said "under a megabyte" as opposed to 
"under half a megabyte"?  I still feel that this level of embedded 
system is not common, and even where it might be common, I bet that 
slogan is not the catch phrase that got you interested in SQLite (or 
would sway you from choosing it).


It's however clear my view may not be 100% representative, so perhaps 
the  KiB or 0.5 MiB route has its place.



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

Re: [sqlite] Size of the SQLite library

[Christian Schmitz]

> 
> See https://sqlite.org/tmp/size-20180531.jpg for the library size
> trend over 5 years. 

Maybe your graph should have three lines.

1. Minimum SQLite with all off
2. Default SQLite
3. Maximum SQLite with all on

Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/


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

Re: [sqlite] This is driving me nuts

[Scott Robison]

Sqlite will use different strategies for ASC and desc ordering and result
set sizes. Perhaps one is creating a temp btree to order the results. I
think explain query plan might help show exactly what sqlite is
contributing to the memory consumption without the need for as much
speculation. Not intended as a critical comment, just a thought in passing.

On Thu, May 31, 2018, 9:22 AM x  wrote:

> Yes, I think you’re right. I suppose maybe it recognises the desc sequence
> from page access.
>
>
>
> 
> From: sqlite-users  on
> behalf of Andy Ling 
> Sent: Thursday, May 31, 2018 3:57:27 PM
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] This is driving me nuts
>
> > While that makes sense Clemens it again fails to explain why it does no
> > caching when desc is stepped through first. I am at least satisfied that
> it’s not
> > something I should dwell on or, more the point, it isn’t down to a bug
> in my
> > code which is why I got embroiled in it in the first place.
> >
>
> My understanding from what Clemens said, which might clarify.
>
> When ascending pages are read sequentially, so sequential mode is used and
> the pages get saved in the cache, but when descending, pages are read in
> reverse order which makes the cache think it's random so it doesn't keep
> them.
>
> So if you do ascending first the cache gets filled and eats up memory.
> Then when you do descending it gradually deletes all the pages from the
> cache
> and frees up the memory. If you do it the other way round, descending
> uses one page worth of memory then ascending eats up enough for all the
> pages.
>
> 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-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Richard Hipp]

On 5/31/18, ven...@intouchmi.com  wrote:
>
> We have considered SQLITE for our project.  Going over 500Kbytes puts it
> just beyond the size of our Flash - the current Firmware.

By using multiple SQLITE_OMIT compile-time options to leave out
features, I can get the size down to 308,189 bytes using gcc-7 -Os
-m32.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

Yes, I think you’re right. I suppose maybe it recognises the desc sequence from 
page access.




From: sqlite-users  on behalf of 
Andy Ling 
Sent: Thursday, May 31, 2018 3:57:27 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] This is driving me nuts

> While that makes sense Clemens it again fails to explain why it does no
> caching when desc is stepped through first. I am at least satisfied that it’s 
> not
> something I should dwell on or, more the point, it isn’t down to a bug in my
> code which is why I got embroiled in it in the first place.
>

My understanding from what Clemens said, which might clarify.

When ascending pages are read sequentially, so sequential mode is used and
the pages get saved in the cache, but when descending, pages are read in
reverse order which makes the cache think it's random so it doesn't keep them.

So if you do ascending first the cache gets filled and eats up memory.
Then when you do descending it gradually deletes all the pages from the cache
and frees up the memory. If you do it the other way round, descending
uses one page worth of memory then ascending eats up enough for all the pages.

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

Re: [sqlite] Size of the SQLite library

[veneff]

I have to agree with Bob! 

We have considered SQLITE for our project.  Going over 500Kbytes puts it
just beyond the size of our Flash - the current Firmware.

Vance 

On 2018-05-31 11:04, Bob Friesenhahn wrote:

> On Thu, 31 May 2018, R Smith wrote: 
> 
>> Nice idea, but to be honest, I can't remember when last someone cared about 
>> "Kilobytes", and I mean embedded people, not big OSes.
> 
> I work on embedded projects and we do definitely worry about "kilobytes".  
> This is even though our embedded projects have large resources compared with 
> many other embedded projects.  The firmware image for some of our products is 
> consuming all available Flash pages, (except for spares for 
> wear-leveling/repair).
> 
> Many embedded projects are very cost-sensitive since they sell into 
> hyper-competitive markets where being a bit more expensive than the 
> competition results in a lack of sales.
> 
>> The measure of importance is how expensive the DATA storing is, both in size 
>> and write-frequency, when committed to some hardware NANDs. The code store 
>> section of even the smallest modern embedded system will be designed to fit 
>> things many megabytes more than SQLite requires (exceptions may exist, but 
>> are really thin on the ground). So then, whether the operating code is given 
>> in KB or MiB or KiB is, to my mind, not very relevant - and it too will 
>> become untrue in a non-too-distant future.
> 
> Your experience is different than mine.  What NOR or NAND Flash chip are you 
> using on your PCB?  If you are not using a single soldered chip with a 
> specialized flash filesystem (e.g. JFFS2, UBIFS, squashfs on UBI or bare) 
> then perhaps you are just using a small form factor PC which uses components 
> common in laptop PCs.
> 
> Bob
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Size of the SQLite library

[Bob Friesenhahn]

On Thu, 31 May 2018, R Smith wrote:


Nice idea, but to be honest, I can't remember when last someone cared about 
"Kilobytes", and I mean embedded people, not big OSes.


I work on embedded projects and we do definitely worry about 
"kilobytes".  This is even though our embedded projects have large 
resources compared with many other embedded projects.  The firmware 
image for some of our products is consuming all available Flash pages, 
(except for spares for wear-leveling/repair).


Many embedded projects are very cost-sensitive since they sell into 
hyper-competitive markets where being a bit more expensive than the 
competition results in a lack of sales.


The measure of importance is how expensive the DATA storing is, both in size 
and write-frequency, when committed to some hardware NANDs. The code store 
section of even the smallest modern embedded system will be designed to fit 
things many megabytes more than SQLite requires (exceptions may exist, but 
are really thin on the ground). So then, whether the operating code is given 
in KB or MiB or KiB is, to my mind, not very relevant - and it too will 
become untrue in a non-too-distant future.


Your experience is different than mine.  What NOR or NAND Flash chip 
are you using on your PCB?  If you are not using a single soldered 
chip with a specialized flash filesystem (e.g. JFFS2, UBIFS, squashfs 
on UBI or bare) then perhaps you are just using a small form factor 
PC which uses components common in laptop PCs.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[Andy Ling]

> While that makes sense Clemens it again fails to explain why it does no
> caching when desc is stepped through first. I am at least satisfied that it’s 
> not
> something I should dwell on or, more the point, it isn’t down to a bug in my
> code which is why I got embroiled in it in the first place.
> 

My understanding from what Clemens said, which might clarify.

When ascending pages are read sequentially, so sequential mode is used and
the pages get saved in the cache, but when descending, pages are read in
reverse order which makes the cache think it's random so it doesn't keep them.

So if you do ascending first the cache gets filled and eats up memory.
Then when you do descending it gradually deletes all the pages from the cache
and frees up the memory. If you do it the other way round, descending
uses one page worth of memory then ascending eats up enough for all the pages.

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

Re: [sqlite] Size of the SQLite library

[R Smith]

On 2018/05/31 3:44 PM, Richard Hipp wrote:

For many years, we have boasted that the size of the SQLite library is
"less than half a megabyte".  That will likely still be true in the
3.24.0 release, though just barely.  Compiling with gcc 5.4.0 and -Os
on ubuntu, I get 499,709 bytes.  With gcc 7.1.0 and -Os I get 496,399
bytes.

The library is, of course, larger if you enable optional features such
as full-text search and/or use compiler optimizations like -O3 which
enable loop unrolling and function inlining.  And most people do
compile it that way.  So "less than a megabyte" might be a more
accurate description of SQLite in practice.  But the default
configuration compiled with -Os is a good metric for comparison.

See https://sqlite.org/tmp/size-20180531.jpg for the library size
trend over 5 years.  The measurements in the graph were done with gcc
5.4.0 and -Os on ubuntu.  As you can see, we have held the line below
500,000 bytes for a long time.  But the recent addition of new
features (ex: UPSERT) has caused a slight uptick in the library size.
As further new features are in the pipeline, the upcoming 3.24.0
release will probably be the last for which the library size comes in
at less than 500,000 bytes.  For this reason, I will probably change
the size bullet point to say "less than 500 kibibytes (KiB)" or "less
than 0.5 mebibytes (MiB)", as "less than 600KB" does not have quite
the same emotional impact.  You will notice that the graph linked
above is calibrated in mebibytes.


Nice idea, but to be honest, I can't remember when last someone cared 
about "Kilobytes", and I mean embedded people, not big OSes.


The measure of importance is how expensive the DATA storing is, both in 
size and write-frequency, when committed to some hardware NANDs. The 
code store section of even the smallest modern embedded system will be 
designed to fit things many megabytes more than SQLite requires 
(exceptions may exist, but are really thin on the ground). So then, 
whether the operating code is given in KB or MiB or KiB is, to my mind, 
not very relevant - and it too will become untrue in a non-too-distant 
future.


May I propose, if changing is on the table, to rather update to a more 
current universal notion (and modern embedded capacities) and make it 
leaner by removing one word and simply call it:


"less than a megabyte"

This has much the same emotional impact, still is downright amazing, 
doesn't require naming shenanigans, is very TRUE, even with a few funny 
switches compiled-in, AND will remain true for many years to come, 
possibly to the end of the SQLite lifecycle ~30 years hence.



My 2c...
Ryan


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

Re: [sqlite] This is driving me nuts

[x]

While that makes sense Clemens it again fails to explain why it does no caching 
when desc is stepped through first. I am at least satisfied that it’s not 
something I should dwell on or, more the point, it isn’t down to a bug in my 
code which is why I got embroiled in it in the first place.



For completeness here’s the ‘ac-dc’ figures



for (int i=0; i<5; i++)

{

for (int j=0; j<1000; j++) sqlite3_step(asc);

std::cout << "After asc " << FreeMBs() << std::endl;

for (int j=0; j<1000; j++) sqlite3_step(desc);

std::cout << "After desc " << FreeMBs() << std::endl;

}



10mill Ascending - 10mill Descending

After asc 12324

After desc 12334

After asc 11391

After desc 11434

After asc 10484

After desc 10484

After asc 9534

After desc 9535

After asc 8585

After desc 8586





Tom




From: sqlite-users  on behalf of 
Clemens Ladisch 
Sent: Thursday, May 31, 2018 2:06:08 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] This is driving me nuts

x wrote:
> Why would window’s increase it’s cache size during step ascending then
> reduce it during step descending in such a consistent manner?

Windows has several different caching strategies.

says:
| FILE_FLAG_RANDOM_ACCESS
|
|Access is intended to be random. The system can use this as a hint to
|optimize file caching.
|
| FILE_FLAG_SEQUENTIAL_SCAN
|
|Access is intended to be sequential from beginning to end. The system
|can use this as a hint to optimize file caching.
|
|This flag should not be used if read-behind (that is, reverse scans)
|will be used.
|
| If none of these flags is specified, the system uses a default general-
| purpose caching scheme.

SQLite does not specify these flags.

Apparently, the default general-purpose caching scheme tries to detect
what access pattern the application uses.

The rows are stored in rowid order in the file, so the ascending scan
looks as if it might be a sequential scan.  This means that Windows will
prefetch following data, but it does not know if some data will be read
again later, so it will keep the data in the cache.

The descending scan looks like a reverse scan.  By that point, Windows
probably assumes that you are definitely using sequential scans, so it
thinks the data can be thrown out of the cache after you've read it.


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

[sqlite] Size of the SQLite library

[Richard Hipp]

For many years, we have boasted that the size of the SQLite library is
"less than half a megabyte".  That will likely still be true in the
3.24.0 release, though just barely.  Compiling with gcc 5.4.0 and -Os
on ubuntu, I get 499,709 bytes.  With gcc 7.1.0 and -Os I get 496,399
bytes.

The library is, of course, larger if you enable optional features such
as full-text search and/or use compiler optimizations like -O3 which
enable loop unrolling and function inlining.  And most people do
compile it that way.  So "less than a megabyte" might be a more
accurate description of SQLite in practice.  But the default
configuration compiled with -Os is a good metric for comparison.

See https://sqlite.org/tmp/size-20180531.jpg for the library size
trend over 5 years.  The measurements in the graph were done with gcc
5.4.0 and -Os on ubuntu.  As you can see, we have held the line below
500,000 bytes for a long time.  But the recent addition of new
features (ex: UPSERT) has caused a slight uptick in the library size.
As further new features are in the pipeline, the upcoming 3.24.0
release will probably be the last for which the library size comes in
at less than 500,000 bytes.  For this reason, I will probably change
the size bullet point to say "less than 500 kibibytes (KiB)" or "less
than 0.5 mebibytes (MiB)", as "less than 600KB" does not have quite
the same emotional impact.  You will notice that the graph linked
above is calibrated in mebibytes.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[Simon Slavin]

On 31 May 2018, at 2:06pm, Clemens Ladisch  wrote:

> Windows has several different caching strategies.

Also please note that the caching strategies change between different versions 
of Windows, sometimes even on what looks like a trivial update. Also, that 
drivers can request different strategies.  So caching can happen differently on 
a laptop and a desktop, or even on two computers from the same manufacturer 
with different motherboards.

You can do your research now, figure out lots of things about how Windows seems 
to be handling your application, and then have it all change next month, or 
when you update your computer next year.  I would recommend that you don't 
write code which depends on something undocumented.

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

Re: [sqlite] This is driving me nuts

[Clemens Ladisch]

x wrote:
> Why would window’s increase it’s cache size during step ascending then
> reduce it during step descending in such a consistent manner?

Windows has several different caching strategies.

says:
| FILE_FLAG_RANDOM_ACCESS
|
|Access is intended to be random. The system can use this as a hint to
|optimize file caching.
|
| FILE_FLAG_SEQUENTIAL_SCAN
|
|Access is intended to be sequential from beginning to end. The system
|can use this as a hint to optimize file caching.
|
|This flag should not be used if read-behind (that is, reverse scans)
|will be used.
|
| If none of these flags is specified, the system uses a default general-
| purpose caching scheme.

SQLite does not specify these flags.

Apparently, the default general-purpose caching scheme tries to detect
what access pattern the application uses.

The rows are stored in rowid order in the file, so the ascending scan
looks as if it might be a sequential scan.  This means that Windows will
prefetch following data, but it does not know if some data will be read
again later, so it will keep the data in the cache.

The descending scan looks like a reverse scan.  By that point, Windows
probably assumes that you are definitely using sequential scans, so it
thinks the data can be thrown out of the cache after you've read it.


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

Re: [sqlite] This is driving me nuts

[x]

>While SQLite reads more and more rows from your database, Windows caches more 
>and more pages from the file, explaining the physical available memory 
>diminishes, which does not mean SQLite, nor your program 'use' it: the OS does.

Olivier, your post makes perfect sense but the above begs the question why does 
Windows not bother filling the cache when the descending steps are run first? 
The following will make it even more mysterious.

In the original app I was experimenting with mixing ascending and descending. 
Taking 8192 ascending steps then 8192 descending steps and so on (this was 
being done in a background thread) and the available RAM decreased as the 
number of steps increased. While investigating I found that almost all the RAM 
decreases happened during the 8192 descending steps (in contrast to the console 
app). Freaky or what?

Tom

P.S. The 60 sec sleep made no difference.

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

Re: [sqlite] This is driving me nuts

[Olivier Mascia]

> Le 31 mai 2018 à 12:31, x  a écrit :
> 
>std::cout << "Ascending" << std::endl;
>for (int i=0; sqlite3_step(asc)==SQLITE_ROW; i++)
>if (i%Gap==0) std::cout << FreeMBs() << std::endl;
>std::cout << FreeMBs() << std::endl;
> 
>std::cout << std::endl << "Descending" << std::endl;
>for (int i=0; sqlite3_step(desc)==SQLITE_ROW; i++)
>if (i%Gap==0) std::cout << FreeMBs() << std::endl;
>std::cout << FreeMBs() << std::endl;

Following up on my previous answer, I'd suggest to put the process to sleep for 
let's say at least a good full minute between the first loop and the second one 
and see how it impacts the FreeMBs reporting during the second loop.

You might even put the one minute sleep right after the very first step of the 
second loop has run.

   for (int i=0; sqlite3_step(desc)==SQLITE_ROW; i++)
   {
   if (i == 0) Sleep(6);
   if (i%Gap==0) std::cout << FreeMBs() << std::endl;
   }

You get the idea.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] This is driving me nuts

[Olivier Mascia]

> Le 31 mai 2018 à 13:34, x  a écrit :
> 
> Clemens, when I change to ullAvailVirtual the figure stays pretty much the 
> same throughout the programme as you suggested. I still don’t understand the 
> ullAvailPhys numbers though. Why would window’s increase it’s cache size 
> during step ascending then reduce it during step descending in such a 
> consistent manner?

While SQLite reads more and more rows from your database, Windows caches more 
and more pages from the file, explaining the physical available memory 
diminishes, which does not mean SQLite, nor your program 'use' it: the OS does.

The fact the available physical memory goes up, probably because Windows 
reduces its disk cache, while doing the second loop of your program is 
certainly trickier to explain / understand, but it obviously is plausible - as 
it happens. :)

I wouldn't be surprised SQLite reuse temporary file data in the second phase, 
and such temporary files are typically managed differently by the OS (when 
created with the right set of system flags, which I think SQLite use) : that is 
through virtual memory mapping of the file content, bypassing the filesystem 
usual cache, which in turn being put to less stress might be progressively 
freed by Windows while the second pass run.  Though this is only a wild 
supposition.

End to end it only means two things : 1) your computer and OS does apparently 
work correctly and at least makes use of the resources at hand and 2) getting 
to the bottom-line of this would be much easier with source code of Windows.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] This is driving me nuts

[x]

Clemens, when I change to ullAvailVirtual the figure stays pretty much the same 
throughout the programme as you suggested. I still don’t understand the 
ullAvailPhys numbers though. Why would window’s increase it’s cache size during 
step ascending then reduce it during step descending in such a consistent 
manner?



If I code it to step through desc before asc this is the output



Descending

13275

13276

13314

13314

13315

13315



Ascending

13315

11425

9525

7625

5722

3830



and again those results are repeatable. I don’t understand it but I’m convinced 
it’s not reacting to any other app.




From: sqlite-users  on behalf of 
Clemens Ladisch 
Sent: Thursday, May 31, 2018 11:50:19 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] This is driving me nuts

x wrote:
> I’ve written the console app below to illustrate. It prints out the amount of 
> RAM available every 20 million steps.

> GlobalMemoryStatusEx();
> return status.ullAvailPhys / (1024 * 1024);

This is "physical memory currently available", which is affected by other 
things,
such as the Windows file cache.

Try ullAvailVirtual to see the how much your own process could allocate; this is
affected by both your code and by the SQLite library.  (This value is unlikely 
to
change by much because SQLite's default cache size is 2 MB.)

> It seems to lose RAM while step ascending and then give it back when step 
> descending.

RAM that Windows chooses to use for the file cache is not lost.


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

Re: [sqlite] How does _exec() do a transaction ?

[Simon Slavin]

On 30 May 2018, at 2:24pm, Simon Slavin  wrote:

> [something]

Thanks to the mods, but I realised I'd posted this from a wrong address and 
resubmitted it from the right address.  Already asked and answered.  Thanks, 
everyone.

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

Re: [sqlite] This is driving me nuts

[Clemens Ladisch]

x wrote:
> I’ve written the console app below to illustrate. It prints out the amount of 
> RAM available every 20 million steps.

> GlobalMemoryStatusEx();
> return status.ullAvailPhys / (1024 * 1024);

This is "physical memory currently available", which is affected by other 
things,
such as the Windows file cache.

Try ullAvailVirtual to see the how much your own process could allocate; this is
affected by both your code and by the SQLite library.  (This value is unlikely 
to
change by much because SQLite's default cache size is 2 MB.)

> It seems to lose RAM while step ascending and then give it back when step 
> descending.

RAM that Windows chooses to use for the file cache is not lost.


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

Re: [sqlite] This is driving me nuts

[x]

> I’ve just discovered the thread in the original app decreases the
> available memory by around 4 GB. Are they really that expensive?

After others pointed out threads weren’t that expensive I concluded there was a 
bug in my code. On checking though I couldn’t find anything wrong yet the 
programme was seemingly making 4 GB disappear as it ran. It gets even stranger 
though and I’ve written the console app below to illustrate. It prints out the 
amount of RAM available every 20 million steps. All other front end apps were 
closed during running although there were background tasks running. It was run 
in 64 bit mode although it also works for 32 bit.

#include 
#include 
#pragma hdrstop
#pragma argsused
#include 
#include 
#include 
#include 
#include "sqlite.h"

uint64_t FreeMBs()
{
MEMORYSTATUSEX status;
status.dwLength = sizeof(status);
GlobalMemoryStatusEx();
return status.ullAvailPhys / (1024 * 1024);
}

int _tmain(int argc, _TCHAR* argv[])
{
const int Million=100;
const int Gap=20*Million;
sqlite3 *DB;
sqlite3_open("c:/SQLiteData/MyTemp.db",);
sqlite3_stmt *asc,*desc;
sqlite3_prepare_v2(DB,"select RowID from big order by 
RowID",-1,,NULL);
sqlite3_prepare_v2(DB,"select RowID from big order by RowID 
desc",-1,,NULL);

std::cout << "Ascending" << std::endl;
for (int i=0; sqlite3_step(asc)==SQLITE_ROW; i++)
if (i%Gap==0) std::cout << FreeMBs() << std::endl;
std::cout << FreeMBs() << std::endl;

std::cout << std::endl << "Descending" << std::endl;
for (int i=0; sqlite3_step(desc)==SQLITE_ROW; i++)
if (i%Gap==0) std::cout << FreeMBs() << std::endl;
std::cout << FreeMBs() << std::endl;

sqlite3_finalize(asc);
sqlite3_finalize(desc);
sqlite3_close(DB);
getch();
return 0;
}

The big table can be emulated with this sql which creates a table with 100 
million records

sqlite3_exec(DB, "create table wee as "
"with cte(a, b, c) as "
"(values (1, '', '') "
"union all "
"select a+1, b, c from cte where a<1) "
"select * from cte;"
"create table big as select * from wee t1, wee t2;", 0, 0, 0);

OUTPUT

Ascending
13227
11355
9465
7582
5683
3801

Descending
3801
5868
7773
9683
11586
13473

On each run the numbers may be different but the overall results are the same. 
It seems to lose RAM while step ascending and then give it back when step 
descending. I can’t imagine it’s down to sqlite but can’t see anything wrong 
with my code. Can anyone put me out of my misery?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Congratulations on 18 years

[Dominique Devienne]

On Wed, May 30, 2018 at 6:16 PM jungle Boogie 
wrote:

> On 30 May 2018 at 03:27, Christian Schmitz
>  wrote:
> > Congratulations to the SQLite team.
> >
> > As far as I see, the first checkin was 2000-05-29, which was over 18
> years ago.
>
> Way to go! What a truly awesome project this has been!
>

Kudos indeed! To Richard, Dan, Joe, and company.

I'm rooting for a special 20 years celebrations. And why not with an SQLite
conference?

Despite the "lite" in SQLite, there's a wealth of features, extensions,
experimental feature branches,
not even talking of all the ecosystem around SQLite with tools, "IDEs",
etc... A special
conference to teach/present on all this, and discuss the "next 20 years",
would be
wonderful, and something I'd travel to Europe from (I'd assume it'd be in
the US...). My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users