Servlet hang when performing to much insert function

[michael yue]

hi, I have done servlet that perform inserting into a table. The problem is that when inserting to much data in a loop, the connection just hangs there. My servlet is like this. When user put in 1 in first number and 100 in last number and AA in the prefix field, the servlet will insert records from AA1 till AA100 in a loop. It still works fine if the amount to be inserted is less than 100 but the connection hangs up when the amount exceed 200 or sometimes 150. I need this servlet to sometime insert more than 1000 records. Anyone have any ideas to improve on the code. Below is the for loop code for insert records. Is too much of insert records at once too overwhelming for the database connection?
for(int count=intfirstno; count<=intlastno; count++){
String strnum = String.valueOf(count);
String covernoteno = prefixno+strnum;
strsql = "SELECT cncvnoteno FROM covernote WHERE cncvnoteno=?";


pstmtselcv = cnt.prepareStatement(strsql);
pstmtselcv.setString(1,covernoteno);
rst0 = pstmtselcv.executeQuery();

if(rst0.next()){

noduplicate = 1;//got same
break;
}//while

if(noduplicate==0) {


strsql = "INSERT INTO covernote (cncvnoteno,cncompcode,cneffectivedate,cnprodcode,cnstatus,cninsertdate,cnagentcode) "+
"VALUES(?,?,?,?,?,?,?)";
pstmt2 = cnt.prepareStatement(strsql);
pstmt2.setString(1,covernoteno);
pstmt2.setString(2,compcode);
pstmt2.setString(3,effdate);
pstmt2.setString(4,prodcode);
pstmt2.setString(5,"0");
pstmt2.setTimestamp(6,currenttime);
pstmt2.setString(7,"Public");
okconf = pstmt2.executeUpdate();

}
}//for

[Vladimir Ergovic]

You need to be more specifc (what database, driver, servlet container, OS). You can try to add inserts in the batch but I doubt that it can help you.

[Mike Curwen]

I agree we need more information, including exactly what you mean by "just hangs".

But here's some code improvements...

// don't re-prepare the statements within the loop
// String + int = String.  Don't use a wrapper method
// don't use break when if/else will do. (or !if)
strsql = "SELECT cncvnoteno FROM covernote WHERE cncvnoteno=?"; 
pstmtselcv = cnt.prepareStatement(strsql);

strsql = "INSERT INTO covernote " +
         "(cncvnoteno,cncompcode,cneffectivedate,cnprodcode, " + 
         "cnstatus,cninsertdate,cnagentcode) "+
         "VALUES(?,?,?,?,?,?,?)";
pstmt2 = cnt.prepareStatement(strsql);

for(int count=intfirstno; count<=intlastno; count++) { 
String covernoteno = prefixno + count;
pstmtselcv.setString(1,covernoteno);
rst0 = pstmtselcv.executeQuery();

if(!rst0.next()){
         // if no record found, it's not a duplicate.
pstmt2.setString(1,covernoteno);
pstmt2.setString(2,compcode);
pstmt2.setString(3,effdate);
pstmt2.setString(4,prodcode);
pstmt2.setString(5,"0");
pstmt2.setTimestamp(6,currenttime);
pstmt2.setString(7,"Public");
okconf = pstmt2.executeUpdate();
} 
}//for 

[ November 21, 2003: Message edited by: Mike Curwen ]

[Mark Spritzler]

Rollin, my suggestion is to do the inserting work on the database side with a stored procedure. You are causing too much network traffic from your Servlet to the Database. If you know the begining and end, then just pass those two variables to a stored procedure that will do the rest of the work. I guarantee that you will find it sooooo muccchhh faster.
Mark

[michael yue]

The database I am using is DB2 using JDBC connection on windows98 OS. I currently close the connection and not the statement. Is this a possible cause of problem?

[michael yue]

I followed mike's advise but still encounter hangs. My jsp send info to the servlet and it stops there without closing connection and my codes clearly closes connections in finally. In short my servlet just stop working when the amount of records inserted is too big (>300 rec). below is my updated codes. Please help me someone. As for stored procedure, it is not feasible for my project and maybe will face problems since it is db2 database. Thanks
String prodcode = req.getParameter("prodcode");
String firstno = req.getParameter("firstno");
String lastno = req.getParameter("lastno");
String xeffdate = req.getParameter("effdate");
String s1=xeffdate.substring(0,2);
String s2=xeffdate.substring(3,5);
String s3=xeffdate.substring(6,10);
String effdate=s2+"/"+s1+"/"+s3;


Connection cnt=null;
PreparedStatement pstmt1=null, pstmt2=null, pstmtselcv=null;
Statement statgetdate;
ResultSet rst0=null, rstdate=null;
String strsql=null, strsql2=null, sqlselcv=null, sqlgetdate=null;
int noduplicate = 0;
int ok1=0, ok2=1, okconf=0;
try{
String url = "jdbc b2 L" + compcode;
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
cnt = DriverManager.getConnection(url, "db2inst1", "063inst1");

//compare effective date
sqlgetdate = "SELECT cseffectivedate FROM cnseries where cseffectivedate='" + effdate + "' AND cscompcode='" + compcode + "'";
statgetdate = cnt.createStatement();

rstdate = statgetdate.executeQuery(sqlgetdate);
while(rstdate.next()){

noduplicate = 2;//got same effdate
}//while
Calendar calendars = Calendar.getInstance();
java.util.Date currentdate = calendars.getTime();
long currentyeartime = currentdate.getTime();
Timestamp currenttime = new Timestamp(currentyeartime);;



if(noduplicate==0) {
int intfirstno = Integer.parseInt(firstno);
int intlastno = Integer.parseInt(lastno);
cnt.setAutoCommit(false);
strsql = "INSERT INTO covernote (cncvnoteno,cncompcode,cneffectivedate,cnprodcode,cnstatus,cninsertdate,cnagentcode) "+
"VALUES(?,?,?,?,?,?,?)";
pstmt2 = cnt.prepareStatement(strsql);


for(int count=intfirstno; count<=intlastno; count++){
String strnum = String.valueOf(count);
String covernoteno = prefixno+strnum;
//validate for same cnno
strsql = "SELECT cncvnoteno FROM covernote WHERE cncvnoteno=?";

pstmtselcv = cnt.prepareStatement(strsql);
pstmtselcv.setString(1,covernoteno);
rst0 = pstmtselcv.executeQuery();
//pstmtselcv.setString(1,compcode);

if(rst0.next()){
//String covernotex = rst0.getString("cncvnoteno");
//if(covernotex !=null)
//covernotex = covernotex.trim();
//if(covernotex.equals(covernoteno)) {
noduplicate = 1;//got same
}//while

if(noduplicate==0) {


pstmt2.setString(1,covernoteno);
pstmt2.setString(2,compcode);
pstmt2.setString(3,effdate);
pstmt2.setString(4,prodcode);
pstmt2.setString(5,"0");
pstmt2.setTimestamp(6,currenttime);
pstmt2.setString(7,"Public");
okconf = pstmt2.executeUpdate();

if(okconf==0)
ok2=0;

}
}//for
} //endif

if( noduplicate==0){

strsql = "INSERT INTO cnseries (cscompcode,csprodcode,csprefix,csfirstno,cscreatedate,cslastno,cseffectivedate) "+
"VALUES(?,?,?,?,?,?,?)";
pstmt1 = cnt.prepareStatement(strsql);
pstmt1.setString(1,compcode);
pstmt1.setString(2,prodcode);
pstmt1.setString(3,prefixno);
pstmt1.setString(4,firstno);
pstmt1.setTimestamp(5,currenttime);
pstmt1.setString(6,lastno);
pstmt1.setString(7,effdate);
ok1 = pstmt1.executeUpdate();

if(ok1==1 && ok2==1){
cnt.commit();
}
else{
cnt.rollback();
}
//close all other
if(rst0!=null)
rst0.close();
if(rstdate!=null)
rstdate.close();
if(statgetdate!=null)
statgetdate.close();
if(pstmt1!=null)
pstmt1.close();
if(pstmt2!=null)
pstmt2.close();
if(pstmtselcv!=null)
pstmtselcv.close();
//end close all other
if(cnt!=null){
if(!cnt.isClosed()){
cnt.close();
}
}
res.sendRedirect("https://"+host+"/PremiumLink/CVN/cncreate.jsp?status=success");
}
else if(noduplicate==1) {
cnt.commit();
//close all other
if(rst0!=null)
rst0.close();
if(rstdate!=null)
rstdate.close();
if(statgetdate!=null)
statgetdate.close();
if(pstmt1!=null)
pstmt1.close();
if(pstmt2!=null)
pstmt2.close();
if(pstmtselcv!=null)
pstmtselcv.close();
//end close all other
if(cnt!=null){
if(!cnt.isClosed()){
cnt.close();
}
}
res.sendRedirect("https://"+host+"/PremiumLink/CVN/cncreate.jsp?status=fail");
}
else if(noduplicate==2) {
//close all other
if(rst0!=null)
rst0.close();
if(rstdate!=null)
rstdate.close();
if(statgetdate!=null)
statgetdate.close();
if(pstmt1!=null)
pstmt1.close();
if(pstmt2!=null)
pstmt2.close();
if(pstmtselcv!=null)
pstmtselcv.close();
//end close all other
if(cnt!=null){
if(!cnt.isClosed()){
cnt.close();
}
}
res.sendRedirect("https://"+host+"/PremiumLink/CVN/cncreate.jsp?status=gotdate");
}

}catch(Exception e){ System.out.println("consub" + e.toString());}

finally{
if(cnt!=null){
try{
cnt.close();
}catch(Exception e){}
}
}

}//if submit



mikec: I put code tags around the code, but that turned out lookin' worse.
[ November 26, 2003: Message edited by: Mike Curwen ]

[Mike Curwen]

Might it simply be that your servlet response is timing out ?

Do the database updates actually occur?

I still don't know what you mean by "In short my servlet just stop working"

In what WAY does it stop working? Does the browser throw a 404? Does it not do the 'send redirect', so your JSP page submitting to the servlet seems to take forever? Is anything printed to the console from the catch statement?

That your finally block is being run makes me think the updates are all working, but your response time (because you're doing over xxxx updates) is taking too long, and by the time the servlet gets around to saying "response.sendRedirect()" the browser has since moved on to other things (like "I'm not waiting any more.. page not found!")

[Jeanne Boyarsky]

Rolin,
You should also consider doing all your inserts in a batch update instead of individual prepared statements. This will save on network traffic.

[michael yue]

Thanks for all the reply. I managed to catch the exception. It prints out:
consubCOM.ibm.db2.jdbc.app.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011
I think it means not enough of resources on my hardware part. Am I wrong?
How can I make sure that I can catch this particular exception and redirect to another error page? Can the code below works for catching this exception cause I think comparing the error code string is too long and maybe not accurate.
Thanks

catch(Exception e){ System.out.println("consub" + e.toString());

String err = e.toString();
if err.equals(COM.ibm.db2.jdbc.app.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011) {
res.sendRedirect("http://"+host+"/CVN/error2.jsp");
}
}

[brams mun]

Rollin,
fine, finally u got the exception and discuss over it with the administrator and for the next insertion use beans instead of working on the servlets and use batch updates instead of prepare statements ok,
take care.

[brams mun]

use batch insertion , try this also once

[Mike Curwen]

'on the hardware part'.. if you mean your database, then I think you're partly right.

You might have plenty of available memory on your database server, but you will have to re-configure a setting to make this monster query run. (or, try alternate approaches, two of them are mentioned in this thread)

references:
http://dbforums.com/arch/155/2003/1/671026
http://www.lsc-group.phys.uwm.edu/pipermail/lal-discuss/2002q1/000461.html