Uploading 4000 records in 1 minute

[Shane W]

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]

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 ?

[Shane W]

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]

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.

Sub UpdateData
 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset("INSERT INTO DB...")
 
    rs.Close
    Set db = Nothing
End Sub

[Shane W]

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]

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]

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]

"Shane Warne", please check your private messages.

[William Brogden]

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]

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]

Thanks for the update, please let us know what your final solution is.

Bill