4 ms·
Recently was sorting a 10 million line CSV by the second field which was numerical. After an hour went by and it wasn't done, I poked around online and saw a su
by ikeboy 8y ago
Recently was sorting a 10 million line CSV by the second field which was numerical. After an hour went by and it wasn't done, I poked around online and saw a suggestion to put the field sorted on first.
One awk command later my file was flipped. Run same exact sort command on this but without specifying field. Completed in 12 seconds.
Morals:
1. Small changes can have a 3+ orders of magnitude effect on performance
2. Use the Google, easier than understanding every tool on a deep enough level to figure this out yourself ;)
- dorfsmay 8y agocsv files are extremely easy to import in postgres, and 10 M rows (assuming not very large) isn't much to compute even in a 6 or 7 year old laptop. Keep it in mind if you've got something slightly more complicated to analyse.
- hobs 8y agoIf SQL is your game but you dont want to get PG setup - try SQLite - wget "https://data.cityofnewyork.us/api/views/kku6-nxdu/rows.csv" rows.csv sqlite3 .mode csv .import ./rows.csv newyorkdata SELECT * FROM newyorkdata ORDER BY `COUNT PARTICIPANTS`;
- mxuribe 8y agoNice! Talk about being a productive engineer! Kudos!
- jillesvangurp 8y agoOr use csvkit ...
- hobs 8y agoOh nice, I hadn't seen this before, so a similar query would be a bit shorter! wget "https://data.cityofnewyork.us/api/views/kku6-nxdu/rows.csv" csvsql --query "SELECT * FROM newyorkdata ORDER BY `COUNT PARTICIPANTS`" rows.csv > new.csv
- ranit 8y agoWhat did you use to sort these 10 million lines?
- ikeboy 8y agosort in bash. Specific command: LC_ALL=c sort -n filename -r > output
- mynegation 8y agobut what did you use to sort by the second field, in your original setup?
- cmurphycode 8y agoThis is really interesting to me - I can't seem to reproduce this on a couple different platforms. In the past (and just now) I've definitely seen 100x differences with LC_ALL=C, but you used that on both, I'm sure. How large was the 10 million line file, and do you know whether sort was paging files to disk?
- ikeboy 8y ago