I'm using an updatable ResultSet, where I want to update the columns directly in the ResultSet and commit (for performance and avoiding complex rollback logic). For this, I do the following -
- Create PreparedStatement for query
SELECT <pk_col>, <col2>,...<coln> FROM <my_table_name> FOR UPDATEwith
CONCUR_UPDATABLEas resultSetType and resultSetConcurrency respectively.
- Invoke PreparedStatement.executeQuery and collect the ResultSet.
- Loop through the ResultSet, perform business logic, and invoke the updateXXX methods, and invoke updateRow.
- Continue to the next entry in the ResultSet.
- After the loop is done, commit.
I have noticed that every invocation of the updateRow results in a (pretty complex) select query executed by the driver to retrieve the metadata. I understand that this query is executed to find out the primary key that must be used in the where clause of the update query executed inside updateRow. But this leads to a significant performance hit. In fact, this query to retrieve the metadata takes the most amount of time.
Is there any way that the execution of this query can be avoided or minimized (by telling the driver to cache the results) while using updatable ResultSets? Here is the query executed. I'm using PostgreSQL Driver version 42.3.1.
Let me know if more information is required. Thank you in advance for your help.
SELECT result.TABLE_CAT, result.TABLE_SCHEM, result.TABLE_NAME, result.COLUMN_NAME, result.KEY_SEQ, result.PK_NAME FROM ( SELECT null AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME, ( information_schema._pg_expandarray(i.indkey) ) .n AS KEY_SEQ, ci.relname AS PK_NAME, information_schema._pg_expandarray(i.indkey) AS KEYS, a.attnum AS A_ATTNUM FROM pg_catalog.pg_class ct JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) JOIN pg_catalog.pg_index i ON ( a.attrelid = i.indrelid) JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) WHERE true AND ct.relname = <my_table_name> AND i.indisprimary ) result where result.A_ATTNUM = ( result.KEYS ) .x ORDER BY result.table_name, result.pk_name, result.key_seq