wood burning stoves*
The moose likes Servlets and the fly likes Servlet hang when performing to much insert function Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Java » Servlets
Bookmark "Servlet hang when performing to much insert function" Watch "Servlet hang when performing to much insert function" New topic
Author

Servlet hang when performing to much insert function

michael yue
Ranch Hand

Joined: Nov 20, 2003
Posts: 204
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
Ranch Hand

Joined: Apr 22, 2001
Posts: 63
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.


Vladimir Ergovic
Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

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

But here's some code improvements...
[ November 21, 2003: Message edited by: Mike Curwen ]
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
michael yue
Ranch Hand

Joined: Nov 20, 2003
Posts: 204
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
Ranch Hand

Joined: Nov 20, 2003
Posts: 204
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
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30580
    
154

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
michael yue
Ranch Hand

Joined: Nov 20, 2003
Posts: 204
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
Greenhorn

Joined: Nov 27, 2003
Posts: 25
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
Greenhorn

Joined: Nov 27, 2003
Posts: 25
use batch insertion , try this also once
Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

'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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Servlet hang when performing to much insert function