Performence issue:Write query directly in session bean or call storted procedure

[chandr prakash]

Enviornment : Weblogic 10.x,Oracle11gR2,EJB3
Both ApplicationServer and Database server are installed on different machine.From my sessionBean deployed on application server i am retrieve some rows from database(table Department).There are 40000000(40 milion rows) rows are present in database table. Data source is deployed on application server.To do this i wrote two approaches.
Approach1
In this approach i am calling stored procedure (GET_NOS) from my session bean.
SessionBean

public ArrayList getDetails(String selectedCity,Timestamp startDate1,Timestamp endDate1,
			Timestamp startDate2,Timestamp endDate2,String salaryOperator,String salaryDuring
			,String totalCountOperator,String totalCount,String startDate3Operator,Timestamp startDate3,
			String endDate3Operator,Timestamp endDate3) throws SQLException
	{
		System.out.println(" Method Start time "+new java.util.Date());
		java.text.SimpleDateFormat sdf=new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
   	    String SDA=sdf.format(startDate2);
   	    String EDA=sdf.format(endDate2);
   	    String ED=sdf.format(startDate3);
	    String LD=sdf.format(endDate3);
	    String SSD=sdf.format(startDate1);
	    String SED=sdf.format(endDate1);
   	    int index=0;
     
   	
		ArrayList<CnaSilentDTO> cnList=new ArrayList<CnaSilentDTO>();
		
		Connection conn = null;
		String sql = "";
		CallableStatement stmt = null;
		try 
	    {
	    	 /********************Retrieve Connection object by lookup of data source*********************/
	    	 	
			sql = "{ Call GET_NOS(?,?,?,?,?,?,?,?,?,?,?,?,?,?) } ";
		     
	         stmt = conn.prepareCall(sql);
	         stmt.setString(1,selectedCity);
	    	 stmt.setTimestamp(2,startDate1);
	    	 stmt.setTimestamp(3,endDate1);
	    	 stmt.setTimestamp(4,startDate2);
	    	 stmt.setTimestamp(5,endDate2);
	    	 stmt.setString(6,salaryOperator);
	    	 stmt.setString(7,salaryDuring);
	    	 stmt.setString(8,totalCountOperator);
	    	 stmt.setString(9,totalCount);
	    	 stmt.setString(10,startDate3Operator);
	    	 stmt.setTimestamp(11,startDate3);
	    	 stmt.setString(12,endDate3Operator);
	    	 stmt.setTimestamp(13,endDate3);
	    	 
	    	 stmt.registerOutParameter(14,-10);
	    	 stmt.execute();
	    	 ResultSet rs = (ResultSet)stmt.getObject(14);
	    	
	    	 
	    	 
	    	 
	    	 System.out.println(" Before while loop "+new java.util.Date());
	   		 
	   		  while (rs.next()) 
		        {
	   			 
	                outputDTO oDto=new outputDTO(i,rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),
	                		rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9));
	            	
	                outList.add(outputDto);
	            }
		 System.out.println(" Method End time "+new java.util.Date());
	    	 
	    }	 
	    catch (Exception e) 
	    {
	    	e.printStackTrace();
	    	try
	    	   {
	    			if (conn != null)
	    				conn.close();
	    	   } 
	    	catch (Exception e1) 
	    	{
	    	     e1.printStackTrace();
	    	}
	    }
	    finally
	    {
	    	try
	    	    {
	    	         if (conn != null)
	    	           conn.close();
	    	    } 
	    	catch (Exception e) 
	    	{
	    	   e.printStackTrace();
	    	}
	   }
		return outList;	 
	}

Stored Procedure

CREATE or REPLACE PROCEDURE GET_NOS_NUMBERS(
cities IN VARCHAR2,
startDate1 IN DATE,
endDate1 IN DATE,
startDate2 IN DATE,
endDate2 IN DATE,
salaryOperator IN VARCHAR2,
totalSalary IN VARCHAR2,
totalCountOperator IN VARCHAR2,
totalCount IN number,
startDate3Oper IN VARCHAR2,
startDate IN DATE,
endDate3Oper IN VARCHAR2,
endDate IN DATE,
Out_Entity OUT TEST_PKG.RefCsr
)
AS
c_SQL CONSTANT VARCHAR2(4000) :='
select firstName,minStartDate,maxStartDate,branchComp,branchEce,branchEle,branchIt
                ,sumSalary,branchComp+branchEce+branchEle+branchIt as totalBranch
From
(
select x.firstName,x.minStartDate,x.maxStartDate
        ,sum(decode(Id,:ln_Val1,1,0)) as branchComp
        ,sum(decode(Id,:ln_Val2,1,0)) as branchEce
        ,sum(decode(Id,:ln_Val3,1,0)) as branchEle
        ,sum(decode(Id,:ln_Val4,1,0)) as branchIt
        ,sumSalary
  from
(select firstName,minStartDate, maxStartDate,sumSalary 
From 
(
select firstName,minStartDate, maxStartDate,sumSalary 
From 
(
select first_name firstName,min(start_date) minStartDate,max(start_date) maxStartDate,sum(salary) sumSalary from Department WHERE start_date between
                  :ln_Val5 AND :ln_Val6 AND city IN (select * from the (select cast(str2tbl(:ln_Val7) as mytableType) from dual ))
group by first_name
HAVING count(*) #op1 :ln_Val10
) 
where minStartDate #op2 :ln_Val11 AND maxStartDate #op3 :ln_Val12 AND sumSalary #op4 :ln_Val13
)
where
               firstName not in
               (
                select first_name from Department where start_date BETWEEN :ln_Val8 AND :ln_Val9
               )
)x
,Department act
      where x.firstName IN(act.first_name) and act.start_date between :ln_Val5 AND :ln_Val6 AND city IN (select * from the (select cast(str2tbl(:ln_Val16) as mytableType) from dual )) 
      group by x.firstName, x.minStartDate, x.maxStartDate,sumSalary)';
         

ls_Sql      VARCHAR2(4000);
ln_Val1 VARCHAR2(100) := 'Ece';
ln_Val2 VARCHAR2(100) := 'Comp';
ln_Val3 VARCHAR2(100) := 'Ele';
ln_Val4 VARCHAR2(100) := 'It';
ln_Val5 DATE := startDate2;
ln_Val6 DATE := endDate2;
ln_Val7 VARCHAR2(4000) := cities;
ln_Val8 DATE := startDate1;
ln_Val9 DATE := endDate1;
ln_Val10 NUMBER := totalCount;
ln_Val11 DATE := startDate3;
ln_Val12 DATE := endDate3;
ln_Val13 NUMBER := totalSalary;
ln_Val14 DATE := startDate2;
ln_Val15 DATE := endDate2;
ln_Val16 VARCHAR2(4000):= cities;






BEGIN

    ls_Sql := REPLACE(c_SQL, '#op1', totalCountOperator);
    ls_Sql := REPLACE(ls_Sql,'#op2', startDate3Oper);
    ls_Sql := REPLACE(ls_Sql,'#op3', endDate3Oper);
    ls_Sql := REPLACE(ls_Sql,'#op4', salaryOperator);

    OPEN Out_Entity FOR ls_Sql using ln_Val1,ln_Val2,ln_Val3,ln_Val4,ln_Val5,ln_Val6,ln_Val7,ln_Val10,ln_Val11,ln_Val12,ln_Val13,ln_Val8,ln_Val9,ln_Val5,ln_Val6,ln_Val16;
  end;
  /

Approach 2 : In this approach i didnt use stored procedure and wrote sql query in session bean code.(The only difference between both approach is, i wrote same query directly in session bean.)
Session bean

public ArrayList getDetails(String selectedCity,Timestamp startDate1,Timestamp endDate1,
			Timestamp startDate2,Timestamp endDate2,String salaryOperator,String salaryDuring
			,String totalCountOperator,String totalCount,String startDate3Operator,Timestamp startDate3,
			String endDate3Operator,Timestamp endDate3) throws SQLException
	{
		System.out.println(" Method Start time "+new java.util.Date());
		java.text.SimpleDateFormat sdf=new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
   	    String SDA=sdf.format(startDate2);
   	    String EDA=sdf.format(endDate2);
   	    String ED=sdf.format(startDate3);
	    String LD=sdf.format(endDate3);
	    String SSD=sdf.format(startDate1);
	    String SED=sdf.format(endDate1);
   	    int index=0;
     
   	
		ArrayList<CnaSilentDTO> cnList=new ArrayList<CnaSilentDTO>();
		
		Connection conn = null;
		String sql = "";
		CallableStatement stmt = null;
		try 
	    {
	    	 /********************Retrieve Connection object by lookup of data source*********************/
	    	 	
			sql = "select firstName,minStartDate,maxStartDate,branchComp,branchEce,branchEle,branchIt,sumSalary,branchComp+branchEce+branchEle+branchIt as totalBranch From (select x.firstName,x.minStartDate,x.maxStartDate,sum(decode(Id,'Comp',1,0)) as branchComp,sum(decode(Id,'Ece',1,0)) as branchEce,sum(decode(Id,'Ele',1,0)) as branchEle,sum(decode(Id,'It',1,0)) as branchIt,sumSalary from (select firstName,minStartDate, maxStartDate,sumSalary From (select firstName,minStartDate, maxStartDate,sumSalary From (select first_name firstName,min(start_date) minStartDate,max(start_date) maxStartDate,sum(salary) sumSalary from Department WHERE start_date between to_date('"+SDA+"', 'YYYY-MM-DD HH24:MI:SS') AND to_date('"+EDA+"','YYYY-MM-DD HH24:MI:SS') AND city IN ("+selectedCity+") group by first_name HAVING count(*) "+totalCountOperator+" "+totalCount+") where minStartDate "+startDate3Operator+" to_date('"+ED+"', 'YYYY-MM-DD HH24:MI:SS') AND maxStartDate "+endDate3Operator+" to_date('"+LD+"', 'YYYY-MM-DD HH24:MI:SS') AND sumSalary   "+salaryOperator+" "+salaryDuring+") where firstName not in (select first_name from Department where start_date BETWEEN to_date('"+SSD+"', 'YYYY-MM-DD HH24:MI:SS') AND to_date('"+SED+"','YYYY-MM-DD HH24:MI:SS')))x,Department act where x.firstName IN(act.first_name) and act.start_date between to_date('"+SDA+"', 'YYYY-MM-DD HH24:MI:SS') AND to_date('"+EDA+"','YYYY-MM-DD HH24:MI:SS') AND city IN ("+selectedCity+") group by x.firstName, x.minStartDate, x.maxStartDate,sumSalary)";
		     
	         java.sql.Statement stmt1 = conn.createStatement();
	    	 ResultSet rs = (ResultSet)stmt1.executeQuery(sql);
	    	
	    	 
	    	 
	    	 
	    	 System.out.println(" Before while loop "+new java.util.Date());
	   		 
	   		  while (rs.next()) 
		        {
	   			 
	                outputDTO oDto=new outputDTO(i,rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),
	                		rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9));
	            	
	                outList.add(outputDto);
	            }
		 System.out.println(" Method End time "+new java.util.Date());
	    	 
	    }	 
	    catch (Exception e) 
	    {
	    	e.printStackTrace();
	    	try
	    	   {
	    			if (conn != null)
	    				conn.close();
	    	   } 
	    	catch (Exception e1) 
	    	{
	    	     e1.printStackTrace();
	    	}
	    }
	    finally
	    {
	    	try
	    	    {
	    	         if (conn != null)
	    	           conn.close();
	    	    } 
	    	catch (Exception e) 
	    	{
	    	   e.printStackTrace();
	    	}
	   }
		return outList;	 
	}

Both Approaches are working successfully,but for same input, approach 1 is 10 times slower than approach2.(All conditions are exactly same while testing). For same input if i run code then approach2 is taking less time, and i dont want to write quey in session bean,i want to use stored procedure.
What am i doing wrong in approach1? please reply

[chandr prakash]

Kindly reply!

[Andrew Moko]

Definitely the problem lies with your SP.

[Andrew Moko]

Run the SP seperately on your database and calculate the time it takes to execute

[john lazeraski]

I would have thought a stored procedure would be MUCH faster than jdbc query?

[Andrew Moko]

john lazeraski wrote:I would have thought a stored procedure would be MUCH faster than jdbc query?

Yes, but not when it's wrongly written

[chandr prakash]

thankx for reply!
@Andrew
Yeah,its true.....when i executed stored procedure from sqlplus stand alone, its taking time 10 times as comapred to jdbc query. what is wrong with my stored procedure...where it got stuck up?