wood burning stoves*
The moose likes Performance and the fly likes Not able to generate xl sheet for 30k+ records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Not able to generate xl sheet for 30k+ records" Watch "Not able to generate xl sheet for 30k+ records" New topic
Author

Not able to generate xl sheet for 30k+ records

Anantha Reddy
Greenhorn

Joined: Jun 24, 2008
Posts: 12
Hi,

We are facing issues while genarating a xl with 30000+ records from a JSP.

Generationg error:
CORBA TRANSACTION_ROLLEDBACK 0x0 No; nested exception is:
org.omg.CORBA.TRANSACTION_ROLLEDBACK: javax.transaction.TransactionRolledbackException: ; nested exception is:
javax.transaction.TransactionRolledbackException: Transaction is ended due to timeout vmcid: 0x0 minor code: 0 completed: No

Application Architecture similar to Struts Framework, find below navigation wayforword and backwords for request and response respectively.

JSP->Action Class-->SLEJB-->submoduleSLEJB-->DAX->DB.
AppServer: Websphere 6.0.2.21
JDK: IBM JDK1.4.2

Client inactivity timeout :600 sec.
Total Transaction lifetimeout: 600 sec.
Maximum transaction Timeout: 300 sec.

Min and Max Heap: 256 and 1462.

>>firing query featching results putting into hashtable passing it to JSP, JSP will be genarating xl file. Query time to featch result from DB (oracle 10G) 1 min only in production.

Can any one sugeest how resolve the issue
1. by tunning parameters (what all parameters required to tune to what extent)
2. What is the right design for that requirement, at times query may have to featch 100000+ records, each record is having 40 columns.


would appriciate if any one shed lite on this.

Thanks
Anantha
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41112
    
  45
If it's really the amount of data that's causing the timeout -and you should do timings to determine whether that's the case- then it may be better to perform the processing asynchronously. In other words, have the JSP kick off the process of generating the file in a background thread, but then returning. The background process can later notify the user when the file is ready (maybe by email).


Ping & DNS - my free Android networking tools app
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15957
    
  19

You mean that there are *2* people on this planet using CORBA?

As I read this, your backend is invoking CORBA to obtain the data, but the data is so voluminous - or its access time is so long - that by the time it's ready to ship back to the querent, CORBA has decided that it's not going to get an answer at all (timeout).

You can probably tune the CORBA timeout interval. You probably SHOULD tune the CORBA backend, if you can. But it's probably not a bad idea to break the data retrieval into chunks and pull over a thousand records (more or less) at a pop. That will not only get rid of timeout issues, it will also make it easier to present a progress indicator if you want to and make it possible to terminate retrieval in mid-transmission instead of just hanging until it's all done.


Customer surveys are for companies who didn't pay proper attention to begin with.
Pat Farrell
Rancher

Joined: Aug 11, 2007
Posts: 4646
    
    5

Originally posted by Tim Holloway:
You mean that there are *2* people on this planet using CORBA?

You mean *still* using CORBA.

I used it in the early 1990s.

And I can assume there are still legacy applications using it. But for at least a decade, I would never start new code using CORBA.

Send messages, not remote procedures calls. Use REST.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15957
    
  19

Actually, in my case it isn't "still". In fact, I was snubbed back in that day, not claiming 15 years CORBA experience in 1997 when everyone was demanding it. But I've inherited a project that was not only CORBA-based, but designed and implemented by Windows people with a C++ background (shudder).

I can't complain too loudly, since I was committing a lot of the same offenses back then myself (except for the CORBA part). But CORBA and firewalls are not a good combination.

Still, if I deliver, I might win a bid to convert the project to a more traditional webapp. In the meantime, I grin and bear it.
Anantha Reddy
Greenhorn

Joined: Jun 24, 2008
Posts: 12
Hi,

We are not using CORBA at all, it is Application server throwing Tx timeout related exceptions.

Present Design:
Jsp > use for submit query, when slelet all option that particular query has to featch 450 locations data(more than 40k records).

EJB>DAX >> if we fire query on toad it is taking 15 -20 sec,

same query if we fire thru java code and featch the result, iterate the ResultSet, get and put the result in to HashTable, pass the HashTable back to presentation layer and in jsp setting content type as xls and generating the xls.

but thru java code it is not working, giving exception Transaction time out.

is there any possible and fiesable approch get the huge reults near around 1 Lac records and generating Xls file?.

We have thought generating xls through Oracle Utilities place those files in one loaction on server, develop a screen to download to client location. but it seems not that good solution.

Requirement: When user sumbit request for xls, UI should be showing wait status/progress bar, presently it is there, and it should successfully generate xls file after few mins without any exceptions so that user can save that xls report on his system.

any work aruond/any solution to serv the purpose would highly appreciable.

Thank You.
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 860
same query if we fire thru java code and featch the result, iterate the ResultSet, get and put the result in to HashTable, pass the HashTable back to presentation layer and in jsp setting content type as xls and generating the xls.

As is always the case with performnance tuning first you need to figure out where the performance problem lies. You need to profile your application. You mention a lot of steps above, any one of them or something else could be your problem. JAMon can help you hear, as well as many other profiling tools.

You can also remove parts of your code and see if the problem goes away. For example perform the query, but don't return the data. Simply loop the resultset, but don't put it in the hashtable. Not sure how you are converting the hashmap to an excel spreadsheet. You could try to return the data as html and then simply change the content type to 'application/vnd.ms-excel'

Also in your orginal posting you called an excel spreadsheet xl and I assumed that meant xml. Without being specific it is difficult for us to help you.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15957
    
  19

You may not think you're using CORBA, but your stack trace says you are. It may be you used some sort of high-level design system that setup the CORBA infrastructure underneath your application. I think you're going to have to analyse the application to see what's actually there.

There's another problem, potentially. 100,000 records is more than some versions of Excel can manage. For most of its existence, the maximum allowable number of rows was 65,536.

I normally expect spreadsheets to be a focussing tool when used in conjunction with a database, so I try to limit what actually comes back to the spreadsheet, OLAP-style. But I've had to address similar problems myself.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Not able to generate xl sheet for 30k+ records
 
Similar Threads
javax.transaction. TransactionRolledback Exception: CORBA
Need help on optimizing JDBC configurations
how can we increase the tranasaction time for ejb?
Getting Time out issue in Webservice call
javax.transaction.Transaction Rolledback Exception: CORBA