Improve insert performance from .NET app if number of records is huge.

We generally operate insert operation using either raw ADO.NET Command object or LINQ to SQL or any other ORM tool.These are fine and best if the number of records to insert is limited.But if you need bulk insert of thousands of records, these methods become trivial and its performance gets quite worse.In this scenario i am going to explain how to improve efficiency of your application. I have tested it on .NET 4.0 and SQL Server 2008 R2 database.
Hope same approach will be applicable to all other versions.

First of all create a Stored Procedure in your database that takes varchar type parameter. From client application XML formatted data will be sent to this Stored Procedure, where client is responsible for creating XML data that encapsulates all the data to be inserted.Stored Procedure will then process that XML data and insert those data into respective columns of table. This way processing speed is significantly improved and response time gets better.

Before proceeding further make sure that you know how to create Stored Procedure in SQL server, should know LINQ to SQL and basic concepts of .NET OOps.

Below is the Stored Procedure code that manipulated the XML data and inserts data to respective table :

CREATE PROCEDURE [dbo].[TakeAndInsertXML]
@x varchar(max)
AS
declare @xmldoc xml
declare @docHandle int
BEGIN

SET NOCOUNT ON;

set @xmldoc = Convert(XML,@x);

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmldoc;
insert t_test select * from openxml(@docHandle,'/Info/Data') with t_test;

EXEC sp_xml_removedocument @docHandle

END

And below is the .NET client application helper method that creates the required XML data(as String datatype) :

private static string CreateXML(List People)
{
StringBuilder sb = new StringBuilder();

sb.Append("<Info>");
foreach (Person p in People)
{
sb.Append("<Data Name='"+ p.Name +"'");
sb.Append(" Age='"+ p.Age +"'");
sb.Append(" />");
}
sb.Append("</Info>");
return sb.ToString();
}

Using the above helper method you can create XML data and pass it to Stored Procedure using raw ADO.NET Command objects or LINQ to SQL or any other ORM tool. Here i will explain using LINQ to SQL :

using (PeopleDataContext dc =
new PeopleDataContext())
{
List people = new List();
// Logic to populate this list ....

string data = CreateXML(people);
dc.TakeAndInsertXML(data);
}

Calling the Stored Procedure will insert all data in XML string within a moment.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s