Inserting taking to long time System.Data.OracleClient

[vviktorsson]

Hi I hope I'm in the right group. I'm accessing a lot of data from a
WebService that returns me a DataSet. I need to insert the Data set
into an Oracle Database version 9. It's taking about an hour to loop
throug this 40.000 rows and I think thats to long. Here is how I'm
doing it:

OracleConnection _conn = new OracleConnection(this.getOracleConn());
OracleCommand _cmd = new OracleCommand("BEGIN
BLA.DATA.InsertData( :sSSN, :sYear, :nAmount, :sType ); END;", _conn);
try
{
//The call to the WebService
TheData[] tData = MyWebserviceInstance.getMyData();
_conn.Open();
for (int i = 0; i < tData.Length; i++)
{
_cmd.CommandType = CommandType.Text;

_cmd.Parameters.Add("sSSN", OracleType.VarChar);
_cmd.Parameters["sSSN"].Value = tData.SSN;
_cmd.Parameters["sSSN"].Direction =
ParameterDirection.Input;

_cmd.Parameters.Add("sYear", OracleType.VarChar);
_cmd.Parameters["sYear"].Value = tData.YEAR;
_cmd.Parameters["sYear"].Direction =
ParameterDirection.Input;

_cmd.Parameters.Add("nAmount", OracleType.Number);
_cmd.Parameters["nAmount"].Value =
tData.AMOUNT;
_cmd.Parameters["nAmount"].Direction =
ParameterDirection.Input;

_cmd.Parameters.Add("sType ", OracleType.VarChar);
if( tData.tegund == null )
_cmd.Parameters["sType "].Value = "";
else
_cmd.Parameters["sType "].Value =
tData.TYPE;
_cmd.Parameters["sType "].Direction =
ParameterDirection.Input;

_cmd.ExecuteNonQuery();
}
}
catch (System.Exception ex)
{
ex.ToString();
throw new Exception("Sorry Exception: \n" +
ex.ToString());
}
finally
{
_conn.Close();
_conn.Dispose();
_conn = null;
_cmd.Dispose();
_cmd = null;
}

It usually starts off on a good pace then the last 10-15 thousand rows
are very very slow. Does anyone out there have a better solution !?
Is there any Bulk Insert in the System.Data.OracleClient !?

Best regards

 

[vviktorsson]

It seems like this code eats up the CPU on the server!? How can I do
this diffrently!

 

[W.G. Ryan]

have you separated the pieces making sure that it's the db portion that's
causing all of the trouble?

I will say ADO.NET is not the technology I'd use here b/c it's not really
optimized for loads of this sort but assuming you can't use SSIS or another
similar approach.

So as I said in the first part, you're going to need to isolate the problem.
Are there indexes on this table? if so, you may want to drop them before
the load and recreate them after the load.

Another approach (this depends on the rowstate of each row you get back from
the webservice, but assuming that the RowState is Added), you can build a
dataAdapter and try changing the UpdateBatchSize property
http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter_members.aspx
which can help reduce the number of round trips.

Are you using the same data each time, clearing it out and then redoing it?
Or are you saying that no matter what data you use, after x it starts
slowing down?

One other thing - unless i'm misreading this, you can create the parameters
for the command and set their directions Outside of the loop. At each pass
of the loop simply set the value of the parameter. This should cut out some
of the processing here - what i mean is that at each pass, you don't need to
set the direction if it's never changing.


All in all you're going to first need to isolate what the problem is, is it
server side or client side. It may be a server issue that can only be
addressed by a DBA. I'd check the params issue though either way just b/c
that will cut out some overhead but I can't begin to guess on the impact.
Also, you may want to try using the Adapter to handle the updates and set
the batch size to a few different increasingly large intervals and see if
that has an impact. If the network is the bottleneck then it should improve
things.

 

[Guest]

Hi there!

You've got it! The problem was the parameters. I took the
_cmd.Parameters.Add and the Direction and took it outside the loop. The
result of doing that the code runs in 5-6 minutes in stead of 80-90 minutes
and the CPU is only 10-15 % max when the code is running.

Thank you very, very, very much!