Very slow to convert DBInterface (DuckDB) result

[grantmcdermott]

Hi folks.

I'm not sure if this belongs here or at DBInterface,jl (or DuckDB,jl or elsewhere), but let me try...

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" DataFrame 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 original 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 may be some upstream issue rather than particular to DataFrames.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

Now I'll be the first to admit that this particular MWE may not be the most compelling given the lack of actual querying. 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. After all, doing those aggregations is usually the primary goal of a database backend like DuckDB. But I'm still surprised by the extent of the gap and I hope that it can be reduced. Thanks.

[grantmcdermott]

Closing in favour of an upstream report JuliaDatabases/DBInterface.jl#48