> From: itandet...@mvps.org > Jason Freshwater wrote: > > For a 1 million row table with 8 distinct values of c the query > > time on my system (pretty typical modern laptop using precompiled > > sqlite 3.6.18) is about 2.8 seconds. For my requirements this is > > a bit too slow... > > I believe SELECT DISTINCT is implemented by sorting. Internally, a resultset > is created as if by ORDER BY, then a loop compares each > record with the previous one. > > In your case, something like this might work better: > > create temp table d(c unique); > insert or ignore into d(c) select c from t1; > select c from d; > drop table d; >
Thanks, thats an interesting suggestion. Seems to give not quite 2x performance improvement 2.8 -> 1.6 s The create and drop contribute a small constant overhead. I guess the read all and hash method still seems to give the best performance with no code changes. Just seems a bit wrong to read all the data from the db out to the app... Jason _________________________________________________________________ Learn how to add other email accounts to Hotmail in 3 easy steps. http://clk.atdmt.com/UKM/go/167688463/direct/01/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users