On Thu, Mar 9, 2017 at 11:59 AM, Dominique Devienne <ddevie...@gmail.com> wrote:
> On Fri, Feb 10, 2017 at 10:46 AM, Dominique Devienne <ddevie...@gmail.com> > wrote: > >> What would be the [...] most efficient way to do this (NDR: thousand >> separator) in SQL? >> > > Here's a little demo that evaluates the 3 approaches proposed in this > thread. > > Formatting 10M numbers takes > ~2s using DRH's built-in, > ~10s using Edzard Pasma's string-based solution (thank you BTW!), > ~5s using my numeric-based solution. > > [...] > sqlite> select 'baseline'; > > sqlite> with num(n) as (select 1 union all select n+1 from num where n < > 1e7) > ...> select count(*) from num; > 10000000 > Run Time: real 3.986 user 3.978026 sys 0.000000 > It occurred to me that a better baseline would be below, which shifts all results down by 2s, thus the printf() overhead of the thousand separator itself is only around 200-300ms, to add almost 19M spaces / separators. --DD sqlite> with num(n) as (select 1 union all select n+1 from num where n < 1e7) ...> select sum(length( ...> cast(n as 'text') ...> )) from num; 68888897 Run Time: real 6.012 user 6.021639 sys 0.000000 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
