LoadDataRow

[Igor Zhavrid]

Hi!

I have some problem with DataTable.LoadDataRow. Everething was ok untill I
added to the table autoincrement column.
There's some table
<xs:element name="Messung">

<xs:complexType>

<xs:sequence>

<xs:element name="ID" type="xs:integer" minOccurs="1"
msdata:AutoIncrement="true" msdata:AutoIncrementSeed="1"
msdata:AutoIncrementStep="1" />

<xs:element name="AKZID" type="xs:integer" minOccurs="1" />

<xs:element name="GruppeID" type="xs:integer" minOccurs="0" />

<xs:element name="StartD" type="xs:date" minOccurs="1" />

<xs:element name="EndD" type="xs:date" minOccurs="0" />

<xs:element name="UsrID" type="xs:integer" minOccurs="0" />

<xs:element name="Probenz" type="xs:date" minOccurs="0" />

<xs:element name="Updated" type="xs:boolean" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

and defined PK for it:
<xs:key name="MessungPK" msdatarimaryKey="true">

<xs:selector xpath=".//Messung" />

<xs:field xpath="AKZID" />

<xs:field xpath="GruppeID" />

<xs:field xpath="StartD" />

<xs:field xpath="EndD" />

</xs:key>

In the help it said that "
The LoadDataRow method takes an array of values and finds the matching
value(s) in the primary key column(s).

If a column has a default value, pass a null value in the array to set the
default value for that column. Similarly, if a column has its AutoIncrement
property set to true, pass a null value in the array to set the
automatically generated value for the row."

But when I do like

messRow = dtMessung.LoadDataRow(New Object() {Nothing, newRow(0), newRow(1),
newRow(4), newRow(5)}, True)

the new row is added everytime, though autoincrement ID is not in PK.

Is there solution for the problem?

 

[Mark Ihimoyan [MSFT]]

Could you please include some sample code?
Also include what result you are getting and what you are actually
expecting.

Thanks.

 

[Ilya Tumanov [MS]]

Igor,



This is by design behavior.

Only rows already in the DataTable before you execute BeginLoadData() will
be considered for update, but not rows you're currently loading.

If for some reason you load two rows with the same primary key in a single
BeginLoadData()/EndLoadData() session, both will be added and you'll get PK
constraint violation.



If you need new rows to be updated instead, do not use
BeginLoadData()/EndLoadData().

In this case PK index will be updated after each row is loaded.

Should you have another row with the same PK, it will be used to update
previously loaded row.

You should expect performance degradation as a price for live index update
in bulk data load.



Also, using such complex composite PK could lead to performance problems in
general.

Consider using this auto increment column as a surrogate primary key
instead.



Best regards,



Ilya



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ilya Tumanov [MS]]

Igor,



Are you familiar with "2 out of 3" rule? Here it is:



If it's cheap and fast, it's not good.

If it's good and fast, it's not cheap.

Finally, if it's cheap and good, it's not fast.



The point is: if you need it fast on the same platform, you have to do less
job.

Accepting changes and updating this huge composite index on every new record
certainly means more job and less performance.

You should try it, may be it's an acceptable tradeoff.



If not, you can solve this problem by eliminating the need for the extra
job.

Consider removing duplicate records from CSV file, this way you're OK.

This would also reduce file size as it won't contain redundant data and
ultimately improve performance even further.



About using Find() - I could not say if it's efficient or not as it depends
on your application.

Find is pretty fast if you have an index.

If you have, say, 10000 records it might take several minutes to build this
index, however.



Best regards,



Ilya



This posting is provided "AS IS" with no warranties, and confers no rights.