aspose file tools*
The moose likes Servlets and the fly likes Fetch 10K rows from table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Fetch 10K rows from table" Watch "Fetch 10K rows from table" New topic
Author

Fetch 10K rows from table

Versha Agarwal
Greenhorn

Joined: Oct 14, 2011
Posts: 13
OS : widows 2003 Server
RAM : 4 GB
Oracle
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
Java j2sdk1.4.2_14
Struts 1.1
Tomcat 5.5
Jsp

For my web application I want to fetch all the rows from product table to generate invoice.
I want to take all the records on client side and then add one row at a time
when needed by using javascript.
My query does not take time if I run it on SQL (get the output within second).
But In my web application I have following steps:
1. Run the query
2. Populate product object(s) for 10K rows in loop
3. After that JSP page is displayed by using <logic:iterate> Tag
This whole process takes 1 min 55 sec(s), around 2 min(s).
This time is too long if user is waiting for a form to get displayed.
Is there any other way to populate product object directly from query?
Query I tried:
SELECT PRO_ID, NVL(PRO_CODE,'') As PRO_CODE,
NVL(PRO_DESC, '') AS PRO_DESC,
NVL(PUR_PRICE, '') AS PUR_PRICE,
NVL(SALE_PRICE, '') AS SALE_PRICE,
NVL(CRNCY, '') AS CRNCY
FROM PRO p ORDER BY PRO_CODE,PRO_DESC ;


Table structure
CREATE TABLE "TM"."PRO"
( "PRO_ID" NUMBER, "PRO_CODE" VARCHAR2(30),
"PRO_DESC" VARCHAR2(500), "PUR_PRICE" NUMBER(20, 2),
"SALE_PRICE" NUMBER(20, 2), "CRNCY" VARCHAR2(10),
UNIQUE ("PRO_ID") VALIDATE ,
PRIMARY KEY ("PRO_CODE") VALIDATE )
ORGANIZATION INDEX TABLESPACE "SYSTEM"
INITRANS 2 MAXTRANS 255 STORAGE
( INITIAL 64K FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
LOGGING;
Valor Tauri
Greenhorn

Joined: Dec 13, 2012
Posts: 1
Try disabling the query log.
Versha Agarwal
Greenhorn

Joined: Oct 14, 2011
Posts: 13
how to disable query log.
Is it a good idea to disable the log.
Richard Tookey
Ranch Hand

Joined: Aug 27, 2012
Posts: 1111
    
  10

I can't believe that you need to display 10,000 products on one invoice (I should have such a customer) so presumably you actually only need a few of them for each invoice. The solution is to select, using SQL, only those products needed for each particular invoice.

There is a another reason for not loading all the products every time you want to create an invoice. In the Servlet and JSP world memory is a scarce resource so what would happen if say a hundred or more of your customer all want an invoice at about the same time?
Versha Agarwal
Greenhorn

Joined: Oct 14, 2011
Posts: 13
I want to take all the records on client side and then display one row at a time (I want all rows in memory at beginning and then display the needed product row when needed) by using javascript/CSS
Richard Tookey
Ranch Hand

Joined: Aug 27, 2012
Posts: 1111
    
  10

versha Ag wrote:I want to take all the records on client side and then display one row at a time (I want all rows in memory at beginning and then display the needed product row when needed) by using javascript/CSS


So your users are going to be able to scroll through 10,000 records. How long before they get bored ? Even if this made sense then since presumably this product list is immutable then you should only need to load the product list when the Servlet is loaded so that it is available to all without further database access. Also, since you are going to use javascript to display the records then presumably you are not going to pass 10,000 records all at once and you are going to get the records on demand using something like Ajax. If so then Ajax calls can get the results direct from the database on demand rather than getting them from an in memory collection.

Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16305
    
  21

Richard Tookey wrote:
versha Ag wrote:I want to take all the records on client side and then display one row at a time (I want all rows in memory at beginning and then display the needed product row when needed) by using javascript/CSS


So your users are going to be able to scroll through 10,000 records. How long before they get bored ?


Bored, nothing. After the first hundred or so rows, my eyeballs begin to bleed.

Then there's the network overhead. A page that size will take forever to download. People will start screaming if they accidentally hit a button that causes a redisplay. Some browsers might crash, and the RAM usage on the server will go through the roof.

Customer surveys are for companies who didn't pay proper attention to begin with.
Versha Agarwal
Greenhorn

Joined: Oct 14, 2011
Posts: 13
I am also planning for Ajax, because if I take 10 K records in memory system will go very very slow.
But never worked with Ajax.
Any one can give link for Ajax as per my requirement
gurunath pai
Greenhorn

Joined: Dec 10, 2012
Posts: 13

better to retrieve the records on set of batch's so that, so that when the client scroll down the later records are retrieved.


“Anyone who has never made a mistake has never tried anything new.” ― Albert Einstein
Richard Tookey
Ranch Hand

Joined: Aug 27, 2012
Posts: 1111
    
  10

versha Ag wrote:I am also planning for Ajax, because if I take 10 K records in memory system will go very very slow.
But never worked with Ajax.
Any one can give link for Ajax as per my requirement


Google is a good starting point and the Wikipedia entry is the first on my list. This has load of references.

I hope you have abandoned the nonsense concept of your users scrolling through 10,000 records to find the few they want!
Arun Giridhar
Ranch Hand

Joined: Mar 10, 2012
Posts: 149

Why can't you use Pagination ? . There is a beautiful Article written by a rancher Look into this and DB side this.


hate Professionalism . Join the http://2014.hack.lu/index.php/Main_Page
Versha Agarwal
Greenhorn

Joined: Oct 14, 2011
Posts: 13
I just want to input the product code and retrieve the record from database.
Richard Tookey
Ranch Hand

Joined: Aug 27, 2012
Posts: 1111
    
  10

versha Ag wrote:I just want to input the product code and retrieve the record from database.


So have you abandoned the requirement for the user to scroll though all 10,000 products? As you are aware, fetching one product at a time via product code requires a way to select the product. Using a dirty great list or even a paged list of 10,000 items is going to be clumsy at best. One normally needs some for of search engine that will allow the user to type in a search criteria and then get a list of those products that match the criteria. This way only a limited amount of scrolling is required.

The search engine can be quite simple consisting of a set of drop down lists. Starting with a primary list one select from than and a sub-list is displayed (this is where Ajax comes in). This process can be repeated several times to home in on the desired product.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Fetch 10K rows from table