Caching a Resultset

Google

Mailing List

Home

Forum Home

JBoss - Java Application Server

Tomcat - JSP/Servlet container

Struts - A MVC web framework

iText - An open source PDF Java Library

JDOM - JDOM XML Parser

JSP - A mailing list about Java Server Pages specification and reference

J2EE - A mailing list for Java(tm) 2 Platform, Enterprise Edition

J2EE Pattern - An interest list for Sun Java Center J2EE Pattern Catalog

Servlet - A mailing list for discussion about Sun Microsystem's Java Servlet API Technology

Struts & Hibernate

Subjects

•	JSP editor plugin for eclipse ?
•	org apache jasper JasperException: Unable to compile class for JSP
•	Tomcat: Connection reset by peer: socket write error
•	Cannot retrieve definition for form bean null
•	Struts Tiles Tutorial (free Struts training)
•	Where do I download Tomcat 4 0 6?
•	Data Access Object (DAO) pattern, example DAO 's
•	Where to download Tomcat v 4 1 24 from?
•	Tomcat 5 0 16 Requested resource not available
•	Subject: Servlet : Session invalidate
•	Oracle Connection Pooling in 3 2 2
•	Servlet action is currently unavailable
•	Tomcat/Struts Unicode Encoding/Decoding problems
•	Subject: Running a Simple JMS Example
•	Tomcat and webapplication specific java library path
•	Mapping in workers2 properties
•	org apache jasper JasperException
•	problem with html:text bean throwing exception
•	Cannot find message resources under key org apache struts action MESSAGE
•	Cannot find message resources under key org apache struts action MESSAGE
•	invalid direct reference problem with solution
•	Tool for jsp debug Try Sysdeo Eclipse Plugin
•	Tomcat 5 Cannot load JDBC driver class 'null ' SQL state: null
•	weblogic ejbc
•	java properties file
•	Jboss 3 2 3 Coyote Can 't re
•	Tomcat 5, Apache2 and mod jk2 integration problem
•	JBoss example problem new to J2EE
•	Value attribute of <html:checkbox
•	url string for connecting jboss to oracle
•	javax servlet ServletException: BeanUtils populate
•	5 0 18: Windows XP Pro vs Windows 2000
•	HTTP Status 404 The requested resource is not available

Caching a Resultset

Caching a Resultset

2005-02-09 - By arun

Back

Reply: 1 2 3 4 5 6

ther is another easy way of doing it ,
and it will increase the performance ,
performance is not hit by number of records you are getting,
its by scrollable resultset and using rownum like 20-40, 40-60 etc.
but if u use 1-20, 140, 1-60 your performance will be quite better.
so u have always to pick the result from first record and make result set
skip the first 20 records when you are going to result for second page
this how u can do that
1) get the total number of records using
select count(*) from table where this that
int totalRows =0;
if (ewRs.next()) {
totalRows = ewRs.getInt("totalRows");
} else {
totalRows = "0";
}

now using your actual query you have skip the records
20 records for getting 20-40 records

if (totalRows >= (iPage-1 (See http://age-1.ora-code.com))*iRow +1 )
{
for (int i=0 ; i< ((iPage-1 (See http://age-1.ora-code.com))*iRow ) ; i++)
ewRs.next();
}
here u might be knowing which page u r going , and iRow ( for u its 20) my
case it was dynamic

now next step is to get the next 20 records
for( int j=0 ; j< iRow ; j++)
{
if(ewRs.next())
{

}
}

i hope this will help
if you still need any help
ask me
Arun jassiar

-- --Original Message-- --
From: A mailing list for Java(tm) 2 Platform, Enterprise Edition
[mailto:J2EE-INTEREST@(protected)]On Behalf Of Kalra, Ashwani
Sent: Tuesday, February 08, 2005 8:56 PM
To: J2EE-INTEREST@(protected)
Subject: Re: Caching a Resultset

Do not fetch all the records from DB. Fetch certain no of records and
cache them. When user hits next button and the record is not there, hit the
database again and fetch the records again. In my application I am caching 5
pages of records. This incluldes 1 page backward , 1 current page and 3
forward pages. I keep on moving this window as user progresses to the next
page and when he hits the page which is not in cache.

/ Ashwani

-- --Original Message-- --
From: Sanjeev K.R [mailto:sanjeevkoppal@(protected)]
Sent: Tuesday, February 08, 2005 3:24 PM
To: J2EE-INTEREST@(protected)
Subject: Caching a Resultset

Hi,
We have around 20000 - 40000 of record in a table,
We are using JSP and a front controller(Servlet) and some DAO's
We have implemented Pagination also....
We have a performance set back,we are displaying 10 record at a time in
a page,
We are using the absolute potion of the ResultSet to fetch the next 10
records,
can anyone help me in achiving a better performance,
I cannot built all the 20000 objects at the same time in my DAO,
How can i cache the result set? or the query.....
My code goes some thing like this...
[
DBConnection dbConn = manager.getConnection();
Statement stmt =
dbConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_RE
AD_ONLY);
EnquireMaintainOrderVO enquireOrder=((EnquireMaintainOrderVO)query);
ResultSet rs = stmt.executeQuery(getQuery(enquireOrder));
rs.absolute(((Integer)param).intValue());
]
Please can anyone throw some light on this ASAP..

Thanks & regards
Sanjeev

-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
===========================================================================
To unsubscribe, send email to listserv@(protected) and include in the body
of the message "signoff J2EE-INTEREST". For general help, send email to
listserv@(protected) and include in the body of the message "help".

This message contains information that may be privileged or
confidential and is the property of the Capgemini Group. It is intended only
for the person to whom it is addressed. If you are not the intended
recipient, you are not authorized to read, print, retain, copy, disseminate,
distribute, or use this message or any part thereof. If you receive this
message in error, please notify the sender immediately and delete all copies
of this message.

===========================================================================
To unsubscribe, send email to listserv@(protected) and include in the body
of the message "signoff J2EE-INTEREST". For general help, send email to
listserv@(protected) and include in the body of the message "help".

===========================================================================
To unsubscribe, send email to listserv@(protected) and include in the body
of the message "signoff J2EE-INTEREST". For general help, send email to
listserv@(protected) and include in the body of the message "help".

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=ISO-8859 (See http://ISO-8859.ora-code.com)-1">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>ther
is another easy way of doing it ,</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>and
it
will increase the performance , </FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2>performance is not hit by number of records you are getting,
</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>its
by
scrollable resultset and using rownum like 20-40, 40-60 etc.</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>but
if
u use 1-20, 140, 1-60 your performance will be quite better.</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>so u
have always to pick the result from first record and make result set skip the
first 20 records when you are going to result for second
page</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>this
how u can do that</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>1)
get
the total number of records using </FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#ff0000 size=2
>select
count(*) from table where this that</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>int
totalRows =0;</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>if
(ewRs.next()) {<BR>    totalRows =
ewRs.getInt("totalRows");<BR>   } else
{<BR>    totalRows =
"0";<BR>   }</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>now
using your actual query you have skip the records</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>20
records for getting 20-40 records</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2> if (totalRows >= (iPage-1 (See http://age-1.ora-code.com))*iRow +1
)<BR>            
       
{<BR>            
            
for (int i=0 ; i< ((iPage-1 (See http://age-1.ora-code.com))*iRow ) ;
i++)<BR>           &nbsp
;             
   
ewRs.next();<BR>          
         
}</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>here
u
might be knowing which page u r going , and iRow ( for u its 20) my case it was
dynamic </FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>now
next step is to get the next 20 records</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2> for( int j=0 ; j< iRow ; j++)
<BR>            
        
{<BR>            
            
if(ewRs.next())<BR>          
             
 
{</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2>    }</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2>}</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>i
hope
this will help </FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>if
you
still need any help </FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>ask
me</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff size=2>Arun
jassiar</FONT></SPAN></DIV>
<DIV><SPAN class=124252906-09022005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-- --Original Message-- --<BR><B>From:</B> A mailing list for Java(tm)
2 Platform, Enterprise Edition [mailto:J2EE-INTEREST@(protected)]<B>On
Behalf
Of </B>Kalra, Ashwani<BR><B>Sent:</B> Tuesday, February 08, 2005 8:56
PM<BR><B>To:</B> J2EE-INTEREST@(protected)<BR><B>Subject:</B> Re: Caching a
Resultset<BR><BR></FONT></DIV>
<DIV><SPAN class=922401615-08022005><FONT face=Arial color=#0000ff size=2>Do
not fetch all the records from DB. Fetch certain no of records and cache them
.
When user hits next button and the record is not there, hit the database
again
and fetch the records again. In my application I am caching 5 pages of
records. This incluldes 1 page backward , 1 current page and 3 forward pages.
I keep on moving this window as user  progresses to the next page and
when he hits the page which is not in cache.</FONT></SPAN></DIV>
<DIV><SPAN class=922401615-08022005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><B><FONT face="Arial Narrow"><SPAN
class=922401615-08022005>/</SPAN> Ashwani</FONT></B></DIV>
<P><STRONG><FONT face="Arial Narrow"></FONT></STRONG><STRONG><FONT
face="Arial Narrow"></FONT></STRONG><STRONG><FONT
face="Arial Narrow"></FONT></STRONG><STRONG><FONT
face="Arial Narrow"></FONT></STRONG><STRONG><FONT
face="Arial Narrow"></FONT></STRONG><BR> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-- --Original Message-- --<BR><B>From:</B> Sanjeev K.R
[mailto:sanjeevkoppal@(protected)]<BR><B>Sent:</B> Tuesday, February 08, 2005
3:24 PM<BR><B>To:</B> J2EE-INTEREST@(protected)<BR><B>Subject:</B> Caching
a Resultset<BR><BR></FONT></DIV>
<DIV>Hi,</DIV>
<DIV>We have around 20000 - 40000 of record in a table,</DIV>
<DIV>We are using JSP and a front controller(Servlet) and some DAO's</DIV>
<DIV>We have implemented Pagination also....</DIV>
<DIV>We have a performance set back,we are displaying 10 record at a time
in
a page,</DIV>
<DIV>We are using the absolute potion of the ResultSet to fetch the next 10
records,</DIV>
<DIV>can anyone help me in achiving a better performance,</DIV>
<DIV>I cannot built all the 20000 objects at the same time in my DAO,</DIV>
<DIV>How can i cache the result set? or the query.....</DIV>
<DIV>My code goes some thing like this...</DIV>
<DIV>[</DIV>
<DIV><FONT size=2>DBConnection dbConn = manager.getConnection();
</FONT></DIV>
<DIV><FONT size=2>Statement stmt =
dbConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR
_READ_ONLY);</FONT></DIV>
<DIV><FONT size=2>EnquireMaintainOrderVO
enquireOrder=((EnquireMaintainOrderVO)query);</FONT></DIV>
<DIV><FONT size=2>ResultSet rs =
stmt.executeQuery(getQuery(enquireOrder));</FONT></DIV>
<DIV><FONT size=2>rs.absolute(((Integer)param).intValue());</DIV></FONT>
<DIV>]</DIV>
<DIV>Please can anyone throw some light on this ASAP..</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>Thanks & regards</DIV>
<DIV>Sanjeev</DIV>
<DIV> </DIV>
<P>
<HR SIZE=1>
Do you Yahoo!?<BR><A
href="http://us.rd.yahoo.com/mail_us/taglines/security/*http://promotions
.yahoo.com/new_mail/static/protection.html">Yahoo!
Mail</A> - You care about security. So do we.
===========================================================================
To unsubscribe, send email to listserv@(protected) and include in the body
of the message "signoff J2EE-INTEREST". For general help, send email to
listserv@(protected) and include in the body of the message "help".
<P></P></BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD bgColor=#ffffff><FONT color=#000000>This message contains
information that may be privileged or confidential and is the property
of the Capgemini Group. It is intended only for the person to whom it
is
addressed. If you are not the intended recipient, you are not
authorized
to read, print, retain, copy, disseminate, distribute, or use this
message or any part thereof. If you receive this message in error,
please notify the sender immediately and delete all copies of this
message.<BR><BR></FONT></TD></TR></TBODY></TABLE>======================
=====================================================
To unsubscribe, send email to listserv@(protected) and include in the body
of
the message "signoff J2EE-INTEREST". For general help, send email to
listserv@(protected) and include in the body of the message "help".
<P></P></BLOCKQUOTE></BODY></HTML>
===========================================================================
To unsubscribe, send email to listserv@(protected) and include in the body
of the message "signoff J2EE-INTEREST". For general help, send email to
listserv@(protected) and include in the body of the message "help".
<p>