> From: [email protected]
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
