Using a diff connection for fetch and diff connection for Insert, Update and Delete operations

[sathish.gdis]

We have 2 connections C1 and C2 in our application
We need to use C1 for all fetch operations and C2 for all insert/update/delete operations.

Please suggest on how to implement this using a Data Source.

[Isomorphic]

If by a different "connection" you mean a different set of JDBC credentials, you can use the approach shown here. You will end up with two copies of each DataSource with different IDs, and you can use one for fetches and the other for all other operations.

Bear in mind that the automatic selection of just inserted data, used for cache synchronization (see ResultSet docs), will occur using the DataSource that just performed the insert, update, or delete.

If you meant something else, like using two different SQLConnection instances that share the same JDBC credentials, please explain why you would need to do this, and we can suggest an approach.

[sathish.gdis]

Hi Isomorphic

We tried working on first option with 2 different datasource copies. As you said, we found the synchronization problem.

We have two different connections in middleware, one meant for readonly purpose and other one is for insert/update/delete.

We have found performance problem while loading the listgrid when there are many records in resultselt or many table joins in sql.
To optimize this, we have trying with readonly datasource while loading the grid and the other one while add/update/remove from listgrid. But couldn't found the feasible solution.

Any suggestions?

[Isomorphic]

The "synchronization problem" just means that some single-row fetches will still occur on the connections used for updates and inserts. This doesn't seem like it would create a problem for your use case.

The more basic question is whether you have verified that using a separate, read-only connection would have a meaningful performance benefit at all. We wouldn't expect it to without additional, special steps being taken, so we would suggest working with a skilled DBA to figure out what would make an impact, rather than continuing down the current course.