Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Can I bulk insert XML variable?

Author  Topic 

Rodrigo
Starting Member

3 Posts
Posted - 2009-02-27 : 07:59:05
Hi people!

I'm having a hard time to accept I cannot improve the performance of a SP I wrote so here is my problem:

I've a .NET console application that passes an XML to an SP, this xml parameter is the only one it receives and it contains a whole table that I insert afterwards.

Now, it's only like 7000 rows but takes like 4 minutes, i don't think that's OK :(

I've found many ways to bulk insert a file into a table, but that's the thing, there is no file in my case, just an xml variable.

My question is, is there any workaround so that I can make a bulk insert from a variable, or something that allows me to boost the SP performance?

Thanks a lot!

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-27 : 08:05:03
The general answer is Yes.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Rodrigo
Starting Member

3 Posts
Posted - 2009-02-27 : 08:06:11
May I have the specific one? Please! xD
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-27 : 08:08:29
I have to see your code yo use today to give any information back.
Otherwise it is only guesses.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Rodrigo
Starting Member

3 Posts
Posted - 2009-02-27 : 08:26:42
Yes, I understand!

Ok, it's something like this:

The c# part:

public static ErrorNet UpLoadPrice(DataTable table, SqlConnection sqlConn, SqlTransaction sqlTran, string list)
{
ErrorNet errorNet = null;
StringWriter strWrtr = new StringWriter();

table.TableName = "Price";
table.WriteXml(strWrtr);

try
{

SqlCommand cmdInsert = new SqlCommand();
cmdInsert.Connection = sqlConn;
cmdInsert.Transaction = sqlTran;

cmdInsert.CommandText = "InsertProducts";

cmdInsert.Parameters.Add(new SqlParameter("@products", SqlDbType.Xml));
cmdInsert.Parameters.Add(new SqlParameter("@list", SqlDbType.NVarChar, 50));

cmdInsert.Parameters["@products"].Value = strWrtr.ToString();
cmdInsert.Parameters["@list"].Value = lista;

cmdInsert.CommandType = CommandType.StoredProcedure;

//THis is what takes a long time, the C# converting the datastable to XML is instantaneous, I know by debugging
cmdInsert.ExecuteNonQuery();

}
catch (Exception sqlEx)
{
log.Error(sqlEx.Message);
}

return errorNet;
}


THe SP:

ALTER PROCEDURE [dbo].[InsertProducts]
-- Add the parameters for the stored procedure here
@products xml,
@list nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @hDoc int;

EXEC sp_xml_preparedocument @hDoc OUTPUT, @products;


INSERT INTO dbo.Price
([version]
,[cod_alfa]
,[code]
,[rotation]
,[class]
,[subclass]
,[group]
,[enp]
,[calendar]
,[detail]
,[deta_list]
,[a_combust]
,[typer]
,[gas]
,[type_recur]
,[a_medid]
,[width]
,[height]
,[a_filler]
,[a_conde]
,[a_codorig]
,[ubi]
,[price]
,[a_nphoto]
,[descr]
,[typelist]
,[active]
,[ordering])
SELECT isnull([version],''), isnull([cod_alfa],'') ,isnull([code],'') ,isnull([rotation],'') ,isnull([class],'')
,isnull([subclass],'') ,isnull([group],'') ,isnull([enp],'') ,isnull([calendar],'') ,isnull([detail],'')
,isnull([deta_list],'') ,isnull([a_combust],'') ,isnull([typer],'') ,isnull([gas],'') ,isnull([type_recur],'')
,isnull([a_medid],'') ,isnull([width],'') ,isnull([height],''),isnull([a_filler],''),isnull([a_conde],''),isnull([a_codorig],'')
,isnull([ubi],''),isnull([price],''),isnull([a_nphoto],''),isnull([descr],''),isnull([typelist],'')
,isnull([active],''),isnull([ordering],'')
FROM OPENXML
(@hdoc,'//Price',2)
WITH
(version int
,cod_alfa nvarchar(50)
,code int
,rotation nvarchar(50)
,class nvarchar(50)
,subclass nvarchar(50)
,group nvarchar(50)
,emp nvarchar(50)
,calendar nvarchar(50)
,detail nvarchar(150)
,deta_lista nvarchar(50)
,a_combust nvarchar(50)
,typer nvarchar(50)
,gas nvarchar(50)
,type_recur nvarchar(50)
,a_medid int
,width int
,height int
,a_filler int
,a_conde nvarchar(50)
,a_codorig nvarchar(50)
,ubi nvarchar(50)
,price float
,a_nphoto nvarchar(20)
,descr nvarchar(50)
,typelist nvarchar(50)
,active int
,ordering int)

END



-------



I think that is it!, I'm still researching! If I come up with something I'll keep you posted :P
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2009-02-27 : 09:02:15
Change your method to use the new XML features in SQL Server 2005.

Remove sp_xml_preparedocument, OPENXML and your missing sp_xml_removedocument with the new ones.
INSERT	dbo.Price
	(
		[version],
		cod_alfa,
		code,
		rotation,
		class,
		subclass,
		[group],
		enp,
		calendar,
		detail,
		deta_list,
		a_combust,
		typer,
		gas,
		type_recur,
		a_medid,
		width,
		height,
		a_filler,
		a_conde,
		a_codorig,
		ubi,
		price,
		a_nphoto,
		descr,
		typelist,
		active,
		ordering
	)
SELECT	COALESCE(i.value('version[1]', 'INT'), ''),
	COALESCE(i.value('cod_alfa[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('code[1]', 'INT'), ''),
	COALESCE(i.value('rotation[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('class[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('subclass[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('group[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('emp[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('calendar[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('detail[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('deta_lista[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('a_combust[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('typer[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('gas[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('type_recur[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('a_medid[1]', 'INT'), ''),
	COALESCE(i.value('width[1]', 'INT'), ''),
	COALESCE(i.value('height[1]', 'INT'), ''),
	COALESCE(i.value('a_filler[1]', 'INT'), ''),
	COALESCE(i.value('a_conde[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('a_codorig[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('ubi[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('price[1]', 'FLOAT'), ''),
	COALESCE(i.value('a_nphoto[1]', 'NVARCHAR(20)'), ''),
	COALESCE(i.value('descr[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('typelist[1]', 'NVARCHAR(50)'), ''),
	COALESCE(i.value('active[1]', 'INT'), ''),
	COALESCE(i.value('ordering[1]', 'INT'), '')
FROM	@Products.nodes('/Price') AS p(i)

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site