 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / September 2006Limiting Large Result Sets
Hi, I would be glad if someone could provide solution for this problem. working ENV: Jdeveloper 10.1.3 - Development Oracle 9i - Database Windows XP - OS I want to retrieve about 30 rows at a time . The table contains over 2 million rows. I would like to begin at the first row and drag 30 rows over the network. Then get the next 30 and repeat the process untill all rows are displayed. I made 2 approaches for this: 1) cache based 2) query based Using cache based approach, i was running out of memory error because all millions rows cannt be cached. Thats obvious. This kind of approach was working good for 10,000s of rows. Using query based approach, i was able to retreive all the million rows by limiting the query. I run the query everytime to get the rows. This is working absolutely fine but performance is not really good. And this kind of approach is not acceptable at my work place. So, i need to do work with resultset itself. I tried to set max rows on statement using: prepareStatment.setMaxRows(10); resultset = prepareStatment.executeQuery(); Here, i can obtain first 10 rows but if i need to get from 11- 20 and 20-30 rows and so on.......how do i achieve it???? Any other suggestions are also acceptible. Looking forward for the reply. Thanks. Have you looked into the limit statement? Oracle supports this. With limit you can tell which row to start on and how many rows to retrieve: LIMIT <starting_row>, <rows_to_retrieve> You could then setup your prepared statement like so: PreparedStatement st = con.prepareStatement("SELECT * FROM myTable WHERE cust_num = ? LIMIT ?, 10"); Keep incrementing some counter variable, i.e. starting_row, and setting it using st.setInt(2, <starting_row>) until there are no results. You would increment starting_row by 10 (or your desired row count) until there are no results. This is not globally supported. I know MySQL supports it but SQL Server (for example) does not (or didn't in SQL Server 2000). HTH > Have you looked into the limit statement? Oracle supports this. With > limit you can tell which row to start on and how many rows to retrieve: > > LIMIT <starting_row>, <rows_to_retrieve> Not my Oracle 9i. For many year rownum has been the answer. Arne > working ENV: > Jdeveloper 10.1.3 - Development [quoted text clipped - 5 lines] > over the network. Then get the next 30 and repeat the process untill > all rows are displayed. Try look at rownum ! http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html Arne Hi! I'm, not quite sure whether I understood you right. However, did you have a look at the method Statement.setFetchSize(int rows)? The api doc reads as follows: * Gives the JDBC driver a hint as to the number of rows that should * be fetched from the database when more rows are needed. So if you query the database for all rows and set the fetch size to 30, then this should be, what you want. Ralf. The dilemma is via JDBC there's no way to tell it where to start (i.e. send me only 30 rows starting at row 10) only how many to return. > Hi! > [quoted text clipped - 9 lines] > > Ralf. Hi, The RowSet models (JSR-114), on top of JDBC (or integrated in the Oracle case) are what you are looking at. I described these fully with working examples in my book. And will post a related entry on my blog over the labor day week-end; stay tuned. Kuassi, blog http://db360.blogspot.com/ > Hi, > [quoted text clipped - 35 lines] > > Thanks. Thanks for every ones replies the make the question more clear this what i wanted from the code I have a method which can accept a SQL query as input parameter and number of results the user wants public RowSetDynaClass getResults(String sql,int resultNumber) { prepareStatment= conn.prepareStatement(sql.toString()), ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY); prepareStatment.setMaxRows(resultNumber); resultset = prepareStatment.executeQuery(); rowsdc = new RowSetDynaClass(resultset); } So the user gets back a result for the first 10 rows, now the user again requests another set of 10 rows. This method is again called and here is the real problem starts he now wants to get results from 11-20. Simply put the question is how to maintain the state of the results with out modifying the query the user inputs?? CachedRowSet and derived rowset models allow you to chunk large result sets by populating a CachedRowSet object (or a WebRowSet) with 10 rows at a time and starting row#11 of the result set. Here is a code snipet excerpted fom my book. OracleCachedRowSet ocrs = new OracleCachedRowSet(); ocrs.setPageSize(10); // chunk size ocrs.populate(rset, 11); // populate cached row set, 10 rows at a time // starting with row # 11 // Here you can consume the cached rowset As promised, i have posted an introductory entry on my blog (more details and code samples in my book) Kuassi - blog http://db360.blogspot.com/ > Thanks for every ones replies the make the question more clear this > what i wanted from the code [quoted text clipped - 15 lines] > Simply put the question is how to maintain the state of the results > with out modifying the query the user inputs?? Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.