Devart Forums

Posted: **Tue 10 Dec 2013 22:40**

We have a project in which we sometime need to insert up to 100 new records the insert time varies from 1 to 5 seconds per insert. I was reading through the help and see to enable batch updates I need to set UpdateCheck=UpdateCheck.Never and ensure The command does not return OUT parameters using the RETURNING clause, second query, or by LastInsertId (all updated object properties have AutoSync=AutoSync.Never parameter of the column attribute);

I am not sure how to ensure The command does not return OUT parameters. I see in the DBMonitor that the inserts all have a returning statement on them but am unsure how to suppress them.

We are using Oracle 11g and are currently on version: 4.4.374 of linqconnect.

Here is an example of the object we are inserting:

Code: Select all

var udr = new NewDataRecord(); 
udr.COMP_ID = 24;
udr.GL_ACCT_UNIT_ID = 1234;
udr.COMPANY_NBR = 1111;
udr.OP_AREA_NBR = 1111;
udr.FAC_ID = 1111;
udr.YR_NBR = 2013;
udr.INV_DT = "12-NOV-2013;
udr.RTL_INV_TYP_ID = Convert.ToDecimal(3);
udr.RTL_INV_SUB_TYP_ID = 2;
udr.USR_DATA_RECORD_DATA_XML = "<row Attribute1='test' Attribute2='test2' Attribute3='test3' ID='THISID'/>";
udr.STAT_ID = 1;
udr.LAST_UPDATE_TS = DateTime.Now;
udr.MD_UPD_USR_ID = "TESTUSER";
udr.FEED_CD = "APAP";
udr.MD_LD_TS = DateTime.Now;
udr.MD_CRT_USR_ID = "TESTUSER";
udr.USR_DATA_FLD_TXT = "SOMEDATEHERE";
							
listofobjects.Add(udr); 

DataContext.ENTITYNAME.InsertAllOnSubmit(listofobjects);
DataContext.SubmitChanges();

We are currently adding between 10 and 80 of these types of objects what can we do to improve the performance of these inserts?

Thanks,
Nick

Posted: **Wed 11 Dec 2013 13:44**

I see in the DBMonitor that the inserts all have a returning statement

Please check that for all entity properties of the ENTITYNAME entity class the Auto-Sync property is not set to Always or OnInsert (except auto-generated entity key properties). For example, MD_LD_TS property should have the following properties:
Auto-Sync = Never
Auto Generated Value = False
Type = datetime and so on.

listofobjects.Add(udr);
DataContext.ENTITYNAME.InsertAllOnSubmit(listofobjects);
DataContext.SubmitChanges();

Please tell us whether the "listofobjects" variable has only one item or more than one before you passing it to the InsertAllOnSubmit method?
Please also refer to:
http://www.devart.com/linqconnect/docs/ ... mance.html

If this information doesn't help, please send us a small test project so that we are able to find a more suitable solution for you.

Posted: **Wed 11 Dec 2013 15:18**

I checked all the properties, the entity key is the only field that has Auto-Generated set to true. Auto-Sync is set to false on the key as well as all other properties.

listofobjects contains anywhere from 2 to 100 objects depending on the data a user enters.

Posted: **Thu 12 Dec 2013 09:05**

Please send us the additional information so that we are able to find a solution for you:
- the model you are working with;
- the DDL scripts for the corresponding database tables;
- the generated SQL queries ( please refer to http://www.devart.com/linqconnect/docs/?Logging.html ).

Posted: **Tue 17 Dec 2013 00:10**

I was finally able to get this work. We had to grand permission to the sequence in Oracle and then set the IdGenerator type to Sequence and specify the name of the sequence at that point the performance improved greatly.

Posted: **Tue 17 Dec 2013 06:10**

Glad to see that you have found the solution. If you have any further questions regarding working with LinqConnect, feel free to contact us.

Devart Forums

LinqConnect Insert Taking 1 to 5 seconds per record

LinqConnect Insert Taking 1 to 5 seconds per record

Re: LinqConnect Insert Taking 1 to 5 seconds per record

Re: LinqConnect Insert Taking 1 to 5 seconds per record

Re: LinqConnect Insert Taking 1 to 5 seconds per record

Re: LinqConnect Insert Taking 1 to 5 seconds per record

Re: LinqConnect Insert Taking 1 to 5 seconds per record