polars-cli vs Qsv sqlp

[13minutes-yt]

I have been experimenting with polars-cli https://github.com/pola-rs/polars-cli as compared to sqlp. The only drawback is that polars-cli does not accept stdin. The one that strikes me is a querry I was doing on NYyellowtaxi file of more than 7million rows. There was a difference in performance between the two.
These are the results I got using both.

time polars -o csv "select VendorID,sum(total_amount) from read_csv('taxi.csv') group by VendorID "
VendorID,total_amount
2,89959869.13016371
1,52377417.52984266
4,600584.6100000198

real	0m0.530s
user	0m3.448s
sys	0m0.207s

vs

time qsv sqlp -Q taxi.csv "select VendorID,sum(total_amount) from taxi group by VendorID "
VendorID,total_amount
1,52377417.53018965
4,600584.6100000076
2,89959869.13093829

real	0m19.309s
user	0m22.352s
sys	0m0.239s

Any suggestions why the difference

[ondohotola]

Can you post the source link for the file?

[13minutes-yt]

I renamed it a long time ago. I think this one. https://github.com/DataTalksClub/nyc-tlc-data/releases/download/yellow/yellow_tripdata_2019-04.csv.gz

[ondohotola]

If I am bored enough tomorrow (public holiday) I’ll play with it :-)-O thanks

[ondohotola]

CSVQ takes about 2 minutes :-(-O

[ondohotola]

Some hyperfine reports:

Benchmark 1: polars -o csv "select VendorID,sum(total_amount) from read_csv(taxi.csv) group by VendorID order by VendorID "
  Time (mean ± σ):      12.2 ms ±   0.5 ms    [User: 2.8 ms, System: 1.7 ms]
  Range (min … max):    11.3 ms …  13.9 ms    189 runs

Benchmark 1: duckdb -c "select VendorID,sum(total_amount) from read_csv(taxi.csv) group by VendorID order by VendorID"
  Time (mean ± σ):     604.9 ms ±   9.2 ms    [User: 4089.1 ms, System: 249.5 ms]
  Range (min … max):   594.8 ms … 623.3 ms    10 runs

Benchmark 1: qsv sqlp -Q taxi.csv "select VendorID,sum(total_amount) from taxi group by VendorID order by VendorID"
  Time (mean ± σ):     13.980 s ±  0.119 s    [User: 16.269 s, System: 0.546 s]
  Range (min … max):   13.831 s … 14.155 s    10 runs

[jqnatividad]

@13minutes-yt , this is because qsv loads and parses the CSV separately before executing the SQL and this is not exactly an apples-to-apples comparison as the queries are different and you're also bypassing the CSV parsing for the polars-cli.

However, if we also use read_csv directly in the qsv SQL query similar to the polars-cli query, and just pass a small, dummy csv as input, we get similar performance as sqlp also leverages the magic of Polars LazyFrames in reading only what it needs from the CSV to fulfill the query, i.e.

/usr/bin/time qsv sqlp -Q smalldummy.csv "select VendorID,sum(total_amount) from read_csv('taxi.csv') group by VendorID order by VendorID"
VendorID,total_amount
1,52377417.52985942
2,89959869.13054822
4,600584.610000027
        0.38 real         0.95 user         0.19 sys

/usr/bin/time qsv sqlp -Q taxi.csv "select VendorID,sum(total_amount) from taxi group by VendorID order by VendorID"                                
VendorID,total_amount
1,52377417.52985942
2,89959869.13054822
4,600584.610000027
        6.86 real         7.41 user         0.17 sys

[jqnatividad]

Given this finding, I'm thinking of changing sqlp to leverage read_csv when executing queries with just one input CSV...

As apart from supporting stdin @13minutes-yt , sqlp also supports extended input processing and multi-statement SQL scripts.

[ondohotola]

If there is a huge file to be processed, we can just produce an empty temporary file before the pipe and remove it afterwards.

Would be kind of cool if one could dispense with that, however.

[jqnatividad]

Hi @13minutes-yt , @ondohotola ,
Just wanted to let you know that I made a "fast-path" optimization to automatically convert the SQL query to use the read_csv SQL function when 1) there is only one input CSV, 2) there are no CSV parsing options used, and 3) the SQL query is not a ".sql" file. :)

So no need to create a dummy input CSV file!

You can always force sqlp to read and parse the CSV the old way by using the --no-optimizations option.

6490cbb

[13minutes-yt]

I am using two csv files as in this example

qsv sqlp -Q dummy.csv  "select index1,date1,value1,s.sand as sand 
 						FROM read_csv('/tmp/ant.csv') e 
						LEFT JOIN read_csv('sand.csv') s on e.value1=s.value"

I can use whichever file instead of dummy.csv. shall I pick the bigger file.

[jqnatividad]

For the current 0.122.0 release, just use a standard dummy.csv (1 column, 1 row CSV) file as its never used anyway in the SQL query.

FYI, 0.123.0 release will definitely happen this weekend in anticipation of Open Data Day next week...

[ondohotola]

Now on Silicon

head -1 taxi.csv > smalldummy.csv

and then hyperfine tests:

Benchmark 1: polars -o csv "select VendorID,sum(total_amount) from read_csv(taxi.csv) group by VendorID order by VendorID "
Time (mean ± σ): 1.8 ms ± 0.1 ms [User: 1.1 ms, System: 0.4 ms]
Range (min … max): 1.5 ms … 2.4 ms 648 runs

Benchmark 1: qsv sqlp -Q smalldummy.csv "select VendorID,sum(total_amount) from read_csv(taxi.csv) group by VendorID order by VendorID"
Time (mean ± σ): 4.3 ms ± 0.3 ms [User: 2.9 ms, System: 1.0 ms]
Range (min … max): 4.0 ms … 9.3 ms 439 runs

Benchmark 1: qsv sqlp -Q taxi.csv "select VendorID,sum(total_amount) from taxi group by VendorID order by VendorID"
Time (mean ± σ): 7.659 s ± 2.563 s [User: 8.435 s, System: 0.143 s]
Range (min … max): 6.822 s … 14.953 s 10 runs

Benchmark 1: duckdb -c "select VendorID,sum(total_amount) from read_csv(taxi.csv) group by VendorID order by VendorID"
Time (mean ± σ): 327.5 ms ± 3.6 ms [User: 1750.2 ms, System: 78.5 ms]
Range (min … max): 320.1 ms … 333.2 ms 10 runs

I tried an empty smalldummy.csv which failed, which is expected, but it would have been nice to be able to use something like /dev/null

[13minutes-yt]

It is good that the discrepancy has been sorted out with the dummy-file workaround.thanks @jqnatividad for your help and enthusiasm for this project.QSV is amazing . I am trying to get to the full potential of QSV. Very interesting benchmarks @ondohotola .

[ondohotola]

Yesterday's were on an Intel MacPro (Late 2013), today's on a Silicon Macbook Air (2022)

[ondohotola]

Thank you. Message ID: ***@***.***>