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
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?
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?
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
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