There is a SOA Extension function called query-database() which uses the data source JNDI (instead of a Connection factory JNDI) to connect to a database.Does using the datasource JNDI have any disadvantages compared to using the Connection factory JNDI? In other words, say I decide to implement a simple select using this function instead of using a DBAdapter, Will there be a performance impact? or will I have any advantage (with respect to connection pooling features) if the DBAdapter is used instead?
Using DB adapter with connection factory will provide advantages of connection pooling and connection management features, as opposed to using a direct JDBC connect string in the query-database() function where each time the function is called, the application server would create a new connection to the DB, so there is an overhead. Particularly if the function is called heavily (in high volume scenario), there could be performance impact. However, if it's real-time requirement with significantly less volume of transactions and a simple query to the DB, could decide to use the function.
Thanks for your response.
Isn't connection pooling available at datastore? I notice a "Connection Pool" as one of the tabs in the Datasource. Infact I also had raised an SR with Oracle support (3-7755138761) and the support person also mentioned the same