Prepared queries with parameterized values running slowly with Greenplum JDBC driver 5.1

Information

Title	Prepared queries with parameterized values running slowly with Greenplum JDBC driver 5.1

URL Name	prepared-queries-with-parameterized-values-running-slowly-with-datadirect-greenplum-jdbc-driver-5-1

Article Number	000122482

Environment	Product: Connect for JDBC Greenplum Driver Version : 5.1.4.000183 OS: JAVA Database : Greenplum Application: All supported applications

Question/Problem Description

After moving from open source Postgresql JDBC driver 9.3-1101 to DataDirect Greenplum JDBC driver, it was observed that execution of prepared queries with paramaterized values is much slower in DataDirect driver than the open source Postgresql driver.

After further detailed investigation, it was found that the behavior of prepared statements in both the DataDirect and open source Postgresql drivers is identical after the default "prepareThreshold" of 5 is reached by the open source Postgresql driver.
https://jdbc.postgresql.org/documentation/head/server-prepare.html

Here is the basic breakdown of the behavior :

Example of a prepareStatement with parameterized values : (" select foo from bar where foo = ? or foo = ?");

1. The DataDirect Greenplum JDBC driver on first execution of the above statement will generate a PREPARE that will look similar to this (just an example):
> PREPARE stmnt1 (text) AS select foo from bar where foo = $1 or foo = $2;
When the PREPARE is executed, the Orca optimizer will fail because it does not support PREPARE statements with parameterized values. When this occurs, Greenplum falls back to the legacy planner which does support parameterized values. An error message showing this happens is produced in the p-logs on the database side:
> ERROR,""GPDB Expression type: {PARAM :paramkind 0 :paramid 1 :paramtype 25 :paramtypmod -1} not supported in DXL"",",,,,,,"select foo from bar where foo = $1 or foo = $2",

2. The open source Postgresql JDBC driver on first execution of the above statement will not generate a PREPARE, it will by default execute the statement directly:
> "execute <unnamed>: select foo from bar where foo = $1 or foo = $2","parameters: $1 = 'abc', $2 = 'def'"
By default, the first 4 executions of the above prepared statement will have the same behavior since the default value for connection property "prepareThreshold" is 5.

After the "prepareThreshold" is reached, the open source Postgresql JDBC driver will generate a PREPARE that is identical to what is generated by the DataDirect Greenplum JDBC driver. This PREPARE statement will have parameterized values therefore it will be rejected by the Orca optimizer and will fall back to the legacy planner.
So the delay was observed in the first 4 execution (depends on the value of connection property "prepareThreshold") of prepared queries with parameterized values.
The behavior for both drivers is exactly same when "prepareThreshold" is reached.

So the question here is:
Does the DataDirect Greenplum JDBC driver has any configuration option similar to the connection property "prepareThreshold" of open source Postgresql JDBC drivers so that the behavior will be identical all the time?

Steps to Reproduce

Clarifying Information

Error Message

Defect Number	RFA XDBC-15567

Enhancement Number

Cause

Resolution

DataDirect Greenplum JDBC driver does not have any configuration option similar to the connection property "preparedThreshold" of open source Postgresql JDBC driver.
It is considered as an enhancement to the product.

It was determined that the Progress Product is functioning as designed.

An enhancement to the product can be requested through the Progress Community via an Ideas submission. Customer feedback is valuable and Idea submissions are monitored by our Product Management team. Enhancement requests are reviewed during the planning phase of each new product release and a list of the enhancements chosen for implementation can be found in the Release Notes documents that accompany each release. Once an Idea is submitted the Progress Software Community will have the opportunity to comment on and vote for the Idea.

For detailed information on how to submit an Idea, please refer to Knowledge Base article How to submit an idea for a Progress product enhancement

Workaround

Notes

Last Modified Date	11/20/2020 7:00 AM

Files

Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

Sitefinity

NativeChat

Kendo UI

Telerik

Test Studio

Fiddler Everywhere

Chef

MOVEitNew Release

WS_FTP

OpenEdge

Kinvey

WhatsUp Gold

DataDirect

Corticon

Kemp

Flowmon

Prepared queries with parameterized values running slowly with Greenplum JDBC driver 5.1

Information

MOVEit
New Release