Making an Oracle ADO.Net query work faster on VB.Net


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

                     cvrCom = New CvrCommand

                '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 = MyBase.getCvrTransaction()
                        End If

                        successCount = successCount + 1

         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
     End With

End Sub
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Éric MoreauSenior .Net ConsultantCommented:
It is not hitting the database 4 times. It is committing 4 times. For the database to be hit only 4 times you would need to concatenate your sbdSql 25000 and run your command only 4 times.

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
slightwv (䄆 Netminder) Commented:
Even if it is inside a transaction I think the executenonquery still makes a round trip to the database to perform the DML.  It just isn't committed.

I would also strongly encourage you to migrate to ODP.Net as the data access provider.  The new Managed Driver doesn't require an Oracle client and is fairly stable.

For making things "faster", see if array binding will work for you:
ArikkanAuthor Commented:
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?
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

slightwv (䄆 Netminder) Commented:
>>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?
slightwv (䄆 Netminder) Commented:
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.
ArikkanAuthor Commented:
Can I use dataAdapter?

Will that help?
slightwv (䄆 Netminder) Commented:
I don't know.  I've never used a dataAdapter to perform DML.

From a quick scan of the docs, looks like it won't do what you want:

It should be noted that these statements are not performed as a batch process; each row is updated individually
Geert GOracle dbaCommented:
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
Geert GOracle dbaCommented:
on the oracle database
start an sql trace with bind vars for your exe, with the 2 methods
> you'll probably need to ask this to an oracle dba

ask for an evaluation of the files
and where time is spent between 2 inserts

it's usually the app which is slow in inserting or doing a commit between 2 inserts
for some connection components commit has to explicitely be switched off
ArikkanAuthor Commented:
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.
ArikkanAuthor Commented:
@slightwv: There is a command "INSERT ALL" in ORACLE that is used to insert many records at once.
slightwv (䄆 Netminder) Commented:
>>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.
ArikkanAuthor Commented:
Yes I used that and also tried with a lower number. It worked. Thanks. :-)
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

From novice to tech pro — start learning today.