Home » Developer & Programmer » JDeveloper, Java & XML » is embedded sql faster than refcursor? (9i, 10g, 11g)
is embedded sql faster than refcursor? [message #420989] Thu, 03 September 2009 10:19 Go to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
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 ");
    	SQL.append("from ");
    	SQL.append("( ");

    	// 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;
			SQL.append("select '" 
+ state 
+ "' STT_ABBRVTN, '" 
+ paddedWCCode 
+ wcCode 
+ "' WC_CLASS_CODE from dual ");
			if (i < inputParamsList.size()-1)
				SQL.append("UNION ");
    	// 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;

Thanks, Kevin
Re: is embedded sql faster than refcursor? [message #420992 is a reply to message #420989] Thu, 03 September 2009 10:29 Go to previous message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For myself, I don't see any reason for it to be faster. Maybe someone else...
Will think about a way to check both and come back.

Previous Topic: How to SELECT * FROM XML Document
Next Topic: Newbie! Please Help - XML to Relational Table (I have searched)
Goto Forum:

Current Time: Tue May 28 14:21:39 CDT 2024