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!

[AUK1939]

What happens when you do this

@elapsed nyc = DataFrame(nyc, copycols=false)

Let me know if this improves performance

I've run into similar issues when trying to constuct julia DataFrames - Overall Pandas.read_sql is much faster (unless you use the above approach).

In one example reading from a Postgres database took 3.6s (reading roughly 3.4 million rows). But constructing the DataFrame took 31 seconds. This was strange because the same query in pandas takes 4.7s using read_sql (and this includes construction of the dataframe).

    conn = DBInterface.connect(LibPQ.Connection, connection_str)
    sql = """SELECT * FROM my_table WHERE my_column = 'XXX'"""
    stmt = DBInterface.prepare(conn, sql)
    results = DBInterface.execute(stmt)
    df = DataFrame(results)  # takes 31 seconds!

However with the above non copy approach I found the julia solution to be faster by a second than the pandas.read_sql method

df = DataFrame(result, copycols=false)  # takes micro seconds