Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Result Set size  RSS feed

 
Dhananjay Ghanwat
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
i am executing a SQL query using
ResultSet rs = st.executeQuery(strQry)
and while doing so my complete machine hangs . i have to restart my machine to continue any further operations
The qry fetches 3748 rows. and it takes approx
5 secs to be excuted from back end.
Please help me out with this . its very urgent
Thanx
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dhananjay,
Seeing as this is the JSP forum, I assume that you are executing your SQL query on the server side, and transferring the results to the client's web-browser, correct? If so, then you _must_ be creating some "serializable" class, populating it with the data from the "ResultSet" (from your SQL query), and sending it to the web-browser.
The size of the serializable object you are building depends on the size of the database rows you are retrieving as well as the number of rows. So even though you probably think that 3748 rows is not a lot, if each (database) row contains 50 fields and each field is a string of 200 characters and each character in java takes up two bytes, then you have (200 * 50 * 2 [= 20,000]) bytes that you are sending across the network (per row), and the total is then 20,000 * 3,748 (= 74,960,000)! Now if the JVM on your web-browser has only 20 MB of memory allocated (the usual default value), you may be in a bit of trouble!
Have you looked at the output of the "java console" to see if any error messages are being displayed? How long have you waited before deciding to restart your machine? Does this happen _every_ time you try this query?
Perhaps you should split the 3,748 rows into smaller chunks (possibly across several web-pages -- where the user needs to hit a "next" button on the web-browser in order to see the next "chunk" of the query result)?
Hope this has helped you.
Good Luck,
Avi.
 
Dhananjay Ghanwat
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanx a lot . that certainly helped me realize that i am fetching lot of data.
Actually i want to display only 10 rows per page.
What i am doing now is getting all rows and then using ResultSet.absolute() to point to the row desired and then iterate through the next 10 rows.
how can i fetch only desired 10 rows in my result set
Thanx
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dhananjay,
I don't have any experience with JSP. My application is an applet that talks with an EJB. I would suggest using servlets (instead of JSP) and maintaining a "HttpSession". There are many excellent, online resources available. Perhaps an Internet search would help? A good web-site about servlets is:
http://www.servlets.com
Good Luck,
Avi.
 
Manjunath Subramanian
Ranch Hand
Posts: 236
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dhananjay,
I believe that you have a method in the Statement interface, called setFetchSize(int rows), using which you can set the number of rows to be fetched.
I haven't tried this out, but looking at the documentation, i believe that this could help you.
Manjunath
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!