Link to home
Start Free TrialLog in
Avatar of Arikkan
ArikkanFlag for United States of America

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(cvrCom)

                '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(objFileStream)

                '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
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Arikkan

ASKER

I cannot change the .Net access provider as this is project is in production.

So, how can I make sure that I hit the DB only 4 times?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>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?
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.
Avatar of Arikkan

ASKER

Can I use dataAdapter?

Will that help?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Arikkan

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.
Avatar of Arikkan

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.
Avatar of Arikkan

ASKER

Yes I used that and also tried with a lower number. It worked. Thanks. :-)