[sqlite] order by not working in combination with random()
On 08/28/2015 09:36 PM, Scott Robison wrote:
> On Fri, Aug 28, 2015 at 3:47 AM, Yahoo! Mail yahoo.com
>> wrote:
>>
>> On 08/26/2015 09:03 PM, Richard Hipp wrote:
>>
>> Time stands still for multiple rows, as long as they are within the
>>> same sqlite3_step() call. For example, if you run:
>>>
>>> CREATE TABLE t1(a DATETIME);
>>> WITH RECURSIVE
>>> c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10)
>>> INSERT INTO t1(a) SELECT datetime('now') FROM c;
>>>
>>> The entire INSERT statement will be a single sqlite3_step() call, and
>>> so all billion rows of t1 will get set to the same time.
>>>
>> I have tested this code with version 3.8.10.2 using "DB Browser for
>> SQLite" and it would crash; the same with SQLite Manager that uses the same
>> version. I have decided to use the latest SQLite version available from GNU
>> / Linux Debian testing 64-bit; it's 3.8.11.1 and something strange is
>> happening. I hope I'm the reason behind its rather peculiar behavior.
>>
>> I tested the following code
>>
>> *sqlite> drop table if exists t1; create table t1(a datetime); begin; with
>> recursive c(x) as (values(1) union all select x + 1 from c where x <
>> 10) insert into t1(a) select datetime('now') from c; commit;*
>>
>> It would take ages to finish and that is logical; it's a billion rows it
>> has to insert, even though I have used begin - commit.
>>
>> Now the rather strange behavior would be when I use the up arrow key to
>> reuse the previous code but replacing *10* with *10*. It takes the
>> same time to finish as before and that is not right; I'm dropping the table
>> and it should take nearly 0 milliseconds for 10 rows.
>>
>> Can someone confirm this?
>>
> I have been using 3.8.11, so I just downloaded 3.8.11.1 to test your exact
> query, cut & pasted from above. Here is the output from my session:
>
> X:\>sqlite3
> SQLite version 3.8.11.1 2015-07-29 20:00:57
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .timer on
> sqlite> drop table if exists t1; create table t1(a datetime); begin; with
> recursive c(x) as (values(1) union all select x + 1 from c where x <
> 1000) insert into t1(a) select datetime('now') from c; commit;
> Run Time: real 16.751 user 16.520506 sys 0.062400
> sqlite> drop table if exists t1; create table t1(a datetime); begin; with
> recursive c(x) as (values(1) union all select x + 1 from c where x < 10)
> insert into t1(a) select datetime('now') from c; commit;
> Run Time: real 0.387 user 0.374402 sys 0.00
> sqlite>
>
> Note: I pasted the first query that begins drop table, then used up arrow
> and edited the 1000 to 10 for the second query. Further, I didn't have
> time to try a full billion, so I settled for 10 million. Also used a
> transient in-memory database. Finally, I am using Windows 7 Professional on
> a box with 16 GiB of RAM.
I tested it with a local database and not with an in-memory one. I
wanted someone to test it so we could see if it's something that has to
do with a reasonable disk access restriction after a certain number of
multiple attempts.
> Do you see the same behavior for a test of 10M rows (that the second line
> takes the same amount of time as the first)?
>
[sqlite] order by not working in combination with random()
R.Smith,
Thank you for the reply.
I'm pasting again the original message that explains which versions I
have tested on which Operating System.
/I have tested this code with version 3.8.10.2 using "DB Browser for
SQLite" and it would crash; the same with SQLite Manager that uses the
same version. I have decided to use the latest SQLite version available
from GNU / Linux Debian testing 64-bit; it's 3.8.11.1 and something
strange is happening. I hope I'm the reason behind its rather peculiar
behavior.//
//I tested the following code //
*sqlite> drop table if exists t1; create table t1(a datetime);
begin; with recursive c(x) as (values(1) union all select x + 1 from c
where x < 10) insert into t1(a) select datetime('now') from c;
commit;*//
//It would take ages to finish and that is logical; it's a billion rows
it has to insert, even though I have used begin - commit.//
//Now the rather strange behavior would be when I use the up arrow key
to reuse the previous code but replacing //*10*//with //*10*//.
It takes the same time to finish as before and that is not right; I'm
dropping the table and it should take nearly 0 milliseconds for 10 rows.//
//Can someone confirm this?//
//Cheers./
So in other words, what I did was the following:
* I ran the aforementioned command with a billion rows, waited for it
to finish; it took some minutes to complete its task.
* re-run the same command but with 10 rows only; it *should* have
finished within milliseconds, but it repeated the same task as
above, even the database size remained the same (28GB that is).
On 08/28/2015 05:45 PM, R.Smith wrote:
>
>
> On 2015-08-28 03:09 PM, Yahoo! Mail wrote:
>> Where did you see the vacuum happening inside the transaction? It's
>> just right before begin...anyway. It seems I'm unable to make clear
>> the actual "issue" of mine, but anyhow it's not a bit deal. I just
>> wanted to report what I have noticed, that's all.
>
> No no, I did not say you did the vacuum inside a transaction, I meant
> that if I had to include that in my script to see the timed-output
> from it (because I suspected it might take a long time), it would have
> had to be part of the transaction - which of course it cannot be, as
> you already know.
>
> And please do not be dismayed, we would like to assist or at least
> understand your issue because the idea we now have is that you have a
> version of SQLite that somehow takes almost 2 minutes to drop a table
> with only 100-million rows with only dates in it. This would be
> horrible. We are not trying to prove you wrong, we are trying to
> establish the facts.
>
> What we (myself and other responders) tried to show is that SQLite
> does not do that for us at all.. our tables of that size drop in about
> 3 to 6 seconds - which means (the bit we do not specifically say
> because we do not wish to bore everyone with detail) that:
>
> A - We do not get the same results as you, so either there is
> something about your use-case we do not know and you did not state, or
> B - We are not understanding what you mean precisely, so will need a
> bit more description, or
> C - You are using a much different version than we are and you have
> uncovered a bug - in which case we will need to know the specifics:
> - Which version?
> - Did you compile it yourself? Or downloaded from the site?
> D - Your hardware / OS is not playing ball - in which case we will
> need specifics.
>
> In fact, we will need more and more specifics until we can find a case
> that does for us exactly what it does for you, or where we understand
> the difference exactly.
>
> And - if this is a real possibility of a bug, or even just a missed
> opportunity for optimization, we would really like to know it - so
> please try to clarify.
>
> Thanks,
> Ryan
>
>
> PS: We also know your first language isn't English, neither is mine,
> so please bear in mind that we might not clearly understand the first
> time you say something - but that doesn't mean we do not want to
> understand, so don't give up - explain more!
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] order by not working in combination with random()
Where did you see the vacuum happening inside the transaction? It's just
right before begin...anyway. It seems I'm unable to make clear the
actual "issue" of mine, but anyhow it's not a bit deal. I just wanted to
report what I have noticed, that's all.
On 08/28/2015 03:43 PM, R.Smith wrote:
>
> On 2015-08-28 01:17 PM, Yahoo! Mail wrote:
>> Obviously you did not get my issue; something is wrong and your timer
>> suggestion indicates this. During the execution of each command, I
>> would monitor it with *watch "du test.db*"*. The journal size would
>> go mad even surpassing the database's actual size at some moments.
>>
>> *sqlite> .timer on
>> sqlite> drop table if exists t1; create table t1(a datetime); vacuum
>> t1; begin; with recursive c(x) as (values(1) union all select x + 1
>> from c where x < 100) insert into t1(a) select datetime('now') from
>> c; commit;
>> Run Time: real 0.207 user 0.00 sys 0.004000
>>
>> sqlite> drop table if exists t1; create table t1(a datetime); vacuum
>> t1; begin; with recursive c(x) as (values(1) union all select x + 1
>> from c where x < 1) insert into t1(a) select datetime('now')
>> from c; commit;
>> Run Time: real 94.226 user 73.096000 sys 4.788000
>>
>> sqlite> drop table if exists t1; create table t1(a datetime); vacuum
>> t1; begin; with recursive c(x) as (values(1) union all select x + 1
>> from c where x < 100) insert into t1(a) select datetime('now') from
>> c; commit;
>> Run Time: real 209.612 user 4.724000 sys 21.588000*
>
> Firstly, that's a 100-million inserts, not a billion as in the
> previous post. The billion inserts should have taken around 1000s or
> 20-ish minutes.
>
> It might be the vacuum that takes the time for you... the drop table
> is quick.
> If I run the same through SQLitespeed hooking SQLite 3.8.11.1 with
> full diagnostics (and probably worse hardware than you have), I get
> about 120sec (2 minutes) on the insert into an empty table, and 3.6s
> on the drop+insert 10 items.
> I am not vacuuming inside the following script since vacuums cannot
> happen inside transactions, but I vacuum directly after it and the
> vacuum takes less than 2 seconds, so I doubt that is the problem either.
>
>
> --
>
>
> drop table if exists t1;
>
> create table t1(a datetime);
>
> with recursive c(x) as (values(1) union all select x + 1 from c where
> x < 1) insert into t1(a) select datetime('now') from c;
>
>
> -- Script Stats: Total Script Execution Time: 0d 00h 02m and
> 00.082s
> -- Total Script Query Time: 0d 00h 02m and
> 00.064s
> -- Total Database Rows Changed: 1
> -- Total Virtual-Machine Steps: -2094967211
> -- Last executed Item Index:3
> -- Last Script Error:
> --
>
>
> -- 2015-08-28 14:25:16.109 | [Success]Script Success.
> -- 2015-08-28 14:25:45.088 | [Success]Transaction Committed.
> --
>
>
> drop table if exists t1;
>
> create table t1(a datetime);
>
> with recursive c(x) as (values(1) union all select x + 1 from c where
> x < 10) insert into t1(a) select datetime('now') from c;
>
>
> -- Script Stats: Total Script Execution Time: 0d 00h 00m and
> 03.605s
> -- Total Script Query Time: 0d 00h 00m and
> 03.582s
> -- Total Database Rows Changed: 10
> -- Total Virtual-Machine Steps: 305
> -- Last executed Item Index:3
> -- Last Script Error:
> --
>
>
> -- 2015-08-28 14:26:29.095 | [Success]Script Success.
> -- 2015-08-28 14:26:29.239 | [Success]Transaction Committed.
> --
>
>
> Maybe some more information about your specific use case and
> environment is needed.
>
> Cheers,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] order by not working in combination with random()
Obviously you did not get my issue; something is wrong and your timer
suggestion indicates this. During the execution of each command, I would
monitor it with *watch "du test.db*"*. The journal size would go mad
even surpassing the database's actual size at some moments.
*sqlite> .timer on
sqlite> drop table if exists t1; create table t1(a datetime); vacuum t1;
begin; with recursive c(x) as (values(1) union all select x + 1 from c
where x < 100) insert into t1(a) select datetime('now') from c; commit;
Run Time: real 0.207 user 0.00 sys 0.004000
sqlite> drop table if exists t1; create table t1(a datetime); vacuum t1;
begin; with recursive c(x) as (values(1) union all select x + 1 from c
where x < 1) insert into t1(a) select datetime('now') from c;
commit;
Run Time: real 94.226 user 73.096000 sys 4.788000
sqlite> drop table if exists t1; create table t1(a datetime); vacuum t1;
begin; with recursive c(x) as (values(1) union all select x + 1 from c
where x < 100) insert into t1(a) select datetime('now') from c; commit;
Run Time: real 209.612 user 4.724000 sys 21.588000*
On 08/28/2015 01:48 PM, Clemens Ladisch wrote:
> Yahoo! Mail wrote:
>> sqlite> drop table if exists t1; create table t1(a datetime); begin; with
>> recursive c(x) as (values(1) union all select x + 1 from c where x <
>> 10) insert into t1(a) select datetime('now') from c; commit;
>>
>> It would take ages to finish and that is logical; it's a billion rows
>> it has to insert, even though I have used begin - commit.
>>
>> Now the rather strange behavior would be when I use the up arrow key
>> to reuse the previous code but replacing *10* with *10*. It
>> takes the same time to finish as before and that is not right; I'm
>> dropping the table and it should take nearly 0 milliseconds for
>> 10 rows.
> The DROP TABLE is slow, but it should not be the same time:
>
> sqlite> .timer on
> sqlite> create ...; begin; with ... 2000) insert ...; commit;
> Run Time: real 34.315 user 34.242219 sys 0.046800
> sqlite> drop table if exists t1;
> Run Time: real 0.578 user 0.577204 sys 0.00
> sqlite> create ...; begin; with ... 10) insert ...; commit;
> Run Time: real 0.000 user 0.00 sys 0.00
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] order by not working in combination with random()
On 08/26/2015 09:03 PM, Richard Hipp wrote:
> Time stands still for multiple rows, as long as they are within the
> same sqlite3_step() call. For example, if you run:
>
> CREATE TABLE t1(a DATETIME);
> WITH RECURSIVE
> c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10)
> INSERT INTO t1(a) SELECT datetime('now') FROM c;
>
> The entire INSERT statement will be a single sqlite3_step() call, and
> so all billion rows of t1 will get set to the same time.
I have tested this code with version 3.8.10.2 using "DB Browser for
SQLite" and it would crash; the same with SQLite Manager that uses the
same version. I have decided to use the latest SQLite version available
from GNU / Linux Debian testing 64-bit; it's 3.8.11.1 and something
strange is happening. I hope I'm the reason behind its rather peculiar
behavior.
I tested the following code
*sqlite> drop table if exists t1; create table t1(a datetime); begin;
with recursive c(x) as (values(1) union all select x + 1 from c where x
< 10) insert into t1(a) select datetime('now') from c; commit;*
It would take ages to finish and that is logical; it's a billion rows it
has to insert, even though I have used begin - commit.
Now the rather strange behavior would be when I use the up arrow key to
reuse the previous code but replacing *10* with *10*. It takes
the same time to finish as before and that is not right; I'm dropping
the table and it should take nearly 0 milliseconds for 10 rows.
Can someone confirm this?
Cheers.
[sqlite] What software is deployed more than SQLite?
I run a simple search on a Windows XP (virtual) machine of mine and found the following programs using SQLite: * Adobe Reader * Adobe Acrobat Professional * Avira Antivir (almost 400 million use it based on their official website) * Apple Application Support On 05/03/2015 11:32 PM, Richard Hipp wrote: > On 5/3/15, Richard Hipp wrote: >> I'm trying to update the "Most Deployed Database" page >> (https://www.sqlite.org/mostdeployed.html) in the SQLite documentation >> (which has not been touched in close to a decade)... > I invite merciless criticism of the draft revision at > (https://www.sqlite.org/draft/mostdeployed.html). This is part of the > SQLite "sales pitch", so I want SQLite to look good, but I don't want > to overstate the case.
[sqlite] What software is deployed more than SQLite?
How about curl? This is taken directly from the official website: " curl is used in command lines or scripts to transfer data. It is also used in cars, television sets, routers, printers, audio equipment, mobile phones, tablets, settop boxes, media players and is the internet transfer backbone for thousands of software applications totally affecting more than/one billion users/." On 05/03/2015 11:32 PM, Richard Hipp wrote: > On 5/3/15, Richard Hipp wrote: >> I'm trying to update the "Most Deployed Database" page >> (https://www.sqlite.org/mostdeployed.html) in the SQLite documentation >> (which has not been touched in close to a decade)... > I invite merciless criticism of the draft revision at > (https://www.sqlite.org/draft/mostdeployed.html). This is part of the > SQLite "sales pitch", so I want SQLite to look good, but I don't want > to overstate the case.
[sqlite] (no subject)
Please dnt spam my email
[sqlite] Accented chars in Android embeded Sqlite.
Hi everyone! Im Brazilian, also, Android Dev. Im in a struggle with Sqlite and accented chars such as á or é. What Im trying to do is a select with where accent insensitive. For months I have having research over and over the internet to accomplish that, but, unfortunately unsuccessful. What I need is something like this: select * from table where column like a% and result be: agora água através and so on Its possible? Or better to commit suicide ;-) Thanks in advance Youre all aces in my book. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 datatype
On 11 Aug, Gerry Snyder wrote:
> fred238 wrote:
>> Gerry Snyder a écrit :
>>> fred238 wrote:
Is necessary to set column datatype in sqlite3 ?
Is better to use class storage in sqlite3 (TEXT, NUMERIC, INTEGER,
REAL, NONE) ?
>>> Check out http://www.sqlite.org/datatype3.html and ask again here if
>>> yu have further questions.
>>>
>> already read it and i don't understand all
>
> Ok, I will try to help a little.
>
> You do not need to set data types. If you don't, the default affinity
> will be NUMERIC. That is, anything that looks like a number will be
> stored as an integer or a real, as appropriate. This can waste some time
> if you want to use the value as a string, but in general should not be a
> real problem.
>
> Specifying datatypes may make things easier for some strongly typed
> languages (I don't really know--I use Tcl myself).
I disagree (although I also use Tcl). If you store eg. the string '08'
in a column with type NUMERIC, you'll get '8' back, in later
SELECTs. And that is probably not what you want. Set the column type
to 'TEXT' in such cases.
As in tcl, with 'eq' and '=='. Use
if {$a eq $b} {...}
to test for string identity, and
if {$a == $b} {...}
to test for equal numeric values.
I see dynamic typing as a feature (well, I'm using tcl). But this does
not mean, it is without pitfalls.
rolf
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Query on sorting
Hi, I'm developing a small reception application for staff to use. The app records visitors signing in and out of the building. CREATE TABLE av2 ( Activity_ID INTEGER PRIMARY KEY, Timestamp TIMESTAMP, Visitor_ID VARCHAR(8), Action VARCHAR(1), Message VARCHAR(255)); insert into av2 (Activity_ID,Timestamp,Visitor_ID,Action,Message) values (NULL,"2004-05-23 22:29:32","00120145","I","Signed IN"); insert into av2 (Activity_ID,Timestamp,Visitor_ID,Action,Message) values (NULL,"2004-05-23 23:29:32","00120145","O","Signed OUT"); insert into av2 (Activity_ID,Timestamp,Visitor_ID,Action,Message) values (NULL,"2004-05-23 20:29:32","00144321","I","Signed OUT"); What I would like is to run a query, at night, which will display the last entered value for each visitor_id. Anyone still logged in will alert the building manager. I thought the following would work; SELECT Visitor_ID,Action,Message, MAX(Timestamp) FROM (SELECT * FROM av2 WHERE (Action='I' OR Action='O')) GROUP BY Visitor_ID; It seems to almost do it, the result; 00120145|I|Signed IN|2004-05-23 23:29:32 The Time is right but the Action and Message is from 22:29:32 log, bit of a mix- up! Its probably down to my daft SQL query, any idea's ? Dan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
