hello buddies, i am using JSP and Oracle 9i to develop my application. i want to design a JSP page where it shouls get the data from oracle and print in the browser in a table(used <table>...</table> tags).in oracle table there are around 1000 records and i want to print all of them in the browser. i have created the jsp which has db connections and display the data, but it takes some 15 min to generate the page.what and which technology i need to do inorder to load my page faster.i have used tomcat 5.0 as webserver. plz tell which are the best practices to generate pages like from DB. plz guide me.. thanks and regards, Mahesh
First of all - you should understand how that time breaks down to different tasks. You have
1)getting the data from the database 2)compiling the data onto a jsp page 3)sending the data from the server to the client 4)rendering the html in the browser
As a former interface developer, I will comment on 3 and 4, and let others comment on the first two.
I hope this is for yourself and not for your users, because you may end up crashing some machines with that much data - and users do not like that. It is always a best practice to break information into digestable bits.
But assuming it must be this way we have to keep code at a minimum, and keep html structures simple.
NO TABLES! They are deprecated anyway, and it takes a browser extra time to output tables.
Yes, shoving hundreds or thousands of records at your users generally isn't a very useful thing to do. Investigate paging the records.
Ummm, no. They are not. Where do you hear that from?
Tables are still the best way to display tabular data. (They are however, oveused for layout where CSS would be more appropriate). Modern browsers have no problems renderring even complex tables as long as you do not nest too deeply (which usually is a sign that you are using tables incorrectly).
The 15 minute lag is most probably a network or database issue. The use of tables or nesting spans in the HTML is not going to add any appreciable time to that kind of lag. [ October 04, 2004: Message edited by: Bear Bibeault ]
If you are making DB connection every time JSP is requested, that operation might eat some time. Try using connection pooling, in which DB connections are already made and are used as requested. [ October 04, 2004: Message edited by: Jmannu gundawar ]
thanks for ur suggestions. but i am having one more doubt here. Ok now i am havig some 500 records in the resultset and i need not display all the 500 records in a single page , infact in multiple pages like first 100 in a page and next 100 in 2nd page like this.how can actually solve this. thanks & regards, Mahesh
Ok, you have done the paging but you are still doing full table scan. isn't it??
actually, it is very unlikely to do full table scan when the table has thousands of record. please make sure your database is tunned perfactly, specifically indexing could be the issue. but again avoid full table scans because indexes don't help in full table scans and it results in performance lack.
and as aforementioned, use connection pooling instead of getting connection while having request.
But best of all, tune that SQL to allow for retrieving only a subset of the data and not get all thousand records for each page of 100.
A 15 minute delay on requesting a JSP like yours is a database issue. Most likely there's a major problem with bad indices, possibly combined with connections not being closed (causing you to have to wait until they time out). Combine that with a long time needed to collect and transmit to your application server the massive amount of data you request with each query and you're bound for disaster.
You are going to have to do some measuring to see where things are slow.
I'll make a guess though. A 1000 row table shouldn't take 15 minutes to render so I would guess the query is slow (you can test this by running the query outside of your program in some sql tool), or you are concatenating strings and causing object allocation and garbage collection to be time consuming.
For example if you have a 200,000 character string and concatenate a "<td>" to it then a new string would have to be allocated to include the 200,004 characters and all data would have to be copied into here and then the old string would have to be garbage collected. If this is done a lot it is obvious how slow this could be. If you used a StringBuffer and the append method you wouldn't have this problem. Of course all this is a guess on my part and you should monitor your code to see what is consuming all the time.
i suggest you to tune the application in all the layers
Without performance measurements, developers will spend their time guessing where performance bottlenecks occur. Performance problems typically occur in a small percentage of overall code. Algorithm guru Donald Knuth estimated that 4% of application code accounts for 50% of the performance. With measurements, developers can quickly locate that 4% of the code and get the biggest bang for their tuning buck. Without measurements developers waste effort tuning code that may have no significant impact on performance.
thanks alot guys for ur valuable suggestions. it really improved that damn performance.now i can generate the jsp within seconds.i have to thankful to one and all who have contributed their info to this article. regards, Mahesh