On Fri, Feb 10, 2017 at 10:46 AM, Dominique Devienne <[email protected]>
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.
Regarding ease-of-use, no context, the order is then DRH, Edzard, and
myself dead-last.
DRH wins on all counts! ;) --DD
C:\Users\ddevienne>sqlite3.18.0rc
SQLite version 3.18.0 2017-03-06 20:44:13
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
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
sqlite> with num(n) as (select 1 union all select n+1 from num where n <
1e7)
...> select sum(length(
...> case
...> when n < 1000 then cast(n as text)
...> when n < 1000000 then printf("%d,%03d", n/1000, n%1000)
...> when n < 1000000000 then printf("%d,%03d,%03d", n/1000000,
n%1000000/1000, n%1000)
...> else printf("%d,%03d,%03d,%03d", n/1000000000,
n%1000000000/1000000, n%1000000/1000, n%1000)
...> end
...> )) from num;
87887899
Run Time: real 8.765 user 8.767256 sys 0.000000
sqlite> with num(n) as (select 1 union all select n+1 from num where n <
1e7)
...> select sum(length(
...> ltrim(substr(n,-9,3)||','||substr(n,-6,3)||','||substr(n,-3,3),'0,')
...> )) from num;
87887899
Run Time: real 14.631 user 14.632894 sys 0.000000
sqlite> with num(n) as (select 1 union all select n+1 from num where n <
1e7)
...> select sum(length(
...> printf('%,d', n)
...> )) from num;
87887899
Run Time: real 6.258 user 6.255640 sys 0.000000
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
