Connection pooling and other performance considerations

[kaliyugantagonist]

Hello,

In the web applications where a database is the back-end, the connection pooling is handled either by the hosting application server or some third-party library e.g: Apache DBCP - rarely someone writes connection pooling logic these days

As per my understanding, following the flow when HPCC-JDBC driver is used :

An instance of HPCCConnection is created

Internally, a HTTP connection is established with the ESP server

Based on the SQL query, the ECL code is generated and sent over the above connection for 'direct ecl' execution

One can imagine that in case of 100s of simultaneous users, the web application will generate 1000s of SQL queries. Now, the following things are crucial for an acceptable performance:

There is a pool of connections to the HPCC cluster so that neither a new connection is created every time nor can unlimited connections be opened to the HPCC cluster

The Statement objects must be reused and cached

Sharing connection objects between different requests must be done so that a limited no. of connections can serve a relatively large no. of users

I flipped through the HPCC-JDBC driver's source code. Is there currently a way to ensure the listed things for performance tuning?If yes, what and where are the configurations located?If no, what can be done currently to optimize the performance?

Thanks and regards !

[[email protected]]

Hello again kaliyugantagonist,
In this case, if connection pooling necessary, it is the responsibility of the caller.
There's currently no plan to integrate connection pooling into the driver( but remember contributions are always welcomed).

The HPCCConnection can be seen as a session. It fetches and retains information needed to handle incoming SQL requests, and user configuration info. It gathers db metadata from the HPCC system via web services in an on-demand manner.

Depending on the situation, one HPCCConnection object should be created and either HPCCStatement(s) or HPCCPreparedStatement(s) should be created in order to execute the SQL queries. Each statement object communicates with the HPCC system via WebServices in an on-demand manner.

If your queries tend to follow a pattern, you should set-up an HPCCPreparedStatement with parameter placeholders, re-use the object while providing the input param values at execute time. Thanks.