ADO.NET 1.1 Performance Guidelines - XML and DataSet Objects

From Guidance Share

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman

Do Not Infer Schemas at Run Time

Limit schema inference to design time. When you load a DataSet, ensure that your schema is not inferred, which can happen by default. The inference process is costly. To ensure that your existing schema is used and that no schema is inferred, pass XmlReadMode.IgnoreSchema to the ReadXml method.

Perform Bulk Updates and Inserts by Using OpenXML

Different .NET Framework data providers enable you to do bulk updates and inserts by using the OpenXML method. You can use OpenXML to minimize SQL Server database calls, because you can use the OpenXML function to insert multiple rows of data in a single database call. OpenXML enables you to effectively package data together in a single call as XML, map it to a rowset view, and execute all of the inserts within the same database call. This helps reduce calls and resource utilization. The following code fragment shows you how to use OpenXML for updates and inserts.

--This code UPDATES data.
UPDATE Employee
SET
   Employee.FirstName = XMLEmployee.FirstName,
   Employee.LastName = XMLEmployee.LastName
   FROM OPENXML(@hDoc, 'NewDataSet/Employee')
      WITH (EmployeeId Integer, FirstName varchar(100),  LastName varchar(100))  XMLEmployee
WHERE    Employee.EmployeeId = XMLEmployee.EmployeeId

--This code inserts new data.
Insert Into Employee
SELECT EmployeeId, FirstName, LastName
       FROM  OPENXML (@hdoc, '/NewDataSet/Employee',1)
WITH (EmployeeId Integer, FirstName varchar(100),  LastName varchar(100))  XMLEmployee
Where XMLEmployee.EmployeeId Not IN (Select EmployeeID from Employee)

References

• For a complete code sample that shows how to use the OpenXML method, see Knowledge Base article 315968, "HOW TO: Perform Bulk Updates and Inserts Using OpenXML with .NET Providers in Visual C# .NET," at http://support.microsoft.com/default.aspx?scid=kb;en-us;315968.
• For more information about XML and DataSet objects, see "Employing XML in the .NET Framework" in .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconemployingxmlinnetframework.asp.