Very slow to serialize large DBInterface (DuckDB) query result

[grantmcdermott]

Originally raised at JuliaData/DataFrames.jl#3444 but moving here as it seems to be an upstream issue.

Hi folks.

I recently gave a workshop where I was extolling the virtues of DuckDB as a (very) fast universal backend, where you can pass the same underlying SQL string to different front-ends in R, Python, Julia, etc. However, I encountered an unexpected performance lag in Julia compared to these other front-ends; namely the serialization of a large query result into a "native" Julia object.

A quick example using a subset of NYC taxi data:

Julia

using BenchmarkTools, DataFrames, DuckDB
con = DBInterface.connect(DuckDB.DB, ":memory:")

While the main query executes quickly on the DuckDB backend...

@elapsed nyc = DBInterface.execute(
    con,
    "
    FROM 'nyc-taxi/**/*.parquet'
    SELECT *
    WHERE year = 2012 AND month <= 3 
    "
    ))
# 9.868555386

... actually displaying or coercing the result into a "native" Julia DataFrame takes another 2.5 minutes on my laptop.

@elapsed nyc = DataFrame(nyc)
153.228785959

DBInterface.close!(con)

I'll note that even printing the queried nyc object (i.e., before coercing it to a DataFrame) takes just about the same amount of time as coercing it to a DataFrame. So it seems to be an upstream DBInterface serialization issue rather than particular to DataFrames.jl or duckdb.jl.

R (for comparison)

For comparison, serializing the same query to a standard R data frame takes less than 20 seconds (accounting for the actual query run time).

library(duckdb)

con = dbConnect(duckdb(), shutdown = TRUE)

tic = Sys.time()
nyc = dbGetQuery(
   con,
   "
   FROM 'nyc-taxi/**/*.parquet'
   SELECT *
   WHERE year = 2012 AND month <= 3 
   "
)
(toc = Sys.time() - tic)
# Time difference of 26.81664 secs

P.S. I'll be the first to admit that this particular MWE may not be the most compelling given the lack of actual querying. After all, doing those aggregations is usually the primary goal of a database backend like DuckDB. The resulting data frame here is over 45m rows deep (and 20 columns wide), whereas we'd normally expect to be pulling into smaller query results after aggregations etc. But I'm still surprised by the extent of the gap and I hope that it can be reduced. Thanks!