1

The thing is this, Currently I'm developing some sort of application where I have to upload data from an excel sheet, and the save it into a database table (oracle). I managed to accomplish this process by the OracleDataAdapter class

 _adapter = new OracleDataAdapter(query, _conn);
_adapter.Update(_dataTable);

However it takes a lot of time to save the data loaded in the gridView, like 12 min for 100000 registries. Then I tried the OracleBulkCopy which indeed does the work in about 10 to 15 seconds, but the problem its that it does not respect the primary key from the database. I tried everything but it doesn't respect the constraint.

I also tried to make the insert method by myself by doing something like this:

OracleCommand cmdInsert = new OracleCommand();
cmdInsert.CommandText = query;
cmdInsert.Connection = DataAccess._conn;

OracleParameter id_Filtro = new OracleParameter();
cmdInsert.Parameters.Add(id_Filtro);

foreach (DataRow r in _table.Rows)
{              
    id_Filtro.DbType = DbType.Int32;
    id_Filtro.Value = Convert.ToInt32(r["ID_FILTRO"].ToString());
    id_Filtro.ParameterName = "id_Filtro ";
    cmdInsert.ExecuteNonQuery();
}

I did the same for every parameter, but also takes ages to insert all the data, about 25 min or so. I wonder if there is some better way to do the process. If anyone could help me I would be really grateful. I can update the post to clarify any inquiry, please notice that I'm not a database or programmer expert.

Abstract
  • 664
  • 1
  • 5
  • 15
  • `it takes a lot of time to save the data loaded in the gridView, like 12 min for 100000 registries` - You might be interested in watching [this short clip](http://www.youtube.com/watch?v=D3Y6DnFpHCA) – Federico Berasategui Feb 03 '14 at 02:38
  • 1
    BulkCopy is the right way to go for that volume. Best to separate your steps. Don't load it directly into the target table. Load it into a working table, then use a stored procedure to massage as needed into the target table. – dbugger Feb 03 '14 at 02:46
  • @dbugger would you tell me more about it? – Abstract Feb 03 '14 at 02:59
  • 1
    You've already figured out how to get the data into the database using BulkCopy. Do that, but into a working table that has the columns of your source data. Then you can use SQL statements to select the valid data and insert it into the target table. As @OracleUser stated, BulkCopy takes some shortcuts to get the data in fast, so you either need to make sure the data is pristine before you load it, or just get it in and clean afterwards. – dbugger Feb 03 '14 at 13:21

1 Answers1

0

OracleBulkCopy seems to use similar concept of SQL*Loader(Oracle utility to load bulk data faster). This utility would make the index and constraints as UNUSABLE( except Few check constraints) And re enable it after completion.

So, your index would be marked unusable after you initiated the loading process. Hence the downside, it bypasses primary key validations and prone to allow duplicates.

So try validating your datasource before loading. Procedures could be opted too. You can even attempt to make a BULK load to a temp table and do insert into select... To your target table.

Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69