multiple inserts

[Bruce Whitehouse]

Hoping someone can help with an optimisation query...

I'm looping through an array, doing thousands of inserts. At present for
every iteration of the loop I'm creating a new command object, and opening
and closing the connection object, something along the lines of....

For Each objAS400Data In arrAS400
objCommand = New OracleCommand(strPackageName, objConnection)

objCommand.CommandType = CommandType.StoredProcedure

objCommand.Parameters.Add(New OracleParameter("IN_PARAMETER_ID",
OracleType.Int32)).Direction = ParameterDirection.Input

objCommand.Parameters("IN_PARAMETER_ID").Value = objParameter.Value

Try

objConnection.Open()

boolReturn = objCommand.ExecuteNonQuery

If Not (objCommand.Parameters("out_errMsg").Value Is DBNull.Value)
Then

strError += CStr(objCommand.Parameters("out_errMsg").Value)

boolReturn = False

objConnection.close()

objCommand = Nothing

Exit For

End Try

objConnection.close()

objCommand = Nothing

Next



Whilst this all works, I can't help but get the feeling that instantiating
all these objects, and opening/closing the connection is slowing things
down. Is there a better, more efficient way of doing this?

regards,

Bruce

 

[Miha Markic]

Hi Bruce,

Yes, very ineficient..
You might first open the connection and create command object and then run
the loop.
Inside the loop you should set parameter value and invoke ExecuteNonQuery
method for each iteration.

HTH

 

[Bruce Whitehouse]

Miha

As simple as that eh? Haven't tried it yet, but it sounds so obvious. I
think I must have spent far too long worring about how clean my AS400 data
is.

thanks again
Bruce

 

[Bruce Whitehouse]

Miha

Forgot to add one point. At the start of each iteration of the loop I
don't know if its to be an insert or an update. Something more like...

For Each objAS400Data In arrAS400
if boolInsert(objAS400Data) then
strPackageName = "PKG_BULK.INSERT"
else
strPackageName = "PKG_BULK.UPDATE"
end if
objCommand = New OracleCommand(strPackageName, objConnection)
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Parameters.Add(New OracleParameter("IN_PARAMETER_ID",
OracleType.Int32)).Direction = ParameterDirection.Input
objCommand.Parameters("IN_PARAMETER_ID").Value = objParameter.Value
Try
objConnection.Open()
boolReturn = objCommand.ExecuteNonQuery
If Not (objCommand.Parameters("out_errMsg").Value Is DBNull.Value)
Then
strError += CStr(objCommand.Parameters("out_errMsg").Value)
boolReturn = False
objConnection.close()
objCommand = Nothing
Exit For
End If
End Try
objConnection.close()
objCommand = Nothing
Next

to take the command object out of the loop would be difficult as the command
object is set up against the package name. So I thought about creating two
command objects, but then theres the problem of assigning the parameters to
the correct command object. Would I have to duplicate the parameter
setting and call the correct line of code using a select, or is there a more
efficient way of doing it?

Bruce

 

[Miha Markic]

Hi Bruce,

You might do something like (pseudocode):
objCommandI = New OracleCommand("PKG_BULK.INSERT", objConnection)

objCommandU = New OracleCommand("PKG_BULK.UPDATE", objConnection)

objCommandI.Parameters.Add(New OracleParameter("IN_PARAMETER_ID",
OracleType.Int32)).Direction = ParameterDirection.Input
objCommandU.Parameters.Add(New OracleParameter("IN_PARAMETER_ID",
OracleType.Int32)).Direction = ParameterDirection.Input

for loop
dim objCommand as ..
if insert
objCommand = objCommandI
else
objCommand = objCommandU
objCommand.Parameters("IN_PARAMETER_ID").Value = objParameter.Value
try
....

 

[William \(Bill\) Vaughn]

I would use DTS for this. ADO makes a terrible bulk copy utility--or at
least the versions pre 2.0 do.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________