Re^2: [OT] The Long List is Long resurrected

I finally had a few minutes of time thinking about the LLIL problem and looked into PostgreSQL for a solution.

Thank you for the PostgreSQL solution. I changed the ORDER BY clause to match the OP's requirement.

INSERT INTO llil_result (name, count)
    SELECT name, sum(count) AS total FROM llil_raw GROUP BY name ORDER
+ BY total desc, name;
[download]

Results from Linux installed on a USB 3.0 drive.

$ /usr/bin/postgres -D /var/lib/pgsql/data -h localhost

$ time psql -d test_db -f llil.sql
CREATE TABLE
COPY 3515200
COPY 3515200
COPY 3515200
CREATE TABLE
INSERT 0 10367603
COPY 10367603
DROP TABLE
DROP TABLE

real    0m52.508s
user    0m0.001s
sys     0m0.001s
[download]

Results from the DB residing in memory, /tmp/data location.

$ /usr/bin/postgres -D /tmp/data -h localhost

$ time psql -d test_db -f llil.sql 
CREATE TABLE
COPY 3515200
COPY 3515200
COPY 3515200
CREATE TABLE
INSERT 0 10367603
COPY 10367603
DROP TABLE
DROP TABLE

real    0m49.396s
user    0m0.001s
sys     0m0.002s
[download]

See also, SQLite solution. The results there were captured on Clear Linux. Here, on Ubuntu Linux 24.04. The Sort::Packed module is used for sorting the output.

$ perl -I ~/perl5/lib/perl5/ llilsql.pl --threads=1 big{1,2,3}.txt | c
+ksum
Tie::Hash::DBD SQLite database - start
fixed string length=12, threads=1, maps=32
get properties   :    41.352 secs
pack properties  :     5.897 secs
sort packed data :     1.164 secs
write stdout     :     3.602 secs
total time       :    52.017 secs
    count lines  : 10545600
    count unique : 10367603
2956888413 93308427

$ perl -I ~/perl5/lib/perl5/ llilsql.pl --threads=8 big{1,2,3}.txt | c
+ksum
Tie::Hash::DBD SQLite database - start
fixed string length=12, threads=8, maps=32
get properties   :     5.572 secs
pack properties  :     0.856 secs
sort packed data :     1.173 secs
write stdout     :     0.719 secs
total time       :     8.323 secs
    count lines  : 10545600
    count unique : 10367603
2956888413 93308427

$ perl -I ~/perl5/lib/perl5/ llilsql.pl --threads=16 --maps=64 big{1,2
+,3}.txt | cksum
Tie::Hash::DBD SQLite database - start
fixed string length=12, threads=16, maps=64
get properties   :     3.140 secs
pack properties  :     0.533 secs
sort packed data :     1.175 secs
write stdout     :     0.395 secs
total time       :     5.247 secs
    count lines  : 10545600
    count unique : 10367603
2956888413 93308427
[download]

Comment on Re^2: [OT] The Long List is Long resurrected Select or Download Code


The stupid question is the question not asked
	PerlMonks