Re: [sqlite] which of these is faster?
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
Re: [sqlite] which of these is faster?
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?
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?
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?
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?
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