与语句相比,使用preparedStatement查询要慢得多 [英] query much slower with preparedStatement compared to statement
问题描述
我有两种包含相同SQL查询的不同方法。第一拳使用了非常慢的prepareStatement
I have two different methods that contain the same SQL query. The fist one uses preparedStatement which is very slow
public String getPropertyPreparedStatement(String address) throws Exception {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
String content = null;
try {
Class.forName("org.postgresql.Driver");
conn = DataSourceUtils.getConnection(template.getDataSource());
pst = conn.prepareStatement(
"EXPLAIN ANALYZE SELECT property.id AS property_id , full_address, street_address, street.street, city.city as city, state.state_code as state_code, zipcode.zipcode as zipcode FROM property INNER JOIN street ON street.id = property.street_id INNER JOIN city ON city.id = property.city_id INNER JOIN state ON state.id = property.state_id INNER JOIN zipcode ON zipcode.id = property.zipcode_id WHERE full_address = ?");
pst.setString(1, address);
rs = pst.executeQuery();
while (rs.next()) {
// content = rs.getString("street_address");
System.out.println(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (pst != null) {
pst.close();
}
if (rs != null) {
rs.close();
}
if (conn != null) {
conn.close();
}
}
return content;
}
以下方法的解释分析
Nested Loop (cost=1.27..315241.91 rows=1 width=97) (actual time=0.091..688.583 rows=1 loops=1)
-> Nested Loop (cost=0.98..315233.61 rows=1 width=107) (actual time=0.079..688.571 rows=1 loops=1)
-> Nested Loop (cost=0.71..315225.26 rows=1 width=120) (actual time=0.069..688.561 rows=1 loops=1)
-> Nested Loop (cost=0.42..315216.95 rows=1 width=127) (actual time=0.057..688.548 rows=1 loops=1)
-> Seq Scan on property (cost=0.00..315208.51 rows=1 width=131) (actual time=0.032..688.522 rows=1 loops=1)
Filter: ((full_address)::text = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211'::text)
Rows Removed by Filter: 8790
-> Index Scan using street_pkey on street (cost=0.42..8.44 rows=1 width=28) (actual time=0.019..0.019 rows=1 loops=1)
Index Cond: (id = property.street_id)
-> Index Scan using city_id_pk on city (cost=0.29..8.30 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (id = property.city_id)
-> Index Scan using state_id_pk on state (cost=0.28..8.32 rows=1 width=19) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (id = property.state_id)
-> Index Scan using zipcode_id_pk on zipcode (cost=0.29..8.30 rows=1 width=22) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (id = property.zipcode_id)
Planning Time: 2.400 ms
Execution Time: 688.674 ms
以下方法使用语句,我直接在查询中找到地址以测试性能
The method below uses statement and I have the address directly in the query to test performance
public String getPropertyStatement() throws Exception {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String content = null;
try {
Class.forName("org.postgresql.Driver");
conn = DataSourceUtils.getConnection(template.getDataSource());
stmt = conn.createStatement();
rs = stmt.executeQuery(
"EXPLAIN ANALYZE SELECT property.id AS property_id , full_address, street_address, street.street, city.city as city, state.state_code as state_code, zipcode.zipcode as zipcode FROM property INNER JOIN street ON street.id = property.street_id INNER JOIN city ON city.id = property.city_id INNER JOIN state ON state.id = property.state_id INNER JOIN zipcode ON zipcode.id = property.zipcode_id WHERE full_address = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211'");
while (rs.next()) {
// content = rs.getString("street_address");
System.out.println(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stmt != null) {
stmt.close();
}
if (rs != null) {
rs.close();
}
if (conn != null) {
conn.close();
}
}
return content;
}
对上述方法进行解释分析
EXPLAIN ANALYZE for the method above
Nested Loop (cost=29.82..65.96 rows=1 width=97) (actual time=0.232..0.235 rows=1 loops=1)
-> Nested Loop (cost=29.53..57.65 rows=1 width=107) (actual time=0.220..0.223 rows=1 loops=1)
-> Nested Loop (cost=29.25..49.30 rows=1 width=120) (actual time=0.211..0.213 rows=1 loops=1)
-> Nested Loop (cost=28.97..41.00 rows=1 width=127) (actual time=0.198..0.200 rows=1 loops=1)
-> Bitmap Heap Scan on property (cost=28.54..32.56 rows=1 width=131) (actual time=0.175..0.177 rows=1 loops=1)
Recheck Cond: (full_address = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211'::citext)
Heap Blocks: exact=1
-> Bitmap Index Scan on property_full_address (cost=0.00..28.54 rows=1 width=0) (actual time=0.162..0.162 rows=1 loops=1)
Index Cond: (full_address = '139-Skillman-Ave-Apt-5C-Brooklyn-NY-11211'::citext)
-> Index Scan using street_pkey on street (cost=0.42..8.44 rows=1 width=28) (actual time=0.017..0.017 rows=1 loops=1)
Index Cond: (id = property.street_id)
-> Index Scan using city_id_pk on city (cost=0.29..8.30 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (id = property.city_id)
-> Index Scan using state_id_pk on state (cost=0.28..8.32 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (id = property.state_id)
-> Index Scan using zipcode_id_pk on zipcode (cost=0.29..8.30 rows=1 width=22) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (id = property.zipcode_id)
Planning Time: 2.442 ms
Execution Time: 0.345 ms
当我运行直接在数据库上查询的速度也非常快,就像使用语句而不是prepareStatement的方法一样。
When I run the query on the database directly it's also very fast, pretty much like the method that uses statement instead of preparedStatement.
为什么prepareStatement这么慢?在保持能够在查询中使用占位符的同时,我必须保持哪些选项?
Why is the preparedStatement so much slower? What options do I have to keep the performance of using statement while still being able to use a placeholder in the query?
推荐答案
您的准备好的语句将 full_address
转换为 text
(Postgres的内置文本类型),而表似乎已创建 citext
(不区分大小写)文本类型(或者,您在 full_address :: text
上缺少索引)。也许尝试在 full_address :: text
上创建一个索引,看看您准备好的语句是否可以使用它。
Your prepared statement casts full_address
into text
(Postgres' built-in text type), whereas it seems that your table is created with a citext
(case-insensitive) text type (or, you lack an index on full_address::text
). Maybe try creating an index on full_address::text
and see if your prepared statement will pick it up.
另一个选项是对完整地址
列使用文本
类型,然后在 lower(full_address)
-该选项的适用性取决于您的要求。
Another option is to use a text
type for the full_address
column, and then create a functional index on lower(full_address)
-- the palatability of that option depends on what your requirements are.
我认为问题的一部分在于JDBC不知道 citext
类型,因此除非您可以获取JDBC以 citext
类型,查询计划人员会将其解释为文本
,就像您的 setString()
方法一样
I think that part of the problem is that JDBC doesn't know about the citext
type so unless you can get JDBC to send your address to the database as a citext
type, it's going to be interpreted by the query planner as text
, just as your setString()
method probably does.
有趣的是,我最近遇到了类似的问题
披露:我为 EnterpriseDB(EDB)
这篇关于与语句相比,使用preparedStatement查询要慢得多的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!