PreparedStatement very slow, but manual query quick

Question

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 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.

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?

Post your java code. From your description the problem is on java side. — StanislavL, Jan 30 '15 at 13:27
Also, make sure that you're executing exactly the same query (as shown by logging from the DB side) and not one that's subtly different. — chrylis -cautiouslyoptimistic-, Jan 30 '15 at 13:44
Those two statements look quite different. Why does the second one have an inner join? — chrylis -cautiouslyoptimistic-, Jan 30 '15 at 13:45

score 4 · Answer 1 · answered Jan 30 '15 at 13:36

4

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.

answered Jan 30 '15 at 13:36

Pavel Stehule

42,331
5
91
94

what do you mean by use dynamic SQL? – david Nov 06 '19 at 04:57
@david - it is common name for statements executed via `EXECUTE` statement on Postgres or `EXECUTE IMMEDIATE` on Oracle. The query are formatted at last time - at runtime, and any time can be different - at runtime are created from strings - dynamic queries – Pavel Stehule Nov 06 '19 at 05:26

score 2 · Answer 2 · answered Jan 30 '15 at 13:34

2

If the result set is large - PreparedStatement could work slower, because default "fetchSize" is only 10. Try to set bigger fetchSize for this query. More info here: http://docs.oracle.com/cd/A97335_02/apps.102/a83724/resltse5.htm
And specialy for Postgres here: https://jdbc.postgresql.org/documentation/head/query.html

answered Jan 30 '15 at 13:34

StrekoZ

618
6
12

The actual query is slow (it is running at the db, not the resultset processing). – Zsolt János Jan 30 '15 at 13:37

score 0 · Answer 3 · answered Aug 08 '18 at 09:12

If you use preparedStatements,you must identify paramter type . you can explain the sql. e.g uid type is integer explain analyse select * from users where uid in (200,500,800); PREPARE fooplan (int, int, int) AS select * from users where uid in ($1, $2, $3); explain analyse EXECUTE fooplan(200, 500, 800); PREPARE fooplan1 (numeric, numeric, numeric) AS select * from users where uid in ($1, $2, $3); explain analyse EXECUTE fooplan1(200, 500, 800);

PreparedStatement very slow, but manual query quick

3 Answers3

Linked