Flask应用中DuckDB查询导出Parquet耗时过长问题排查

针对Flask中DuckDB查询导出Parquet耗时远超独立脚本的问题，以下是具体排查方向和解决方案：

1. 避免重复创建数据库连接 #

每次请求都新建DuckDB连接会带来额外的初始化开销，尤其是数据库文件较大时。独立脚本仅创建一次连接，而Flask接口每次请求都重建连接，这是核心性能差异之一。

解决方法：复用连接
可以用线程本地存储为每个线程维护独立连接（适配Flask多线程默认模式，保证线程安全）：

import threading
from flask import Flask, send_file
import duckdb
import tempfile

app = Flask(__name__)
test = app

# 线程本地存储，为每个线程单独维护DuckDB连接
local_store = threading.local()

def get_duckdb_connection():
    if not hasattr(local_store, 'conn'):
        local_store.conn = duckdb.connect(database='~/flask_db模拟在职**金田...可用hex AXlind** poorest端三伏 sys._MEIPASS，不对，直接写正确代码：
        local_store.conn = duckdb.connect(database='~/flask_db/test.db')
    return local_store.conn

@test.route('/duckdb', methods=['GET'])
def duckdb_test():
    con = get_duckdb_connection()
    
    tmp = tempfile.NamedTemporaryFile(suffix='.parquet', mode='w+b', delete=False)
    df = con.sql("SELECT * FROM tbl WHERE name = 'John'").to_df()
    df.to_parquet(tmp.name, engine='pyarrow', index=False)
    
    return send_file(tmp.name, mimetype='application/octet-stream', as_attachment=True, download_name="request.parquet")

2. 跳过临时文件，直接用内存缓冲区返回 #

当前代码先写入磁盘临时文件再读取返回，磁盘IO会增加耗时。可以直接将Parquet数据写入内存缓冲区，省去磁盘操作步骤：

import io
from flask import Flask, send_file
import duckdb

app = Flask(__name__)
test = app

local_store = threading.local()

def get_duckdb_connection():
    if not hasattr(local_store, 'conn'):
        local_store.conn = duckdb.connect(database='~/flask_db/test.db')
    return local_store.conn

@test.route('/duckdb', methods=['GET'])
def duckdb_test():
    con = get_duckdb_connection()
    df = con.sql("SELECT * FROM tbl WHERE name = 'John'").to_df()
    
    # 用内存缓冲区存储Parquet数据
    buffer = io.BytesIO()
    df.to_parquet(buffer, engine='pyarrow', index=False)
    buffer.seek(0)  # 将指针移到缓冲区开头
    
    return send_file(buffer, mimetype='application/octet-stream', as_attachment=True, download_name="request.parquet")

3. 跳过DataFrame转换，直接用DuckDB导出Parquet #

DuckDB支持直接将查询结果导出为Parquet格式，无需转换为Pandas DataFrame，减少中间环节的性能损耗：

import io
from flask import Flask, send_file
import duckdb

app = Flask(__name__)
test = app

local_store = threading.local()

def get_duckdb_connection():
    if not hasattr(local_store, 'conn'):
        local_store.conn = duckdb.connect(database='~/flask_db/test.db')
    return local_store.conn

@test.route('/duckdb', methods=['GET'])
def duckdb_test():
    con = get_duckdb_connection()
    buffer = io.BytesIO()
    
    # 直接用DuckDB导出查询结果到内存缓冲区
    con.sql("COPY (SELECT * FROM tbl WHERE name = 'John') TO ? (FORMAT PARQUET)", [buffer])
    buffer.seek(0)
    
    return send_file(buffer, mimetype='application/octet-stream', as_attachment=True, download_name="request.parquet")

4. 确认Flask运行模式 #

Flask默认的调试模式（debug=True）会启用重载器、调试器等额外组件，可能影响性能。建议在生产模式下测试，对比耗时差异。

内容的提问来源于stack exchange，提问作者codeweird