Re: [sqlite] which of these is faster?

2014-03-14 Thread Max Vlasov 
On Fri, Mar 14, 2014 at 4:51 PM, Richard Hipp  wrote:

>>
> In the original problem, there was already an index on the term for which
> the min() was requested.
>.
> Whit your CTE-generated random integers, there is not an index on the
> values.  So "SELECT min(x) FROM..." does a linear search and "SELECT x FROM
> ... ORDER BY x LIMIT 1" does a sort.
>

I see, my fault, didn't notice the db was a concrete one
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] which of these is faster?

2014-03-14 Thread Richard Hipp 
On Fri, Mar 14, 2014 at 8:41 AM, Max Vlasov  wrote:

> On Thu, Mar 13, 2014 at 11:06 PM, Richard Hipp  wrote:
>
> >
> > Once you do that, you'll see that the opcode sequence is only slightly
> > different between the two.  They should both run at about the same speed.
> > I doubt you'll be able to measure the difference.
> >
> >
>
> Actually a comparatively long (10,000,000 elements) CTE for random
> integer generation shows difference 20 vs 38 seconds. I suppose pure
> min should use linear search while "order by" one uses temporal b-tree
> (exlain query also hints about this). Sure unless sqlite has some
> detection of "order by limit 1" pattern redirecting it to linear
> search.
>
>
In the original problem, there was already an index on the term for which
the min() was requested.  So with either query, SQLite would merely search
for the first non-NULL entry in the index and stop.  The specific sequence
of VDBE opcodes is slightly different, but both accomplish the same thing:
searching for the first non-NULL entry in the index and stopping.

Whit your CTE-generated random integers, there is not an index on the
values.  So "SELECT min(x) FROM..." does a linear search and "SELECT x FROM
... ORDER BY x LIMIT 1" does a sort.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] which of these is faster?

2014-03-14 Thread Max Vlasov 
On Thu, Mar 13, 2014 at 11:06 PM, Richard Hipp  wrote:

>
> Once you do that, you'll see that the opcode sequence is only slightly
> different between the two.  They should both run at about the same speed.
> I doubt you'll be able to measure the difference.
>
>

Actually a comparatively long (10,000,000 elements) CTE for random
integer generation shows difference 20 vs 38 seconds. I suppose pure
min should use linear search while "order by" one uses temporal b-tree
(exlain query also hints about this). Sure unless sqlite has some
detection of "order by limit 1" pattern redirecting it to linear
search.

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

Re: [sqlite] which of these is faster?

2014-03-13 Thread Richard Hipp 
On Thu, Mar 13, 2014 at 2:37 PM, Stephan Beal  wrote:

> Hi, all,
>
> i know this is probably splitting hairs, and i ask only out of curiosity,
> not because i'm looking to optimize at this level...
>
> Given a Fossil repository db (namely the event.mtime value, a Julian Day),
> which of the following is faster for finding the min/max value of that
> field:
>
> SELECT MIN(mtime) FROM event;
>
> or:
>
> SELECT mtime FROM event ORDER BY mtime LIMIT 1;
>
> My intuition says that the first one would be faster, but EXPLAIN tells me
> that #1 uses 21 ops where #2 uses 16. The EXPLAIN output means nothing to
> me, though - maybe those 16 represent more work:
>

The output of EXPLAIN looks much nicer if you do ".explain" first to set up
appropriate formatting.

Once you do that, you'll see that the opcode sequence is only slightly
different between the two.  They should both run at about the same speed.
I doubt you'll be able to measure the difference.


>
> sqlite> explain SELECT MIN(mtime) FROM event;
> 0|Trace|0|0|0||00|
> 1|Null|0|1|2||00|
> 2|Goto|0|17|0||00|
> 3|OpenRead|1|3207|0|k(2,nil,nil)|00|
> 4|Null|0|3|0||00|
> 5|Affinity|3|1|0|c|00|
> 6|SeekGt|1|12|3|1|00|
> 7|Column|1|0|5||00|
> 8|CollSeq|0|0|0|(BINARY)|00|
> 9|AggStep|0|5|1|min(1)|01|
> 10|Goto|0|12|0||00|
> 11|Next|1|7|0||01|
> 12|Close|1|0|0||00|
> 13|AggFinal|1|1|0|min(1)|00|
> 14|Copy|1|6|0||00|
> 15|ResultRow|6|1|0||00|
> 16|Halt|0|0|0||00|
> 17|Transaction|0|0|0||00|
> 18|VerifyCookie|0|657|0||00|
> 19|TableLock|0|2897|0|event|00|
> 20|Goto|0|3|0||00|
>
> (MAX() needs 2 fewer)
>
> sqlite> explain SELECT mtime FROM event ORDER BY mtime LIMIT 1;
> 0|Trace|0|0|0||00|
> 1|Noop|0|0|0||00|
> 2|Integer|1|1|0||00|
> 3|Goto|0|12|0||00|
> 4|OpenRead|2|3207|0|k(2,nil,nil)|00|
> 5|Rewind|2|10|2|0|00|
> 6|Column|2|0|3||00|
> 7|ResultRow|3|1|0||00|
> 8|IfZero|1|10|-1||00|
> 9|Next|2|6|0||01|
> 10|Close|2|0|0||00|
> 11|Halt|0|0|0||00|
> 12|Transaction|0|0|0||00|
> 13|VerifyCookie|0|657|0||00|
> 14|TableLock|0|2897|0|event|00|
> 15|Goto|0|4|0||00|
>
> (the MAX variant also needs 16)
>
> :-?
>
> --
> - 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] which of these is faster?

2014-03-13 Thread Stephan Beal 
On Thu, Mar 13, 2014 at 7:50 PM, Andreas Kupries
wrote:

> WIBNI regardless of which form is faster, the engine would detect and
> rewrite the slower into the other ?
>

i wouldn't quite expect the engine to figure that out, but of course would
be happy if it could.


> Note: Which is faster might depend on if we have an index on the mtime
> field or not.
>

Now that you mention it...

CREATE TABLE event(
  type TEXT,  -- Type of event: 'ci', 'w', 'e', 't', 'g'
  mtime DATETIME, -- Time of occurrence. Julian day.
  objid INTEGER PRIMARY KEY,  -- Associated record ID
  tagid INTEGER,  -- Associated ticket or wiki name tag
  uid INTEGER REFERENCES user,-- User who caused the event
  bgcolor TEXT,   -- Color set by 'bgcolor' property
  euser TEXT, -- User set by 'user' property
  user TEXT,  -- Name of the user
  ecomment TEXT,  -- Comment set by 'comment' property
  comment TEXT,   -- Comment describing the event
  brief TEXT, -- Short comment when tagid already seen
  omtime DATETIME -- Original unchanged date+time, or NULL
);
CREATE INDEX event_i1 ON event(mtime);


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

Re: [sqlite] which of these is faster?

2014-03-13 Thread Andreas Kupries 
On Thu, Mar 13, 2014 at 11:37 AM, Stephan Beal  wrote:
> Hi, all,
>
> i know this is probably splitting hairs, and i ask only out of curiosity,
> not because i'm looking to optimize at this level...
>
> Given a Fossil repository db (namely the event.mtime value, a Julian Day),
> which of the following is faster for finding the min/max value of that
> field:
>
> SELECT MIN(mtime) FROM event;
>
> or:
>
> SELECT mtime FROM event ORDER BY mtime LIMIT 1;

WIBNI regardless of which form is faster, the engine would detect and
rewrite the slower into the other ?

Note: Which is faster might depend on if we have an index on the mtime
field or not.

-- 
Andreas Kupries
Senior Tcl Developer
Code to Cloud: Smarter, Safer, Faster(tm)
F: 778.786.1133
andre...@activestate.com
http://www.activestate.com
Learn about Stackato for Private PaaS: http://www.activestate.com/stackato

EuroTcl'2014, July 12-13, Munich, GER
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] which of these is faster?

2014-03-13 Thread Stephan Beal 
Hi, all,

i know this is probably splitting hairs, and i ask only out of curiosity,
not because i'm looking to optimize at this level...

Given a Fossil repository db (namely the event.mtime value, a Julian Day),
which of the following is faster for finding the min/max value of that
field:

SELECT MIN(mtime) FROM event;

or:

SELECT mtime FROM event ORDER BY mtime LIMIT 1;

My intuition says that the first one would be faster, but EXPLAIN tells me
that #1 uses 21 ops where #2 uses 16. The EXPLAIN output means nothing to
me, though - maybe those 16 represent more work:

sqlite> explain SELECT MIN(mtime) FROM event;
0|Trace|0|0|0||00|
1|Null|0|1|2||00|
2|Goto|0|17|0||00|
3|OpenRead|1|3207|0|k(2,nil,nil)|00|
4|Null|0|3|0||00|
5|Affinity|3|1|0|c|00|
6|SeekGt|1|12|3|1|00|
7|Column|1|0|5||00|
8|CollSeq|0|0|0|(BINARY)|00|
9|AggStep|0|5|1|min(1)|01|
10|Goto|0|12|0||00|
11|Next|1|7|0||01|
12|Close|1|0|0||00|
13|AggFinal|1|1|0|min(1)|00|
14|Copy|1|6|0||00|
15|ResultRow|6|1|0||00|
16|Halt|0|0|0||00|
17|Transaction|0|0|0||00|
18|VerifyCookie|0|657|0||00|
19|TableLock|0|2897|0|event|00|
20|Goto|0|3|0||00|

(MAX() needs 2 fewer)

sqlite> explain SELECT mtime FROM event ORDER BY mtime LIMIT 1;
0|Trace|0|0|0||00|
1|Noop|0|0|0||00|
2|Integer|1|1|0||00|
3|Goto|0|12|0||00|
4|OpenRead|2|3207|0|k(2,nil,nil)|00|
5|Rewind|2|10|2|0|00|
6|Column|2|0|3||00|
7|ResultRow|3|1|0||00|
8|IfZero|1|10|-1||00|
9|Next|2|6|0||01|
10|Close|2|0|0||00|
11|Halt|0|0|0||00|
12|Transaction|0|0|0||00|
13|VerifyCookie|0|657|0||00|
14|TableLock|0|2897|0|event|00|
15|Goto|0|4|0||00|

(the MAX variant also needs 16)

:-?

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