PreparedStatement非常慢，但是手动查询很快 [英] PreparedStatement very slow, but manual query quick

问题描述

我从如下框架中准备了一份陈述:

I have a preparedstatement from a framework as followed:

SELECT OH.ORDER_ID, MAX(OS.STATUS_DATETIME) FROM public.ORDER_HEADER OH, public.ORDER_STATUS OS WHERE ((OH.ORDER_ID = OS.ORDER_ID AND OH.STATUS_ID = ? AND OS.STATUS_ID = ?)) GROUP BY OH.ORDER_
ID HAVING (MAX(OS.STATUS_DATETIME) <= ?) ORDER BY OH.ORDER_ID ASC

OrderHeader有30万行，OrderStatus大约有5-6百万. 所有查询的字段都有索引.数据库是Postgres 9.1

OrderHeader has 300 000 rows, OrderStatus has roughly 5-6 million. All queried fields have an index on them. The database is Postgres 9.1

SELECT OH.ORDER_ID, MAX(OS.STATUS_DATETIME) FROM ORDER_HEADER OH INNER JOIN ORDER_STATUS OS 
    ON ((OH.ORDER_ID = OS.ORDER_ID AND OH.STATUS_ID = 'ORDER_PARTIALLY_RECEIVED' AND OS.STATUS_ID = 'ORDER_PARTIALLY_RECEIVED'))
     GROUP BY OH.ORDER_ID HAVING (MAX(OS.STATUS_DATETIME) <= '2015-01-27 00:00:00') ORDER BY OH.ORDER_ID ASC

这是替换参数时的查询字符串.

This is the query string when the parameters are substitued in.

从Java运行查询，作为PreparedStatement和其中设置的参数使查询运行几分钟(5-6分钟). 当我手动运行SQL时，它需要10秒钟. 这怎么可能，我该怎么解释?

Running the query from Java, as a PreparedStatement and parameters set in there is making the query to run for minutes (5-6 minutes). When i run the SQL manually, it takes 10 seconds. How is this possible, how can i explain it?

推荐答案

在不了解实际参数的情况下优化了准备好的语句.在PostgreSQL 9.1中，准备好的语句仅针对大多数常用值进行了优化.当准备好的语句很慢时，则必须使用动态SQL. PostgreSQL有很好的API-参数查询-它介于预准备语句和常规查询之间-可以安全地防止SQL注入，并且可以避免盲目优化的问题.

Prepared statements are optimized without knowledge of actual parameters. In PostgreSQL 9.1 a prepared statements are optimized only for most common values. When prepared statements is slow, then you have to use a dynamic SQL. PostgreSQL has nice API - parametric queries - it is some between prepared statements and usual queries - It is safe against SQL injection, and it is immune against a problems with blind optimization.

这篇关于PreparedStatement非常慢，但是手动查询很快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！