|is embedded sql faster than refcursor? [message #420989]
||Thu, 03 September 2009 10:19
Registered: December 1999
Location: Connecticut USA
Hi all. I am mostly a database guy but the java development team in my company does not seem to have a lot of experience with oracle so from time to time I have to provide java support at least to the extent of connecting and getting data from the oracle database.|
Recently I had a team that decided to embedd some sql in their java application code. Their reason was as follows:
|a select statement embedded in java is faster than one that calls an oracle api which returns a refcursor|
Can someone explain to me why this would be so?
Can someone offer suggestions as to how this could be tested to prove it to be so.
I do not want to debate the virtue of the practice. My java teams all know I dislike it and in general they do not do it. I believe this particlar team thought this particular app was a high performance WEB app so they needed the boost.
As an example of why I do not want to discuss this practice of embedding sql in general consider that some would say the lack of use of LPAD in their sql construction was a bad thing, some would say it was a good thing. The almost fanatical argument that ensues from this difference of professional opinion I am not concerned with.
I just want to make sure there is really a significant performance advantage to constructing and executing sql the way it is presented here as compared to the use of a plsql procedure that takes parameters in and sends a refcursor out.
I also want to be able to explain why when someone else asks me.
I figure the best place to get this answer would be in the java forums and not the database forums.
Here is their code as it was forwarded to me:
// set the beginning of the query
SQL.append("select input_data.STT_ABBRVTN, ");
SQL.append(" input_data.WC_CLASS_CODE, ");
SQL.append(" DECODE(ncci_data.NCCI_CLASS_CODE, null, input_data.WC_CLASS_CODE, ");
SQL.append(" ncci_data.NCCI_CLASS_CODE) NCCI_CLASS_CODE ");
// loop through all Data items and build the logical table
List inputParamsList = aDTO.getNodes("Data");
for (int i=0; i<inputParamsList.size(); i++)
DTO aParamDto = (DTO)inputParamsList.get(i);
String state = aParamDto.getValue(STATE_TAG).getAsString();
String wcCode = aParamDto.getValue(CLASS_CD_TAG).getAsString();
String paddedWCCode = wcCode;
// if the state is DE or PA and the class code is not 4 digits, we need to prepend zeros
// since the ref table in database has 4 digit class codes
if ((("DE").equalsIgnoreCase(state) || ("PA").equalsIgnoreCase(state)) && wcCode.length() < 4)
if (wcCode.length() == 1)
paddedWCCode = "000" + wcCode;
else if (wcCode.length() == 2)
paddedWCCode = "00" + wcCode;
else if (wcCode.length() == 3)
paddedWCCode = "0" + wcCode;
+ "' STT_ABBRVTN, '"
+ "' PADDED_CLASS_CODE, '"
+ "' WC_CLASS_CODE from dual ");
if (i < inputParamsList.size()-1)
// append the final part of the query
SQL.append(") input_data, ");
SQL.append("WC_NCCI_CODE_XREF ncci_data ");
SQL.append("where input_data.STT_ABBRVTN = ncci_data.STT_ABBRVTN (+) ");
SQL.append("and input_data.PADDED_CLASS_CODE = ncci_data.WC_CLASS_CODE (+) ");
//System.out.println("##### QUERY:\n" + SQL.toString() + "\n\n");
aSearchCriteriaDTO = aDTO;