Bulk insert data from feed file to Oracle database using ASP and vbscript.

Hello Experts,
I need to improve a functionality in one of my projects. Currently i have a feature by which the users can upload a feed file from ASP/vbscript frontend to the server and then this feed is read and data is inserted row by row into the oracle database. There are about 100,000 records to be inserted and it takes around 20 to 30 minutes for this activity to complete. Given that this is a frontend activity, the time it takes is too much. Please suggest some alternative to improve the performance.
Do Until objTextStream.AtEndOfStream
			'Response.Write "<script>alert('inside do until');</script>"
				nextLine = objTextStream.Readline 
				aLine = split(nextLine, ",")
				strIns = "insert into iprism.tb_ipcg_rev_adj_balances_load " & _
					   " (account_cd, ipcg_prod_cd, ibu_cd, ytd_bal) " & _
					   " values ( '"& aLine(0) &"' , " & _
				       " '"& aLine(1) &"' , " & _
					   " '"& aLine(2) &"' , " & _
					   " "& aLine(3) &") " 
			'Response.Write "<script>alert('before insert execute');</script>"
			Conn.Execute(strIns)
			'Response.Write "<script>alert('after insert execute');</script>"
			Loop

Open in new window

LVL 8
Faheem ShaikhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

neeraj523Commented:
hello

Try somethign like this

===========================================================
count = 0
Do Until objTextStream.AtEndOfStream
nextLine = objTextStream.Readline
aLine = split(nextLine, ",")
strIns = strIns = "insert into iprism.tb_ipcg_rev_adj_balances_load " & _
  " (account_cd, ipcg_prod_cd, ibu_cd, ytd_bal) " & _
   " values ( '"& aLine(0) &"' , " & _
       " '"& aLine(1) &"' , " & _
   " '"& aLine(2) &"' , " & _
   " "& aLine(3) &"); " & vbcrlf
if count MOD 100 Then
   Conn.Execute(strIns)
   strIns = ""
End if
count = count + 1
Loop
===========================================================

This will execute 100 insert statements at one shot instead of processing one by one. This will significantly improve process time.

You can also try to increase query batch from 100 to some higher number.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Faheem ShaikhAuthor Commented:
Thanks neeraj523, a simple and easy solution. It does help me to improve the performance. But can you suggest something more to further improve this.
Thank You.
0
neeraj523Commented:
what sort of improvements you are looking further ?? the above code is written in optimum way..

if you tell me exactly what else you are looking for, i may able to help you out..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.