Repository navigation
Prepared statements on DuckDB's Python Client increases execution time almost 10X #300
Closed as duplicate
Description
What happens?
Note: This was tested using DuckDB with several extensions loaded (cache_httpfs, parquet, DuckLake, observefs)
After a change we started using prepared statements (in particular we are using user input on our queries and wanted to protect our data against SQL inyection attacks) we noticed a severe slow down on our system. Tracing the issue we figured out that our DuckDB SQL statements were 10 times more slow after the patch, and after testing the queries with and without using params our conclusion is that using params is ten times slower than a simple query
To Reproduce
#DuckDB connection creation
self.connections[thread_id] = duckdb.connect(database)
self.connections[thread_id].install_extension("parquet")
self.connections[thread_id].install_extension("observefs", repository="community")
self.connections[thread_id].install_extension("cache_httpfs", repository="community")
self.connections[thread_id].install_extension("ducklake")
self.connections[thread_id].install_extension("postgres")
self.connections[thread_id].load_extension("ducklake")
self.connections[thread_id].load_extension("cache_httpfs")
self.connections[thread_id].load_extension("observefs")
self.connections[thread_id].load_extension("postgres")
self.connections[thread_id].execute("SET cache_httpfs_type='on_disk'")
self.connections[thread_id].execute("SET cache_httpfs_cache_directory='/tmp/general_cache_httpfs/'")
self.connections[thread_id].execute("SET cache_httpfs_disk_cache_reader_enable_memory_cache=true")
self.connections[thread_id].execute("SET enable_external_file_cache=true")
self.connections[thread_id].execute("SET enable_http_metadata_cache=true")
self.connections[thread_id].load_extension("parquet")
self.connections[thread_id].execute("CREATE OR REPLACE SECRET secret (" \
+"TYPE s3," \
+"PROVIDER config," \
+"KEY_ID '" + os.environ.get("AWS_S3_ACCESS_KEY_ID", "") + "'," \
+"SECRET '" + os.environ.get("AWS_S3_SECRET_ACCESS_KEY", "") + "'," \
+"REGION '" + os.environ.get("AWS_REGION", "us-east-1") + "'" \
+")"
)
self.connections[thread_id].execute("CREATE OR REPLACE SECRET ducklake_postgres (" \
+"TYPE postgres," \
+"HOST '" + settings.DATABASES['default']['HOST'] + "'," \
+"DATABASE '" + settings.DATABASES['default']['NAME'] + "'," \
+"USER '" + settings.DATABASES['default']['USER'] + "'," \
+"PASSWORD '" + settings.DATABASES['default']['PASSWORD'] + "'," \
+"PORT " + settings.DATABASES['default']['PORT'] \
+")"
)
self.connections[thread_id].execute("CREATE OR REPLACE SECRET columns_ducklake (" \
+"TYPE ducklake," \
+f"DATA_PATH 's3://" + settings.AWS_STORAGE_BUCKET_NAME + f"/{os.environ.get("DUCKLAKE_FOLDER", "ducklake")}/'," \
+"METADATA_PATH ''," \
+"METADATA_PARAMETERS MAP { "\
+"'TYPE': 'postgres',"\
+"'SECRET': 'ducklake_postgres',"\
+"}" \
+")"
)
self.connections[thread_id].execute(f"ATTACH 'ducklake:columns_ducklake' AS datalake")
self.connections[thread_id].execute("USE datalake")
self.connections[thread_id].execute("CALL datalake.set_option('delete_older_than', '1d')")
self.connections[thread_id].execute("CALL datalake.set_option('expire_older_than', '1d')")
# where the SQL query is a simple filter over a big parquet file on AWS S3 (200MB) or a ducklake table with S3 as backend partitioned by chunks (same big parquet file but uploaded using ducklake)
filtered_numpy_dict = duckdbConn.sql(sql_query).fetchnumpy()
#This takes 0.9 seconds
# Same SQL query but now with a prepared statement and a dictionary with all values (user input mostly)
filtered_numpy_dict = duckdbConn.sql(sql_query, params=values_dict).fetchnumpy()
#This takes 10 seconds
OS:
Linux/AMD64 Debian Trixie
DuckDB Version:
1.4.4
DuckDB Client:
Python
Hardware:
Docker running on AWS ECS, Mac M4
Full Name:
Fabián Alexis Bravo Abarca
Affiliation:
AlicantoLabs
Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?
- Yes, I have
Did you include all code required to reproduce the issue?
- Yes, I have
Did you include all relevant data sets for reproducing the issue?
No - I cannot share the data sets because they are confidential
Activity
szarnyasg commentedon Feb 6, 2026
Hi @FabianBravoA, thanks for opening this issue. Are you certain that the slowdown is related to prepared statements? If so, could you please provide a minimal example that does not rely on additional extensions? If not, can you please provide a full MWE?
FabianBravoA commentedon Feb 6, 2026
Hi @szarnyasg , I'm certain that this issue is directly related to the use of "params" in the sql method, because of the logic we're following on our code sometimes the dict passed to the sql function is empty and even in that case there's a significant slow down (sometimes bigger than 10X).
Also, I tried to use duckdb 1.4.2, 1.4.3 with the same odd result. My workaround was to cleanup user input before passing the SQL query and avoid using "params" in the sql method, seeing that after the fix everything is running way faster with the same data (and that's the only difference).
I will try to make a MWE using public data.
evertlammerts commentedon Feb 6, 2026
This is part of at least two related issues: #238 and #169
It's on my list to scope this out further and figure out what we can do to fix. Stay tuned.
FabianBravoA commentedon Feb 9, 2026
@szarnyasg do you still need a MWE? I'm asking because @evertlammerts already pointed out two issues that seem to be the root cause of this particular issue.
I'm checking with the company I'm working for if I can share some of the datasets we're using for testing.
evertlammerts commentedon Feb 11, 2026
Closing as duplicate of #238
See this comment for an explanation.