Fetch 10K rows from table

[Versha Agarwal]

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]

Try disabling the query log.

[Versha Agarwal]

how to disable query log.
Is it a good idea to disable the log.

[Richard Tookey]

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]

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]

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]

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.

[Versha Agarwal]

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]

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

[Richard Tookey]

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!

[Mark Yevgeny]

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

[Versha Agarwal]

I just want to input the product code and retrieve the record from database.

[Richard Tookey]

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.