Custom SQL vs Dynamic SQL: is there any performance differences?

, ,
1

Hi folks,

Just wondering: is there any significant performance difference between Custom SQL and Dynamic SQL?

Let’s assume the following scenario:

  • the target database is an Oracle - assuming a current version - 10i;
  • the adapter will be called by a real time interface;
  • the interface will be called by multiple clients concurrently - let’s stick with 10 at a given moment;
  • the interface needs to query the database to check if the value is valid, and return the result.

Given the following SQL (Custom SQL):

select colA, colB
from aSchema.aTable
where colA = ?
and colB = ?

and (Dynamic SQL):

select colA, colB
from ${schema}.aTable
where colA = ${paramA}
and colB = ${paramB}

and a third case (Dynamic SQL):

select colA, colB
from ${schema}.aTable
where colA = ?
and colB = ?

Analyzing just only the JDBC adapter service portion: is there any significant performance difference between Custom SQL and Dynamic SQL?