Performance Issue with JDBC

Hi,

The application structure as follows:

Database - Oracle 9i

JDBC driver - Type IV (ojdbc14.jar)

App server - Weblogic 8.1.5

JDK - 1.4.2_08

d) What type are the columns in the ResultSet i.e. Are there any LOB objects in the ResultSet? - no LOBs, most of them are VARCHAR2(4000) but the data inside them might be around VARCHAR2(20).

e) Are there any large character strings in the ResultSet?

yes - as said above

f) What type of hardware are you running the program on?

- for now, im running it on my PC (1GHZ)

g) What type of hardware are you running the database on?

- HP UX

I am not running this app on the Database server and i can't run it there as i don't have permission.

It's actually a download from the front end JSP. We will have to retrieve the data from different sources and write it to an excel.

My code is as follows.

<code>

</package com.qwest.inventory.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.Hashtable;

import com.qwest.inventory.entity.SocnDetailData;

import com.qwest.inventory.entity.vo.AddressData;

import com.qwest.inventory.entity.vo.NetworxCLINVO;

import com.qwest.inventory.entity.vo.NetworxDownloadVO;

import com.qwest.inventory.entity.vo.NetworxInventoryDetailVO;

import com.qwest.inventory.entity.vo.NetworxInventoryVO;

import com.qwest.inventory.util.StringUtils;

import com.qwest.inventory.util.db.ConnectionPool;

import com.qwest.inventory.util.web.UserProfile;

import com.qwest.inventory.entity.Constants;

import org.apache.log4j.Logger;

public class NetworxDownloadDAO extends InventoryDAO {

Logger logger = Logger.getLogger(NetworxDownloadDAO.class);

public NetworxDownloadDAO() {

}

public ArrayList getDownloadData(String filterclause, String sortclause, UserProfile user){

ArrayList downloadList = new ArrayList();

ConnectionPool connectionPool = null;

Connection conn = null;

PreparedStatement pstm1 = null;

PreparedStatement pstm2 = null;

ResultSet rs_download_main= null;

ResultSet rs_download_socn = null;

NetworxDownloadVO downloadVO = null ;

NetworxInventoryVO inventoryVO = null;

NetworxInventoryDetailVO detailVO = null;

NetworxCLINVO clinVO = null;

String userId = "";

String userType = "";

long starttime =0;

long endtime=0;

long newtime = 0;

long newtime1 = 0;

long qcid=0;

long prevubi=-1;

long currubi;

String socnCurrubi ="";

String socnPrevUbi ="";

SocnDetailData sd = new SocnDetailData();

Hashtable ht = new Hashtable();

String tempStrVal="";

long tempNumVal =0;

//ITR448855, Added SERVICE_INSTANCE_ALIAS_ID to the select.

String mainQuery = "select * from ( SELECT main.customer_acct_id, main.agency_hier_cd, main.ext_network_inventory_cd, " +

"main.prd_acct_service_cat_seq, main.service_instance_alias_id, main. service_cat_dsc,main. enterprise_nm, " +

"main.address_line1_txt, main.address_line2_txt, main.address_line3_txt, main.city_nm, main.state_cd, main.zip_cd, " +

"main.country_cd, main.customer_nm, main.billing_address_val, main.billing_city_nm, main.billing_state_cd, " +

"main.billing_zip_cd, main.billing_country_code, vst.CONTRACTORNAME, vst.CONTRACTNUMBER, vst.DARNAME, " +

"vst.AGENCYSERVICEREQUESTNUMBER, vst.TELECOMSERVICEPRIORITY, vst.CIRCUITID, vst.ACCESSPROVISIONING, vst.BANDWIDTH, " +

"vst.DIRECTEDTONUMBER, vst.CUSTOMERWANTDATE, vst.FIRMORDERCOMMITMENTDATE, vst.COMPLETIONDATE, vst.SERVICEORDERNUMBER, " +

"vst.ORDERTYPE, vst.ADDITIONALINSTRUCTIONS, vst.CONTRACTORCUSTACCTNO, vst.RECEIPTDATE, vst.ROUTINEORCRITICALSERVICELVL, " +

"vst.ORIGINATING_SERVINGWIRECENTER, vst.TERMINATING_SERVINGWIRECENTER, vst.UNIQUEBILLINGIDENTIFIER, vst.CLINVALUE, " +

"vst.Description, vst.FeatureType, vst.SEDDescription, vst.UnitPrice, vst.Quantity, vst.ICBCaseNumber " +

"FROM corp.v_socn_info VST, (SELECT inv.customer_acct_id, UPPER (inv.agency_hier_cd) agency_hier_cd, " +

"UPPER (inv.ext_network_inventory_cd) ext_network_inventory_cd, to_char(inv.prd_acct_service_cat_seq) prd_acct_service_cat_seq, " +

"UPPER(inv.service_instance_alias_id) service_instance_alias_id, UPPER (inv.service_cat_dsc) service_cat_dsc, " +

"UPPER (eai.enterprise_nm) enterprise_nm, UPPER (invloc.address_line1_txt) address_line1_txt, UPPER (invloc.address_line2_txt) address_line2_txt, " +

"UPPER (invloc.address_line3_txt) address_line3_txt, UPPER (invloc.city_nm) city_nm, UPPER (invloc.state_cd) state_cd, invloc.zip_cd zip_cd, " +

"UPPER (invloc.country_cd) country_cd, cai.customer_nm, cai.billing_address_val, cai.billing_city_nm, cai.billing_state_cd, cai.billing_zip_cd, " +

"cai.billing_country_code FROM customer_acct_info cai, inventory inv, inventory_location invloc, enterprise_acct_info eai" +

" WHERE eai.enterprise_acct_id = cai.enterprise_acct_id AND cai.customer_acct_id = inv.customer_acct_id AND cai.status_cd = 'A' " +

"AND invloc.fe_cust_loc_no = inv.fe_cust_loc_no AND invloc.customer_acct_id = inv.customer_acct_id AND inv.product_code = 'NETWORX' " +

"><xxxordbyxxx>) main WHERE vst.uniquebillingidentifier(+) = main.prd_acct_service_cat_seq AND rownum < 2000 <xxxwhereclxxx>)" ;

//AND main.prd_acct_service_cat_seq = 831316

String nonGSAAcctsQuery="AND Inv.CUSTOMER_ACCT_ID in (SELECT * FROM TABLE (l_networx_inventory.f_getCustomerAcctID(?,?,?)))";

String nonGSAUbisQuery =" AND v_socn.UNIQUEBILLINGIDENTIFIER in (select to_char(prd_acct_service_cat_seq) from inventory where customer_acct_id in (SELECT * FROM TABLE (l_networx_inventory.f_getCustomerAcctID('userId','userType',qcid))))";

/*

String GSASocndataQuery = "SELECT V_SOCN.UNIQUEBILLINGIDENTIFIER UBI, 1 COL, V_SOCN_AHC.AHC AHC, '' NETWORXINVENTORYCODE, '' JURISDICTIONID, '' ACCESSTYPE " +

"FROM V_SOCN, V_SOCN_AHC WHERE V_SOCN.UNIQUEBILLINGIDENTIFIER = '831316' AND V_SOCN.UNIQUEBILLINGIDENTIFIER = V_SOCN_AHC.UNIQUEBILLINGIDENTIFIER UNION " +

"SELECT V_SOCN.UNIQUEBILLINGIDENTIFIER UBI, 2 COL, '' AHC,V_SOCN_NIC.NETWORXINVENTORYCODE,'' JURISDICTIONID,'' ACCESSTYPE " +

"FROM V_SOCN, V_SOCN_NICWHERE V_SOCN.UNIQUEBILLINGIDENTIFIER = '831316' AND V_SOCN.UNIQUEBILLINGIDENTIFIER = V_SOCN_NIC.UNIQUEBILLINGIDENTIFIERUNION" +

"SELECT V_SOCN.UNIQUEBILLINGIDENTIFIER UBI, 3 COL, '' AHC,'' NETWORXINVENTORYCODE,V_SOCN_JID.JURISDICTIONID,'' ACCESSTYPE " +

"FROM V_SOCN, V_SOCN_JID WHERE V_SOCN.UNIQUEBILLINGIDENTIFIER = '831316' AND V_SOCN.UNIQUEBILLINGIDENTIFIER = V_SOCN_JID.UNIQUEBILLINGIDENTIFIER UNION " +

"SELECT V_SOCN.UNIQUEBILLINGIDENTIFIER UBI, 4 COL,'' AHC,'' NETWORXINVENTORYCODE,'' JURISDICTIONID,V_SOCN_ATYPE.ACCESSTYPE " +

"FROM V_SOCN, V_SOCN_ATYPEWHERE V_SOCN.UNIQUEBILLINGIDENTIFIER = '831316' AND V_SOCN.UNIQUEBILLINGIDENTIFIER = V_SOCN_ATYPE.UNIQUEBILLINGIDENTIFIER order by UBI, COL ";

*/

String GSASocndataQuery = "SELECT V_SOCN.UNIQUEBILLINGIDENTIFIER UBI, 1 COL, V_SOCN_AHC.AHC AHC, '' NETWORXINVENTORYCODE, '' JURISDICTIONID, '' ACCESSTYPE " +

"FROM V_SOCN, V_SOCN_AHC WHERE V_SOCN.UNIQUEBILLINGIDENTIFIER = V_SOCN_AHC.UNIQUEBILLINGIDENTIFIER UNION " +

"SELECT V_SOCN.UNIQUEBILLINGIDENTIFIER UBI, 2 COL, '' AHC,V_SOCN_NIC.NETWORXINVENTORYCODE,'' JURISDICTIONID,'' ACCESSTYPE " +

"FROM V_SOCN, V_SOCN_NICWHERE V_SOCN.UNIQUEBILLINGIDENTIFIER = V_SOCN_NIC.UNIQUEBILLINGIDENTIFIERUNION" +

"SELECT V_SOCN.UNIQUEBILLINGIDENTIFIER UBI, 3 COL, '' AHC,'' NETWORXINVENTORYCODE,V_SOCN_JID.JURISDICTIONID,'' ACCESSTYPE " +

"FROM V_SOCN, V_SOCN_JID WHERE V_SOCN.UNIQUEBILLINGIDENTIFIER = V_SOCN_JID.UNIQUEBILLINGIDENTIFIER UNION " +

"SELECT V_SOCN.UNIQUEBILLINGIDENTIFIER UBI, 4 COL,'' AHC,'' NETWORXINVENTORYCODE,'' JURISDICTIONID,V_SOCN_ATYPE.ACCESSTYPE " +

"FROM V_SOCN, V_SOCN_ATYPEWHERE V_SOCN.UNIQUEBILLINGIDENTIFIER = V_SOCN_ATYPE.UNIQUEBILLINGIDENTIFIER order by UBI, COL ";

String socndataQuery = "SELECT UNIQUEBILLINGIDENTIFIER UBI, 1 COL, V_SOCN_AHC.AHC AHC, '' NETWORXINVENTORYCODE, '' JURISDICTIONID, '' ACCESSTYPE " +

"FROM V_SOCN_AHC " + nonGSAUbisQuery + " UNION " +

"SELECT UNIQUEBILLINGIDENTIFIER UBI, 2 COL, '' AHC,V_SOCN_NIC.NETWORXINVENTORYCODE,'' JURISDICTIONID,'' ACCESSTYPE " +

"FROM V_SOCN_NIC" + nonGSAUbisQuery + " UNION " +

"SELECT UNIQUEBILLINGIDENTIFIER UBI, 3 COL, '' AHC,'' NETWORXINVENTORYCODE,V_SOCN_JID.JURISDICTIONID,'' ACCESSTYPE " +

"FROM V_SOCN_JID " + nonGSAUbisQuery + " UNION " +

"SELECT UNIQUEBILLINGIDENTIFIER UBI, 4 COL,'' AHC,'' NETWORXINVENTORYCODE,'' JURISDICTIONID,V_SOCN_ATYPE.ACCESSTYPE " +

"FROM V_SOCN_ATYPE " + nonGSAUbisQuery + " order by UBI, COL ";

String socnQuery="";

try {

starttime = System.currentTimeMillis();

if(user !=null){

userId=user.getUserLoginId();

userType = user.getUserType();

qcid=user.getEnterpriseId();

}

if(filterclause !=null){

filterclause = replaceAllOccurences(filterclause,"main.", "INV.");

filterclause = replaceAllOccurences(filterclause,"main.", "INVLOC.");

filterclause = replaceAllOccurences(filterclause,"main.", "EAI.");

logger.debug("filter clause: " + filterclause);

}

/*if(sortclause !=null){

sortclause = replaceAllOccurences(sortclause,"main.", "INV.");

sortclause = replaceAllOccurences(sortclause,"main.", "INVLOC.");

sortclause = replaceAllOccurences(sortclause,"main.", "EAI.");

} */

logger.debug("sort clause: " + sortclause);

connectionPool = new ConnectionPool();

conn = connectionPool.getDBConnection("appPoolName");

if (conn == null) {

logger.warn("Not able to get connection from Weblogic Connection Pool.");

}

if(Constants.GSA_USER.equals(userType) ||

Constants.QWEST_INTERNAL_USER.equals(userType)){

if(filterclause !=null){

mainQuery = replaceAllOccurences(mainQuery,filterclause,"<xxxwhereclxxx>");

}

if(sortclause !=null){

mainQuery = replaceAllOccurences(mainQuery,sortclause,"<xxxordbyxxx>");

}

socnQuery =GSASocndataQuery;

pstm1 = conn.prepareStatement(mainQuery);

pstm2 = conn.prepareStatement(socnQuery);

}

else{

mainQuery+=nonGSAAcctsQuery;

if(filterclause !=null){

mainQuery = replaceAllOccurences(mainQuery,filterclause,"<xxxwhereclxxx>");

}

if(sortclause !=null){

mainQuery = replaceAllOccurences(mainQuery,sortclause,"<xxxordbyxxx>");

}

socndataQuery = replaceAllOccurences(socndataQuery,userId, "userId");

socndataQuery = replaceAllOccurences(socndataQuery,userType, "userType");

socndataQuery = replaceAllOccurences(socndataQuery,String.valueOf(qcid), "qcid");

socnQuery=socndataQuery;

pstm1 = conn.prepareStatement(mainQuery);

pstm1.setString(1,userId);

pstm1.setString(2,userType);

pstm1.setLong(3,qcid);

pstm2 = conn.prepareStatement(socnQuery);

/*pstm2.setString(1,userId);

pstm2.setString(4,userId);

pstm2.setString(7,userId);

pstm2.setString(10,userId);

pstm2.setString(2,userType);

pstm2.setString(5,userType);

pstm2.setString(8,userType);

pstm2.setString(11,userType);

pstm2.setLong(3,qcid);

pstm2.setLong(6,qcid);

pstm2.setLong(9,qcid);

pstm2.setLong(12,qcid); */

}

//startime = System.currentTimeMillis();

logger.debug("the main query used for download is: " + mainQuery);

//pstm1.setFetchSize(500);

rs_download_main = pstm1.executeQuery();

endtime = System.currentTimeMillis();

logger.debug("Time taken to execute main query for download: " + (endtime-starttime)/1000 + "seconds");

starttime=endtime;

logger.debug("the socn query used for download is: " + socnQuery);

rs_download_socn = pstm2.executeQuery();

endtime = System.currentTimeMillis();

logger.debug("Time taken to execute socn query for download: " + (endtime-starttime)/1000 + "seconds");

starttime=endtime;

newtime = starttime; //praveen

///* praveen

while(rs_download_socn.next()){

socnCurrubi = rs_download_socn.getString("UBI");

if(socnCurrubi != null && socnCurrubi.length() !=0)

{

if(!socnCurrubi.equals(socnPrevUbi)){

if(!socnPrevUbi.equals("")){

ht.put(socnPrevUbi.trim(), sd);

}

sd = new SocnDetailData();

socnPrevUbi = socnCurrubi;

}

tempNumVal = rs_download_socn.getLong("COL");

if(tempNumVal==1)

sd.addToAhc(rs_download_socn.getString("AHC"));

if(tempNumVal==2)

sd.addToNetworxInventoryCode(rs_download_socn.getString("NETWORXINVENTORYCODE"));

if(tempNumVal==3)

sd.addToJurisdictionId(rs_download_socn.getString("JURISDICTIONID"));

if(tempNumVal==4)

sd.addToAccessType(rs_download_socn.getString("ACCESSTYPE"));

}

}

ht.put(socnPrevUbi.trim(), sd);

//*/

newtime1 = System.currentTimeMillis();

logger.debug("After the query, Praveen : " + (newtime1-newtime)/1000 + "seconds");

newtime = newtime1;

newtime1 = System.currentTimeMillis();

SocnDetailData socnDetailData = new SocnDetailData();

AddressData address;

AddressData billingaddress;

ArrayList ClinVos;

String tmp = "";

String qty = null;

String clinValue = null;

String receiptDate = null;

String firmOrderCommitDate = null;

String custWantDate = null;

String compDate = null;

int cnt = 0;

downloadVO = new NetworxDownloadVO();

inventoryVO = new NetworxInventoryVO();

detailVO = new NetworxInventoryDetailVO();

clinVO = new NetworxCLINVO();

while(rs_download_main.next()){

//while (cnt < 2000) {

cnt += 1;

if ((newtime1-newtime)/1000 > 2 )

logger.debug("UBI1 : " + (newtime1-newtime)/1000 + " seconds :" + cnt);

newtime = newtime1;

newtime1 = System.currentTimeMillis();

}

endtime = System.currentTimeMillis();

logger.debug("Time taken to complete formatting download vo objects: " + (endtime-starttime)/1000 + "seconds");

}catch (Exception ex) {

logger.error("Caught An exception while fetching/ populationg NetworxDownloadVOs ", ex);

} finally {

connectionPool.closeDBConnection(conn, pstm1, rs_download_main);

}

return downloadList;

}

public static String replaceAllOccurences(String stringToReplaceIn, String StringToReplaceWith, String stringToReplace){

int ind = 0;

if((stringToReplaceIn == null ) || (StringToReplaceWith == null))

return stringToReplaceIn;

if(stringToReplaceIn.length() > 0) {

ind = stringToReplaceIn.indexOf(stringToReplace);

while (ind != -1 ){

stringToReplaceIn = stringToReplaceIn.substring(0, ind)+ StringToReplaceWith + stringToReplaceIn.substring((ind+stringToReplace.length()));

ind = stringToReplaceIn.indexOf(stringToReplace);

}

}

return stringToReplaceIn;

}

}

Thanks

padala4ua at 2007-7-8 2:16:05 >

I want to make sure I understand this correctly. You are saying that the even though you are not retrieving any values from the resultSet, the following code takes 20 minutes from the beginning of the loop until you exit the loop?

while(rs_download_main.next()){

//while (cnt < 2000) {

cnt += 1;

if ((newtime1-newtime)/1000 > 2 )

logger.debug("UBI1 : " + (newtime1-newtime)/1000 + " seconds :" + cnt);

newtime = newtime1;

newtime1 = System.currentTimeMillis();

}

endtime = System.currentTimeMillis();

logger.debug("Time taken to complete formatting download vo objects: " + (endtime-starttime)/1000 + "seconds");

If that's true, how are you determining the length of time it takes to complete the query? I did notice that you have some type of timer information in this code, is this where you believe you are seeing the SQL execution being timed?

//startime = System.currentTimeMillis();

logger.debug("the main query used for download is: " + mainQuery);

//pstm1.setFetchSize(500);

rs_download_main = pstm1.executeQuery();

endtime = System.currentTimeMillis();

logger.debug("Time taken to execute main query for download: " + (endtime-starttime)/1000 + "seconds");

The reason I'm asking is that it can sometimes be difficult to determine where time is being spent in the program. One way to try and figure that out is by placing "timer" code into the code which you have done. Another more robust way is to use a profiler. Have you attempted the use of a profiler on this code to determine exactly where time is being spent? Setting up a profiler can take a little time, but it can be extremely helpful in providing accurate metrics on which to take action.

WorkForFooda at 2007-7-8 2:16:05 >

> I tried using setFetchSize but i got the

> java.lang.OutOfMemoryError

> pstm1.setFetchDirection(ResultSet.FETCH_FORWARD) ;

> pstm1.setFetchSize(1000);

I think you can use -X? command line parameters to increase the amount of memory available to your application.

-Xms<size>set initial Java heap size <-- Might use this one after you profile and determine your average needs.

-Xmx<size>set maximum Java heap size <-- Use this one to avoid the OutOfMemoryError

-Xss<size>set java thread stack size <-- Not pertinent right now

> I haven't tried a profiler yet. Thinking that it

> would take time to setup, i didn't wanna spend time

> but seems like i might have to.

> I am using the newtime and newtime1 to figure out the

> time spent and i also verified the system time from

> the time i clicked on 'download' on the screen to the

> completion. it's taking around 30 min for

> retrieving 10,000 records.

Your application server (1 Ghz) is a bit undersized if you are also running WebLogic, and it is possible that you are running into CPU issues. It is also possible that system memory is an issue. You don't have to use a profiler to determine that on a 20 minute run.I don't know what your application server (client PC?) is running, but you should be able to watch CPU and view system memory usage (any swapping or virtual memory use indicates a memory constraint issue) using some local utility.

> I shall try using a profiler now, do you have any

> preference? I really thank you for taking time to

> guide me (the issue has been banging my head since 5

> days).

I've always used a commercial profiler called JProbe from Quest software. I think you can trial the software for 15 days. It's not the cheapest software out there. Setup is easier then you might think.

> Thanks

You抮e Welcome.

WorkForFooda at 2007-7-8 2:16:05 >