RAM : 4 GB
Oracle9i Enterprise Edition Release 188.8.131.52.0 - Production
PL/SQL Release 184.108.40.206.0 - Production
CORE 220.127.116.11.0 Production
TNS for 32-bit Windows: Version 18.104.22.168.0 - Production
NLSRTL Version 22.214.171.124.0 - Production
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
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 ;
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)
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?
Richard Tookey wrote:
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 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!
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.