Oraclebulkcopy to CPU in thread

Crawled data, to write data in bulk to ORACLE, the first is to use the EF, processing speed is slow. The main performance is on the validation data (db. getvalidationerrors), can write 1000 per minute less than. After switching to Enterpricelibrary.validation (Validation.validate), the verification speed is greatly increased, 1000 strips in seconds. But the overall speed is still slow, because each piece of data is converted to an INSERT statement by EF. It took me some time to rewrite this part of the logic with oraclebulkcopy (need to refer to the Oracle.DataAccess.dll under the client), and 1000 writes to Oracle in just a few seconds, straight from the cart to the high-speed rail era.

1         Private voidBulkCopy (stringCONNSTR, DataTable DT,stringtblname) { 2             if(dt. Rows.Count >0) { 3                 using(varBC =Neworaclebulkcopy (CONNSTR)) { 4Bc. DestinationTableName =tblname;5                     foreach(DataColumn Colinchdt. Columns) {6 BC. Columnmappings.add (Col. ColumnName, Col. ColumnName); 7                     } 8 BC. WriteToServer (DT); 9                 } Ten             }  One}

ConnStr is the connection string for ORACLE, and tblname is the table name for the target table.

1        /// <summary> 2         ///Convert list to DataTable, only for public property in T3         /// </summary> 4         /// <typeparam name= "T" ></typeparam> 5         /// <param name= "list" ></param> 6          Public StaticDataTable todatatable<t> ( ThisList<t> list)whereR |class { 7             if(List = =NULL) 8                 return NULL; 9 Ten  OneType type =typeof(T); A             varPS = type. GetProperties (). Where (p = p.canwrite && (p.propertytype.isvaluetype | | p.propertytype.isprimitive | | p.propertytype = =typeof(String));  - Type TargetType; - Nullableconverter Nullableconvert; thelist<datacolumn> cols =NewList<datacolumn>();  -             foreach(varPinchPS) {  -                 if(P.propertytype.isgenerictype && p.propertytype.getgenerictypedefinition (). Equals (typeof(nullable<>))) {  -Nullableconvert =NewNullableconverter (p.propertytype); +TargetType =Nullableconvert.underlyingtype; -Cols. ADD (NewDataColumn (P.name, TargetType));  +}Else {  ACols. ADD (NewDataColumn (P.name, P.propertytype));  at                 }  -             }  -  -  -DataTable dt =NewDataTable (); - dt. Columns.addrange (cols. ToArray ());  in  -  toList. ForEach ((l) = {  +list<Object> OBJS =Newlist<Object>();  -Objs. AddRange (PS. Select (p = p.getvalue (L,NULL)));  the dt. Rows.Add (OBJS. ToArray ());  *             });  $ Panax Notoginseng  -             returnDT; the}

This code was written a few years ago in the era of stored procedures, and can now be used. Only the writable value types, primitive types, and strings in T are mapped into the DataTable. Because the EF-generated entity class, the navigation property is not the field in the database. Primitive types in the CLR are:Boolean, Byte, SByte, Int16, UInt16, Int32, UInt32, Int64, UInt64 , IntPtr , UIntPtr, Char, Double, and single .
Https://msdn.microsoft.com/zh-cn/library/system.type.isprimitive.aspx string is not a primitive type and is an address reference, but it is a special. In addition to string, primitive type, Value type of the property, I really can't find out which one can be mapped to the database. Local debugging no problem, put on the server, the CPU incredibly high. Generate dump file, view with WINDBG:

. Load SOS.dll !threadpool

CPU usage 96%

Look at the thread execution time:

!runaway  User Mode time   Thread       time   21:13dc      0 days 0:39:58.140   24:13d4      0 days 0:08:41.750   27:11ac      0 days 0:01:29.906    5:1250      

View the stack of line Line 21:

~21s!clrstackOS Thread ID:0X13DC (+) child SP IP call Site 000000002595e7f8 00000000777e85d7 [helpermethodframe:000000 002595E7F8] 000000002595e910 000007fe9482b688 * * * error:module load completed but symbols could not being loaded for Oracle . DataAccess.dll Oracle.DataAccess.Client.OracleTuningAgent.DoScan () 000000002595e950 000007fe9481d28f Oracle.DataAccess.Client.OracleTuningAgent.TuningFunction () 000000002595e9c0 000007fef0bdd0b5 * * * warning:unable to Verify checksum for Mscorlib.ni.dll System.Threading.ExecutionContext.RunInternal ( System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean) 000000002595eb20 000007fef0bdce19 System.Threading.ExecutionContext.Run (System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean) 000000002595eb50 000007fef0bdcdd7 System.Threading.ExecutionContext.Run (System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object) 000000002595eba0 000007fef0b50301 System.Threading.ThreadHelper.ThreadStart () 000000002595eeb8 000007fef1c7ffe3 [Gcframe:000000002595eeb8] 000000002595f1e8 000007fef1c7ffe3 [Debuggeru2mcatchhandlerframe:000000002595f1e8] 000000002595f3c8 000007fef1c7ffe3 [ CONTEXTTRANSITIONFRAME:000000002595F3C8] 000000002595f5b8 000007fef1c7ffe3 [debuggeru2mcatchhandlerframe:   000000002595F5B8]

A couple of threads were checked for the stack, all of this:
Oracle.DataAccess.Client.OracleTuningAgent.XXX

Search for a bit Oracle.DataAccess.Client.OracleTuningAgent.TuningFunction:
http://stackoverflow.com/questions/2782169/oracle-data-provider-pegs-iis-worker-process-when-web-site-is-stopped

This have been fixed in 11.2.0.2 and in Patch 9966926 ORACLE 11G 11.2.0.1 patch 5 bugs for WINDOWS (64-bit AMD64 and INTEL E M64T).
Or Workaround:is to disable-tuning by adding ' self tuning=false ' to the connection string.

I don't understand, but it mentions the version of Oracle.DataAccess.Client.
This DLL I was from the client: 11.2.0, the version is: 2.112.1.0

Self Tuning from the database connection management of VS, it can be seen that it means: To enable or disable the connection to the use of the specific effect of what is not to verify the impact. Just ignore it.  A odac121021_x64 (client) was downloaded from Oracle, 200 m, after installation, the Odp.net directory has two folders under 2.X and 4 references 4 under Oracle.DataAccess.dll (version: 4.121.2.0), Local operation is also no pressure, very perfect. Take this DLL directly to the server, the results of the CPU is not high, but ligatures do not write! Do not know whether to install the latest client, no test.  Also can not go to experiment, because the basic environment changes, will affect a large area. Even take 2.x version, in the connection string to add self tunning = False, put on the server, the data is normally written, the CPU is not high!

Oraclebulkcopy to CPU in thread