Best Practice for Inserting Many records into Oracle table??

[katiep23]

Relatively new to VB.NET coding. Am creating a pretty simple console application that needs to insert over 50,000 records into an Oracle table (OleDB connection). I tried 3 different methods so far: Individual INSERT, insert using a dataset, & using INSERT ALL (commit every 25 records due to # of columns). It seemed like most articles/posts I read recommended the dataset approach, but the INSERT ALL code was definitely the fastest by far.

Thoughts/opinions on this? Any other approaches I should look into? Looks like this program will take around 45-60 minutes to run with the INSERT ALL.

[Unhnd_Exception]

I would not recommend a data set.

Dataset is nothing but a middle man. Cut him out.

I would use the individual inserts.

example

dim con as new oledbconnection(...)
dim com as new oledbcommand()

com.connection = con
com.commandtext = "Insert Into ATable (Acolumn) values(@AColumn)
com.parameters.add("@AColumn", YourDBType)

try
    con.open
    for i = 1 to 50000
        com.parameters(0).value = "Your Value"
        com.executenonquery
    next
catch

finally
   con.dispose
   com.dispose
end try

I find it hard to believe this should take 45 to 60 minutes. I'm thinking a few minutes.

How many columns are you inserting? And what variable types?

I would like to simulate it on my machine.

You may experience delays if your table is indexed.

[katiep23]

I'm also reading table from another database to insert into the Oracle one. So it's looping through a dbReader.Read() statement to insert. About 25 columns right now & about 5-6 of them are indexed.

[katiep23]

Here is how I was doing the individual inserts (went much slower than INSERT ALL):

While dbReader.Read()
strSQL = "INSERT INTO tws_rep_sales_summary(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25) " & _
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
ORACmd.CommandText = strSQL
ORACmd.Parameters.AddWithValue("p1", "blah")
ORACmd.Parameters.AddWithValue("p2", "blah")
...
ORACmd.ExecuteNonQuery()

End While

[Unhnd_Exception]

Post your select statement.

I'll mess with it later this afternoon.

[Unhnd_Exception]

I just ran some queries.

First off I'm using sqlce. Surely oracle can't be much slower. I use microsoft products.

Heres what I came up with.

Table1 and Table2 have 25 nvarchar columns and 4 indexes

I filled Table1 with 50,000 records each column contained a random length string from 1 to 30

With individual inserts it took 49 seconds.

I filled table2 with everything in table1 using a reader to read table1

With individual inserts it took 46 seconds.

I filled table2 with 1 command

com.CommandText = "Insert Into Table2 Select * From Table1"

That filled 50,000 records into table2 from table1 in 3.5 seconds.

Not sure why yours is running so slow.

Maybe you could post some more info.

[katiep23]

Post your select statement.

I'll mess with it later this afternoon.

I doubt it will be much help, but...

strSQL = "SELECT cc.MCRP11, cc.MCRP07, cc.mcrp09, cc.mcdl01, " & _
"CASE Trim(ab.ABMCU) WHEN '1300' THEN cf.SCMCU2 ELSE ab.ABMCU END AS mcuderived, " & _
"cf.SCSLSM, ab.wwmlnm, cf.SC@CPN AS year_month, im.imsrp1, im.IMSRP2, im.IMSRP0, " & _
"sum(cf.SC@BSL + cf.SC@OSL + cf.SC@NCS ) vTotalSale, " & _
"sum(cf.SC@BSL) as vbasesale, sum(cf.SC@OSL) as vovgsale, " & _
"sum(cf.SC@NCS) as vnocommsale, sum(cf.SC@BCA) as vbasecommamt, " & _
"sum(cf.SC@OCA) as vovgcommamt " & _
"FROM F56111 cf, F4101 im, F0006 cc, twswww01 ab " & _
"WHERE CASE Trim(ab.ABMCU) WHEN '1300' THEN cf.SCMCU2 ELSE ab.ABMCU END = cc.mcmcu " & _
"AND cf.SCSLSM = ab.ABAN8 AND int( cf.SC@CPN ) >= 200801 " & _
"AND cf.SCDCTO NOT IN ( 'ST' ) AND cf.SCAEXP<>0 AND cf.scitm = im.imitm " & _
"AND cf.SCGLC NOT IN ( 'FT60', 'LITE', 'MISF', 'RSTK', 'RWRK', 'RMPS', 'LABR' ) " & _
"GROUP BY cc.MCRP11, cc.MCRP07, cc.mcrp09, cc.mcdl01, CASE Trim(ab.ABMCU) WHEN '1300' THEN cf.SCMCU2 ELSE ab.ABMCU END, " & _
"cf.SCSLSM, ab.wwmlnm, cf.SC@CPN, im.imsrp1, im.IMSRP2, im.IMSRP0 " & _
"ORDER BY cc.MCRP11, cc.MCRP07, cc.mcrp09, cc.mcdl01, CASE Trim(ab.ABMCU) WHEN '1300' THEN cf.SCMCU2 ELSE ab.ABMCU END, " & _
"cf.SCSLSM, ab.wwmlnm, cf.SC@CPN, im.imsrp1, im.IMSRP2, im.IMSRP0"

[katiep23]

Does it make a difference that I'm adding most of the parameters like this?

ORACmd.Parameters.AddWithValue("p21", DbReader("vTotalSale"))

So there is 26 of them right now... looping through at least 50,000 times.

[Unhnd_Exception]

I would use ORACmd.Parameters.AddWithValue("p21", DbReader("vTotalSale"))

when setting the parameter 1 time

If you keep changing the value of the parameter then

'add the parameter before the loop
ORACmd.Parameters.Add("p21", thedbtype)

'and when setting the value in the loop
ORACmd.Parameters("p21").value = DbReader("vTotalSale")

Your select statement may be your problem.

You may want to play around with it.

See how long it takes when having a plain old select statement and go from there.

[Unhnd_Exception]

I just notice you were setting the command text and the parameters inside the while loop. That should not be in there. Set the command text and parameters before the loop.

And try this method
com.CommandText = "Insert Into Table2 Select * From Table1"

Replace Select * From Table1 with your nasty select statement.

If table2 has the same column count as your select statement is returning and their the same db types it should map it for you.

Worth a shot.

You can execute it with 1 call
com.executenonquery

[katiep23]

Made the cmd/parameter fixes & it's still inserting at a rate of about 100 rows in a minute. Waaaaaay too slow.

I know the select query is complex; but once the data is retrieved into the reader, should the query complexity have any effect anymore? It takes a couple minutes to run; but I figured after that, it shouldn't matter anymore.

[katiep23]

And all the fields don't line up exact. There are some columns in the Oracle table that I am not using yet or that I am getting elsewhere.

[Unhnd_Exception]

Run your query with out executing the insert command.

Inside the while loop assign the values from the reader to a variable just to give it a more realistic run.

See how long its taking with no Inserts.

You can also specify the column names with the other method

com.CommandText = "Insert Into Table2 (col1,col3,col5) Select col7,col9,col10 From Table1"

As long as you supply the same number of columns to insert to that your select statement is returning it should work.

You may have to come up with a different way to filter your records. I don't think your having an Insert problem. I think it lies with your select statement. I've been through this when creating mapping software. Millions of records. I ended up having to break things down into smaller pieces to be able to process anything.

See what happens when you run it without inserting.

[katiep23]

I replaced "ORACmd.ExecuteNonQuery()" in the loop with "Console.WriteLine(intCount)" & it went completely through in about 2 minutes.

[Unhnd_Exception]

I guess I can't help you anymore.

I can insert 50,000 in 49 seconds.

Must be something with an oracle setting and i use microsoft's sql server.

I wish I could do more for you.

[katiep23]

Thanks for trying to help! :)