aspose file tools*
The moose likes Performance and the fly likes Uploading 4000 records in 1 minute Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Uploading 4000 records in 1 minute" Watch "Uploading 4000 records in 1 minute" New topic
Author

Uploading 4000 records in 1 minute

Shane W
Ranch Hand

Joined: Aug 31, 2007
Posts: 30
Hi All
I have to come up with a solution to upload 4000 records, each record s containing about 250 columns.I have to achieve this in 1 minute. Ours is a VB application,that does this upload via JBoss, we are doing this by XMLHttp protocol hitting my serlvet.
The data is in Access(Local) and we have to upload it to Oracle(centralised)

Need some info to achieve it.

Right now, its taking 5-10 minutes which is not acceptable.
Increasing the network speed is ruled out

The conversion of data from access to XML itself is taking close to 1 minute.
We parse this XML using SAX parser in the DAO to insert into Oracle

Shane
Rajah Nagur
Ranch Hand

Joined: Nov 06, 2002
Posts: 239
1. Switch from XML processing
2. Use CSV file instead
2. Use JDBC Batch
3. Cache the Prepared statements
4. Spawn threads to process the CSV file simultaneously
5. Remove any transaction isolation levels on the Connection object
6. Use Connection pooling (if not already used)

What is rationale behind finishing this process withing 1 Min?

Your name Shane Warne? Is it real ?


You can't wake a person who is <b><i>pretending</i></b> to be asleep.<br />Like what <b>"it"</b> does not like - <i> Gurdjieff </i>
Shane W
Ranch Hand

Joined: Aug 31, 2007
Posts: 30
Hi
Sorry for not having given a background to the problem
Actually, we have some folks who are not on company network all the time.The are our sales guys.For them, there is this age old excel application, which they use to decide on the price etc based on various parameters.Access is the DB used for them
Now when they come back,we need to upload this data for management review and other decision making process. As the sales guys don't want any change to the way they go about doing their job(ie using excel), we have to give them a buton on their excel which will upload and download data from the centralised DB.
That is why use of XMLHttp was proposed and so we fetch the data from the access db, convert to xml and send it to my servlet which in turn pushes it to the oraccle db.
Now this task can't take lot of time and after an analysis, we found that we need to update say around 4k records each time we do this synchronise thing.

We already are useing connection pooling at JBoss level.
If i use csv, how do i send it to the server?

Shane
(Not the cricket guy)
Rajah Nagur
Ranch Hand

Joined: Nov 06, 2002
Posts: 239
Then in your case to achieve optimum performance - by pass JRun.

Use VBA macros in the excel sheet which uploads the data directly to Oracle DB.

VBA is best suited for such kind of scenarios - which is for getting data from DB and updating the data back to DB.
e.g.

Shane W
Ranch Hand

Joined: Aug 31, 2007
Posts: 30
Hi
Thanks for your suggestion.I'll see if i can convince the tech folks about inserting directly without having to go through the servlet. I never understood the rational behind having JBoss in between myself, but i know it'll be a tough task to get the message thru.

Meanwhile, i'll continue to look at option to speed up the current process

Shane
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12823
    
    5
When you say "conversion of data from access to XML" - there are fast and slow ways to do this.

Slow: build a DOM for the collection of records and serialize it out

Fast: directly write the XML tags and data items in a collection of statements that write one thing at a time with NO intermediate String building.

Fastest: as with Fast but use byte[] output to a binary output stream - minimize character conversions, no String building.

Bill
Shane W
Ranch Hand

Joined: Aug 31, 2007
Posts: 30
Hi
Thanks for the suggestion.
Right now i read the access DB and for each column, I add it to the xml file with<> and <> brackets.For japanese characters, i need to convert to unicode.

Shane
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

"Shane Warne", please check your private messages.
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12823
    
    5
What have you done so far to determine which part of the problem causes the bottleneck? If this was my problem I would try:

1. Timing the fetch of the records but discarding the data.
2. Writing 4000 fake records.
3. Use JAMON toolkit

Bill
Shane W
Ranch Hand

Joined: Aug 31, 2007
Posts: 30
Hi
Thanks for your suggestion
We did do a check to find out how much time each of the process is taking.
We found that insertion into the Access DB is taking around 4 inutes, so thats the bottle neck.
We are now trying to find out ways to minimize this. As its in VB, we are now trying to use ADO queries instead of SQL queries

Shane
William Brogden
Author and all-around good cowpoke
Rancher

Joined: Mar 22, 2000
Posts: 12823
    
    5
Thanks for the update, please let us know what your final solution is.

Bill
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Uploading 4000 records in 1 minute