[sqlite] order by not working in combination with random()
On Fri, 28 Aug 2015 14:45:26 +, "Rousselot, Richard A" wrote: > I have noticed that SQLite Query Browser is running slower > than other IDEs, including SQLitespeed, for some reason. > Even when each IDE is set to using similar versions of the > SQLite3.dll. We had a recursive query in SQB take 6 min, > on other IDEs it would be less than 2 min. > > My $0.02 For exactly that reason I would advise everyone to use the sqlite3 command line tool for performance comparisons in this mailing list. -- Regards, Cordialement, Groet, Kees Nuyt
[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()
On 2015-08-28 04:45 PM, Rousselot, Richard A wrote: > I have noticed that SQLite Query Browser is running slower than other IDEs, > including SQLitespeed, for some reason. Even when each IDE is set to using > similar versions of the SQLite3.dll. We had a recursive query in SQB take 6 > min, on other IDEs it would be less than 2 min. > > My $0.02 Also, opening a DB with a hot journal will take some time (such as when a previous query was process-killed), the command-line and different browser IDE's might do this at different stages of the user-input and it might cause significant differences in observed duration.
[sqlite] order by not working in combination with random()
If you're talking about Database Browser for SQLite (formally named SQLite Database Browser), at least at one time (version 3.5) it executed each query twice, apparently the first time to figure out the return types, and the second time to display the results (this caused me a certain amount of trouble until I realized the initialization function for my extension was being run twice by SELECT load_extension, and I fixed the function to be OK with being run multiple times). ?It wouldn't explain a 3x+ slowdown, but would explain a 2x anyway. Peter On Friday, August 28, 2015 7:45 AM, "Rousselot, Richard A" wrote: I have noticed that SQLite Query Browser is running slower than other IDEs, including SQLitespeed, for some reason.? Even when each IDE is set to using similar versions of the SQLite3.dll.? We had a recursive query in SQB take 6 min, on other IDEs it would be less than 2 min. My $0.02
[sqlite] Performance problems on windows
On Fri, 28 Aug 2015 12:55:00 + Jakub Zakrzewski wrote: > Hi, > > just and update here. > It seems that the newer libraries perform worse when we use multiple > worker threads. I don't know why and I don't have time to investigate > it any further. We'll stay with the old 3.7.16.2 for now. Perhaps newer has a different compile options or pragma values. Fast check executing "pragma compile_options" and post here if we can see some option that can cause it. Don't know a pragma that list all pragmas values as pragma compile_options do, but it'll be a nice addition for debugging purpouses. > Thank you for your assistance. > > -- > Gruesse, > Jakub > --- --- Eduardo Morras
[sqlite] order by not working in combination with random()
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] Insertion into the large DB drastically slows down at some point
> On modern PCs, SQLite's page cache does not have a large effect because > of the file cache of the OS, but you should do, e.g., > PRAGMA cache_size = -100; > for a 1 GB cache (default is only a few MB). Hitting the page cache is much cheaper as it does not involve a system call. Try opening a memory only db, versus placing sqlite on a ramdisk. A simple benchmark will show a large speed difference. Sergej
[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()
I have noticed that SQLite Query Browser is running slower than other IDEs,
including SQLitespeed, for some reason. Even when each IDE is set to using
similar versions of the SQLite3.dll. We had a recursive query in SQB take 6
min, on other IDEs it would be less than 2 min.
My $0.02
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R.Smith
Sent: Friday, August 28, 2015 7:44 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] order by not working in combination with random()
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
This communication is the property of CenturyLink and may contain confidential
or privileged information. Unauthorized use of this communication is strictly
prohibited and may be unlawful. If you have received this communication in
error, please immediately notify the sender by reply e-mail and destroy all
copies of the communication and
[sqlite] order by not working in combination with random()
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] 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] WHERE clause not working in combination with random()
On 28 Aug 2015, at 10:20am, Domingo Alvarez Duarte wrote: > Please remember that the "random" function here is only a detail the real > problem is the "column expression" been reevaluated more than once. Yes. When considering fixes for this problem one needs to also consider non-deterministic user-defined functions, like Jean-Christophe Deschamps' function "new_uuid()" which returns a new value every time it is called. It's not good enough to make a fix explicitly for random(). I note with interest the constant SQLITE_DETERMINISTIC which may be helpful. So any fix for the overall problem must work * both with random() and with non-deterministic user-defined functions * with a column being used in WHERE * with a column being used in ORDER BY * with a column being used in LIMIT * with a column being used in OFFSET Simon.
[sqlite] Performance problems on windows
Hi,
just and update here.
It seems that the newer libraries perform worse when we use multiple worker
threads. I don't know why and I don't have time to investigate it any further.
We'll stay with the old 3.7.16.2 for now.
Thank you for your assistance.
--
Gruesse,
Jakub
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jakub
Zakrzewski
Sent: Mittwoch, 26. August 2015 13:51
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Performance problems on windows
Hi,
also nope. I start with an empty (completely new) database. Then inserted 100K
what we call "objects" and the same amount of "events". Inserting "events"
takes place in one big commit. As soon as events are detected, the processing
starts. I measure time since beginning of the first process till the end of the
last. It is quite a lot of transactions, selects, and updates. Slightly less
inserts and deletions.
As a rule we do a VACUUM at each startup but clients expect to run this thing
for months (if not years) without restart.
--
Gruesse,
Jakub
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Rowan Worth
Sent: Mittwoch, 26. August 2015 13:33
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Performance problems on windows
What about fragmentation of the database itself? Does running VACUUM on the
database affect performance?
-Rowan
On 26 August 2015 at 16:16, Jakub Zakrzewski wrote:
> Hi,
>
> nope. The defragmentation job runs every Wednsday night and the
> fragmentation is very low.
>
> PS: I'm putting together a small program to replay the queries I got
> from sqlite trace. If that will behave the same way as the original,
> I'll post it here.
>
> --
> Gruesse,
> Jakub
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott
> Robison
> Sent: Dienstag, 25. August 2015 18:55
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Performance problems on windows
>
> On Aug 25, 2015 5:21 AM, "Jakub Zakrzewski" wrote:
> >
> > Hi,
> >
> > The slowdown is confirmed by one of our customers. He uses Win 2008
> Server x64. I'm testing on Win7 x64.
>
> This might be obvious in which case my apologies for bringing it up but:
> are these systems demonstrating slowness perhaps heavily fragmented?
> Either the database is fragmented, other non-SQLite files required by
> the system, or especially the master file table? I've seen truly
> abysmal performance on such systems myself, depending on IO patterns.
>
> >
> > I'm sure that you're testing each release carefully. We do too. And
> > we
> still have weird bugs ;)
> >
> > Thanks for interest. If I can provide any useful information, just
> > tell
> me, what you need. I cannot reveal the source code but some profiling
> results or SQL statements are not a secret.
> >
> > --
> > Gruesse,
> > Jakub
> >
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
> Hipp
> > Sent: Dienstag, 25. August 2015 03:19
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Performance problems on windows
> >
> > I don't have any clues.
> >
> > While most of our work happens on Linux, we do measure performance
> > on
> Windows from one release to the next (see for example item 23-b on the
> most recent release check-list
> > https://www.sqlite.org/checklists/private/3081100/index#c23) and it
> > gets
> better from one release to the next. So I don't know why you are
> seeing a slowdown.
> >
> > How do you measure?
> >
> > On 8/24/15, Jakub Zakrzewski wrote:
> > > Hi All,
> > >
> > > I finally got a chance to upgrade SQLite for our product from
> > > ancient 3.7.16.2. Initial tests on Linux were very promising -
> > > ranging from 33% to even 300% (for one degenerated case) speed
> > > improvement. So far
> so good.
> > > Problems begun when I have tested it on Windows. Depending on test
> > > case the new version is up to 0.28x slower! In the course of
> > > investigation I have managed to improve the performance by adding
> > > few missing indexes but this has influenced both old and new
> > > versions so the relative performance with
> > > 3.8.11.1 is still like one third worse.
> > >
> > > I have tried doing it step-by-step: 3.7.16.2 -> 3.7.17.0 ->
> > > 3.8.0.0 and the results are quite surprising:
> > > 3.7.16.2 -> 3.7.17.0 : ~16% faster
> > > 3.7.17.0 -> 3.8.0.0 : ~26% slower
> > > 3.7.16.2 -> 3.8.0.0 : ~15% slower
> > > 3.7.16.2 -> 3.8.11.1 : ~28% slower
> > >
> > > We use SQLite as backend ("persistent storage") to an
> > > implementation of a state machine. The queries are rather simple
> > > and
[sqlite] order by not working in combination with random()
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] 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] order by not working in combination with random()
On Fri, Aug 28, 2015 at 3:47 AM, Yahoo! Mail 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.
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)?
--
Scott Robison
[sqlite] Sqlite3.exe CLP features through sqlite3.dll
On Fri, Aug 28, 2015 at 12:15 PM, wrote: > The Sqlite3.exe CLP has features like .dump, .schema, .read, .output, etc. > > Can I use through sqlite3.dll functions?. No. They are features of sqlite3.exe, not the DLL. > Some examples and/or information, please > You'll need to port them into your app in order to use them. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] Sqlite3.exe CLP features through sqlite3.dll
Hello The Sqlite3.exe CLP has features like .dump, .schema, .read, .output, etc. Can I use through sqlite3.dll functions?. Some examples and/or information, please Best regards
[sqlite] Version Control
-Original Message- From: Dominique Devienne Sent: Friday, August 28, 2015 4:03 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Version Control On Fri, Aug 28, 2015 at 9:56 AM, Scott Doctor wrote: > Slightly off topic, but I am looking at version control systems. SQLite > looks like it is using Fossil. How does Fossil compare to using Git, > Mercurial, or Subversion? > http://fossil-scm.org/xfer/doc/trunk/www/fossil-v-git.wiki ? --DD ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WHERE clause not working in combination with random()
Now that we have solved the expression column reevaluation problem on the "ORDER BY" clause what about the same problem on the "WHERE" clause ? CREATE TABLE myTable (a INTEGER); INSERT INTO myTable VALUES (1),(2),(3),(4),(5); CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable; SELECT a, rr FROM myView WHERE rr < 30 ORDER BY rr; Some outputs of the above through sqlite3 "sqlite3 < test-where-random.sql": = 2|-86 4|-60 1|59 5|5 3|66 5|-83 4|30 1|64 Please remember that the "random" function here is only a detail the real problem is the "column expression" been reevaluated more than once. Cheers ! ? ?
[sqlite] order by not working in combination with random()
At 09:14 28/08/2015, you wrote: >--- > > Looks like "How many zillion devices are going to misbehave if this > is fixed?" > >We will find out, because SQLite was changed two days ago: >http://www.sqlite.org/cgi/src/info/c2f3bbad77850468 >--- Fine, let's sit down and watch the world collapse. No, that won't happen. -- jcd
[sqlite] Version Control
On Fri, Aug 28, 2015 at 9:56 AM, Scott Doctor wrote: > Slightly off topic, but I am looking at version control systems. SQLite > looks like it is using Fossil. How does Fossil compare to using Git, > Mercurial, or Subversion? > http://fossil-scm.org/xfer/doc/trunk/www/fossil-v-git.wiki ? --DD
[sqlite] Performance problems on windows
Hello Jakub, Windows user here. I use Sqlite to contain some fairly bulky data but the tables that store this data are simple and the queries and indexes are also simple. Using a 300 meg test file, I fed data into fresh DB's using different versions of Sqlite including some of the ones you listed in your testing. In all cases the latest version was marginally faster. Say 3 seconds in a 75 second run. Since we don't use the same DB's, I'm not surprised I couldn't reproduce your results but, it does suggest that raw speed to disk is close between the versions. I tried without journal, with journal and WAL mode.WAL mode was slightly faster than journal mode. Not using a journal file at all knocked 18 seconds off a run. I was able to knock 10 seconds off the feed in all modes by increasing my Sqlite cache size to 10,000 pages. My users had reported that older versions were faster than new too but my testing suggests, even if it's true, the problem isn't Sqlite in my case. I see your new post about multiple threads. Typically my access pattern is one thread at a time so, again my testing probably doesn't apply to your particular case. C Wednesday, August 26, 2015, 7:51:08 AM, you wrote: JZ> Hi, JZ> also nope. I start with an empty (completely new) database. Then JZ> inserted 100K what we call "objects" and the same amount of JZ> "events". Inserting "events" takes place in one big commit. As JZ> soon as events are detected, the processing starts. I measure time JZ> since beginning of the first process till the end of the last. It JZ> is quite a lot of transactions, selects, and updates. Slightly less inserts and deletions. JZ> As a rule we do a VACUUM at each startup but clients expect to JZ> run this thing for months (if not years) without restart. JZ> -- JZ> Gruesse, JZ> Jakub JZ> -Original Message- JZ> From: sqlite-users-bounces at mailinglists.sqlite.org JZ> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Rowan Worth JZ> Sent: Mittwoch, 26. August 2015 13:33 JZ> To: General Discussion of SQLite Database JZ> Subject: Re: [sqlite] Performance problems on windows JZ> What about fragmentation of the database itself? Does running JZ> VACUUM on the database affect performance? JZ> -Rowan JZ> On 26 August 2015 at 16:16, Jakub Zakrzewski wrote: >> Hi, >> >> nope. The defragmentation job runs every Wednsday night and the >> fragmentation is very low. >> >> PS: I'm putting together a small program to replay the queries I got >> from sqlite trace. If that will behave the same way as the original, >> I'll post it here. >> >> -- >> Gruesse, >> Jakub >> >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: >> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott >> Robison >> Sent: Dienstag, 25. August 2015 18:55 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Performance problems on windows >> >> On Aug 25, 2015 5:21 AM, "Jakub Zakrzewski" wrote: >> > >> > Hi, >> > >> > The slowdown is confirmed by one of our customers. He uses Win 2008 >> Server x64. I'm testing on Win7 x64. >> >> This might be obvious in which case my apologies for bringing it up but: >> are these systems demonstrating slowness perhaps heavily fragmented? >> Either the database is fragmented, other non-SQLite files required by >> the system, or especially the master file table? I've seen truly >> abysmal performance on such systems myself, depending on IO patterns. >> >> > >> > I'm sure that you're testing each release carefully. We do too. And >> > we >> still have weird bugs ;) >> > >> > Thanks for interest. If I can provide any useful information, just >> > tell >> me, what you need. I cannot reveal the source code but some profiling >> results or SQL statements are not a secret. >> > >> > -- >> > Gruesse, >> > Jakub >> > >> > -Original Message- >> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: >> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard >> Hipp >> > Sent: Dienstag, 25. August 2015 03:19 >> > To: General Discussion of SQLite Database >> > Subject: Re: [sqlite] Performance problems on windows >> > >> > I don't have any clues. >> > >> > While most of our work happens on Linux, we do measure performance >> > on >> Windows from one release to the next (see for example item 23-b on the >> most recent release check-list >> > https://www.sqlite.org/checklists/private/3081100/index#c23) and it >> > gets >> better from one release to the next. So I don't know why you are >> seeing a slowdown. >> > >> > How do you measure? >> > >> > On 8/24/15, Jakub Zakrzewski wrote: >> > > Hi All, >> > > >> > > I finally got a chance to upgrade SQLite for our product from >> > > ancient 3.7.16.2. Initial tests on Linux were very promising - >> > > ranging from 33% to even 300% (for one degenerated case) speed >> > > improvement. So far >>
[sqlite] order by not working in combination with random()
Jean-Christophe Deschamps wrote: > At 21:11 27/08/2015, you wrote: >> I think it still comes back to my earlier comment: Would changing it to >> behave more like the most common / expected outcome above be a breaking >> change? > > Looks like "How many zillion devices are going to misbehave if this is fixed?" We will find out, because SQLite was changed two days ago: http://www.sqlite.org/cgi/src/info/c2f3bbad77850468 Regards, Clemens
[sqlite] Insertion into the large DB drastically slows down at some point
Yuri wrote: > I followed every advise I could find: > * Database is written into memory > * Whole insertion happens in one transaction in one giant BEGIN TRANSACTON; > ... END TRANSACTION; block. > * Foreign keys are deferred: PRAGMA defer_foreign_keys=ON; > * Journal is disabled: PRAGMA journal_mode = OFF; > * Synchronous mode is disabled: PRAGMA synchronous = OFF; On modern PCs, SQLite's page cache does not have a large effect because of the file cache of the OS, but you should do, e.g., PRAGMA cache_size = -100; for a 1 GB cache (default is only a few MB). Regards, Clemens
[sqlite] WHERE clause not working in combination with random()
OK, I am truly grateful for what I have learned in this thread! And I have composed an new, personal, rule: don't use a function in _anything_ other than in the column list portion of a SELECT statement. If necessary, this means I will be using a CREATE TEMPORARY TABLE results type construct when I need to ORDER BY or WHERE using a "function". Such as: BEGIN TRANSACTION; DROP TABLE __results; CREATE TEMPORARY TABLE __results AS SELECT ...columns..., ...functions... FROM sometable WHERE ...only have column names...; SELECT * FROM __results ORDER BY WHERE ..columns containing results of functions... ; COMMIT TRANSACTION; Does anybody see where this will fail (other than, perhaps disk space or an existing real table called __results already existing)? I agree is will likely not perform as well as it could. But, personally, I will embrace lower performance for correctness (I.e. getting what I really want). On Fri, Aug 28, 2015 at 4:20 AM, Domingo Alvarez Duarte < sqlite-mail at dev.dadbiz.es> wrote: > Now that we have solved the expression column reevaluation problem on the > "ORDER BY" clause what about the same problem on the "WHERE" clause ? > > CREATE TABLE myTable (a INTEGER); > INSERT INTO myTable VALUES (1),(2),(3),(4),(5); > CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable; > SELECT a, rr FROM myView WHERE rr < 30 ORDER BY rr; > > Some outputs of the above through sqlite3 "sqlite3 < > test-where-random.sql": > > > = > > 2|-86 > 4|-60 > 1|59 > > > 5|5 > 3|66 > > > > 5|-83 > 4|30 > 1|64 > > > Please remember that the "random" function here is only a detail the real > problem is the "column expression" been reevaluated more than once. > > Cheers ! > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Insertion into the large DB drastically slows down at some point
On 2015-08-28 04:15 AM, Yuri wrote: > I build a fairly large DB, with major tables having 800k..5M rows, > with several relationships between tables. > > At ~30% into the insertion process it slows down rapidly. Records > #171k..172k are still fast, and records #172k...173k are already ~10 > times slower. (all records are more or less similar) > > During Insertion process, selects are also performed to obtain keys to > insert into other tables. On the point of inserting and selecting to get keys.. you don't /have/ to let the autoincrement or primary key assign keys, if I do bulk inserts, I check the once what the next key should be (highest key + 1) and then simply count up from there inserting every next item with it's next key which I then don't need to select to find. Much faster this way. Check extra Indices and Triggers are not slowing things down. Also - I am not sure that turning the Journal off saves much for that size transaction. The cache will spill to disk anyway. Alternatively, break it into several smaller transactions, maybe doing just 100k inserts at a time. Best of luck, Ryan > > I followed every advise I could find: > * Database is written into memory > * Whole insertion happens in one transaction in one giant BEGIN > TRANSACTON; ... END TRANSACTION; block. > * Foreign keys are deferred: PRAGMA defer_foreign_keys=ON; > * Journal is disabled: PRAGMA journal_mode = OFF; > * Synchronous mode is disabled: PRAGMA synchronous = OFF; > > Plan for each "select" statement shows that it uses an index or > primary key. Every insert statement is a simple insert "insert into > xxx(x,x,x) values(?,?,?)" Selects are also all simple one-table > selects. All statements used in prepared form. > > How can I understand why the slowdown occurs? Especially, why the > slowdown in so "sharp"? > > Something drastic happens, like some strategy is recomputed, some > index is rebuilt, etc... > > Thank you, > Yuri > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Insertion into the large DB drastically slows down at some point
On 28 Aug 2015, at 3:45am, Yuri wrote: > My computer has 24GB ob RAM of which 4GB is free. At the time of the problem > the size of the process is 325MB. And the size of complete DB is ~250MB. So > this isn't it. > > What's worth mentioning though is that at the time of the event in question > size of the process increases by 5MB. It seems we need someone who knows how SQLite creates databases in memory. Do you open this database as ':memory:' ? I don't know why the process suddenly gets bigger. I don't think anything in the SQLite programming is doing it. There are many mysterious things going on in modern operating systems and I only know Macintosh well enough to make guesses about why it does things. Simon.
[sqlite] Insertion into the large DB drastically slows down at some point
On 28 Aug 2015, at 3:15am, Yuri wrote: > * Database is written into memory > [...] > How can I understand why the slowdown occurs? Especially, why the slowdown in > so "sharp"? Your computer has a certain amount of free memory. Once your database is bigger than that size the computer has to keep moving parts of the database into storage so it has room for the new data. I will guess that if you got more RAM in your computer you would have faster operations for longer. Simon.
[sqlite] Version Control
Slightly off topic, but I am looking at version control systems. SQLite looks like it is using Fossil. How does Fossil compare to using Git, Mercurial, or Subversion? - Scott Doctor scott at scottdoctor.com -
[sqlite] autoincrement field
On Thu, 27 Aug 2015 23:40:15 +0200 Jean-Christophe Deschamps wrote: > http://www.sqlite.org/c3ref/last_insert_rowid.html is what you need. Yes, thanks a lot! Lev
[sqlite] Insertion into the large DB drastically slows down at some point
Thanks to everybody who made suggestions. There was the legitimate constraint violation caused by a bug in an importing program. Program was erroneously inserting zero integer into the field that is both the leading part of the primary key (possibly causing its non-uniqueness), and a foreign key (definitely causing its violation). This triggered the slowdown behavior. I can't understand why exactly, because the primary key should have failed immediately, and the foreign key was deferred. But that's what happened. Yuri
[sqlite] autoincrement field
At 23:25 27/08/2015, you wrote: >--- >I have a table structure like this: > >CREATE TABLE padstack ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > pin_number INTEGER, > name TEXT >); > >Is there any way to get the 'id' of newly inserted row? My insert of >course >not contains the 'id' field. >--- http://www.sqlite.org/c3ref/last_insert_rowid.html is what you need.
[sqlite] order by not working in combination with random()
At 21:11 27/08/2015, you wrote: > > There are 2 distinct and volontary function invokations, so I don't see > > how SQL engine would decide not to perform the second call. > >Agreed, though I'm pretty sure I've read messages in this thread at >advocate the same function should return the same value when called >multiple times in a single select statement. While this does make sense for datetime functions, it doesn't really prove helpful with especially random(), but it turns to nonsense for generic user-defined function as long as SQL doesn't have the faintest clue whet it does. That means external function, not TSQL or friends. >Using sqlfiddle.com I'm testing the following: > >create table blah(a int); >insert into blah values(1),(2),(3); >select rand(), rand() as b from blah order by b; > >where rand() is whatever random number function the platform in question >provides. Various engines may find it clever to treat the engine's rand() in a particular way. But the real question is: how do they process user-defined external function fct()? >SQL Server 2008: same row three times; rand() is computed at the beginning >of the query and cached. Which makes for decidedly non-random numbers and >doesn't seem very useful IMO. Don't trick me into commenting microsoftisms ;-) >MySQL 5.6 returns three different rows, and it seems to be sorted on >b. Six >invocations of the query always resulted in the set being sorted on b, so >I'm going to assume it worked as expected without generating a third >random >number. >PostgreSQL 9.3: same result as MySQL 5.6. > >Oracle 11g R2: same result as MySQL 5.6. Sounds logical. >I think it still comes back to my earlier comment: Would changing it to >behave more like the most common / expected outcome above be a breaking >change? Looks like "How many zillion devices are going to misbehave if this is fixed?" vs. "Now developpers will be certain that such constructs don't produce non-sensical results any more and will stop using application-level kludges instead". I would give the second possibility a chance because the issue seem to have previously surfaced only about datetime functions and indeed the construct is not the most common. The mere fact that PG, Oracle and MySQL converge on this point makes that the most sensible thing to do. But who am I?
[sqlite] autoincrement field
I have a table structure like this: CREATE TABLE padstack ( id INTEGER PRIMARY KEY AUTOINCREMENT, pin_number INTEGER, name TEXT ); Is there any way to get the 'id' of newly inserted row? My insert of course not contains the 'id' field. Thanks, Lev
