Arikkan
asked on
Making an Oracle ADO.Net query work faster on VB.Net
Hi,
I have to insert about 100K records in my database, so I am using a transaction in my loop.
The transaction commits after every 25K records, so it should hit the database only 4 times.
When I am running the loop with ExecuteNonQuery() commented out, the loop runs in 30 seconds for 100K records.
But when I enable it, it runs pathetically slow (20 records per second).
I have no idea why so much difference.
So, i removed the transaction and ran it, it does the same (20 records per second).
I mean it should hit the database only 4 times with the transaction, so why would this happen.
I am confused.
Any ideas?
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
Dim cvrCom As CvrCommand
Dim cvrTrans As CvrTransaction
Dim sbdSql As StringBuilder = New StringBuilder
Try
cvrCom = New CvrCommand
MyBase.getCvrCommand(cvrCo m)
'Begin the Transaction
cvrTrans = MyBase.getCvrTransaction
cvrCom.Transaction = cvrTrans
'Get the File from Local path and process the records
Dim objFileStream As FileStream = New IO.FileStream(fullFilePath , FileMode.Open, FileAccess.Read, FileShare.Read)
Dim objStreamRead As StreamReader = New IO.StreamReader(objFileStr eam)
'Read the file
Do While objStreamRead.Peek() >= 0
strData = objStreamRead.ReadLine
Me.ProcessFileLineData(cvrCom, sbdSql, strData)
If ((successCount > 0) AndAlso (successCount Mod 25000 = 0)) Then
cvrTrans.Commit()
cvrTrans = MyBase.getCvrTransaction()
End If
successCount = successCount + 1
Loop
cvrTrans.Commit()
objStreamRead.Close()
objFileStream.Close()
Catch ex As Exception
'Exception Handling
End Try
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
Private Sub ProcessFileLineData(ByRef objcvrCom As CvrCommand, ByVal sbdSql As StringBuilder, Byval strData as String)
Dim vstring() as String
vstring = strData.Split({vbTab}, StringSplitOptions.None)
sbdSql.Length = 0
sbdSql.Append(" INSERT INTO TABLE.......... (Blah).........")
With objcvrCom
.CommandText = sbdSql.ToString()
.CommandType = CommandType.Text
.Parameters.Clear()
.ExecuteNonQuery()
End With
End Sub
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
I have to insert about 100K records in my database, so I am using a transaction in my loop.
The transaction commits after every 25K records, so it should hit the database only 4 times.
When I am running the loop with ExecuteNonQuery() commented out, the loop runs in 30 seconds for 100K records.
But when I enable it, it runs pathetically slow (20 records per second).
I have no idea why so much difference.
So, i removed the transaction and ran it, it does the same (20 records per second).
I mean it should hit the database only 4 times with the transaction, so why would this happen.
I am confused.
Any ideas?
--------------------------
Dim cvrCom As CvrCommand
Dim cvrTrans As CvrTransaction
Dim sbdSql As StringBuilder = New StringBuilder
Try
cvrCom = New CvrCommand
MyBase.getCvrCommand(cvrCo
'Begin the Transaction
cvrTrans = MyBase.getCvrTransaction
cvrCom.Transaction = cvrTrans
'Get the File from Local path and process the records
Dim objFileStream As FileStream = New IO.FileStream(fullFilePath
Dim objStreamRead As StreamReader = New IO.StreamReader(objFileStr
'Read the file
Do While objStreamRead.Peek() >= 0
strData = objStreamRead.ReadLine
Me.ProcessFileLineData(cvrCom, sbdSql, strData)
If ((successCount > 0) AndAlso (successCount Mod 25000 = 0)) Then
cvrTrans.Commit()
cvrTrans = MyBase.getCvrTransaction()
End If
successCount = successCount + 1
Loop
cvrTrans.Commit()
objStreamRead.Close()
objFileStream.Close()
Catch ex As Exception
'Exception Handling
End Try
--------------------------
Private Sub ProcessFileLineData(ByRef objcvrCom As CvrCommand, ByVal sbdSql As StringBuilder, Byval strData as String)
Dim vstring() as String
vstring = strData.Split({vbTab}, StringSplitOptions.None)
sbdSql.Length = 0
sbdSql.Append(" INSERT INTO TABLE.......... (Blah).........")
With objcvrCom
.CommandText = sbdSql.ToString()
.CommandType = CommandType.Text
.Parameters.Clear()
.ExecuteNonQuery()
End With
End Sub
--------------------------
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>So, how can I make sure that I hit the DB only 4 times?
Not sure you can. Using Oracle I don't know how you can pass 25,000 SQL statements at once.
Maybe create a stored procedure in Oracle that accepts XML as input. Take the 25,000 values and create a large XML document. Then have the stored procedure parse the XML into individual DML statements?
>>I cannot change the .Net access provider as this is project is in production.
I'm not sure what that has to do with changing things. To get this working the way you want it, something will have to change so why not the drivers at the same time?
Not sure you can. Using Oracle I don't know how you can pass 25,000 SQL statements at once.
Maybe create a stored procedure in Oracle that accepts XML as input. Take the 25,000 values and create a large XML document. Then have the stored procedure parse the XML into individual DML statements?
>>I cannot change the .Net access provider as this is project is in production.
I'm not sure what that has to do with changing things. To get this working the way you want it, something will have to change so why not the drivers at the same time?
Should also point out that loading data from a text file into Oracle can be done much more efficiently with SQL Loader or External Tables.
ASKER
Can I use dataAdapter?
Will that help?
Will that help?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
loop runs in 30 seconds for 100K records.
this is really really slow !
go with slightwv's recommendation for
uploading 2 million records takes between 10 and 20 seconds with Sql*Loader
on a commodity windows server
as this is project is in production.
if something is wrong in production, then it's because not all angles were looked at or known
a common practice in massive data uploads is never to include the oracle dba in the project
with the common problem that performance issues arise in production system during upload
this is really really slow !
go with slightwv's recommendation for
uploading 2 million records takes between 10 and 20 seconds with Sql*Loader
on a commodity windows server
as this is project is in production.
if something is wrong in production, then it's because not all angles were looked at or known
a common practice in massive data uploads is never to include the oracle dba in the project
with the common problem that performance issues arise in production system during upload
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I used a better machine for the Code and DB and now it is much faster.
About 1 million records in 15 sec.
Thanks everyone.
About 1 million records in 15 sec.
Thanks everyone.
ASKER
@slightwv: There is a command "INSERT ALL" in ORACLE that is used to insert many records at once.
>>There is a command "INSERT ALL" in ORACLE that is used to insert many records at once.
I had forgotten about that syntax. Are you stating that you used it to pass in 25,000 records? If so, I'm impressed that it worked.
I had forgotten about that syntax. Are you stating that you used it to pass in 25,000 records? If so, I'm impressed that it worked.
ASKER
Yes I used that and also tried with a lower number. It worked. Thanks. :-)
ASKER
So, how can I make sure that I hit the DB only 4 times?