Click here to Skip to main content
14,975,411 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Please can any one suggest how to make this code faster
1) I am using the following to retrieve an xml document content as xml datatype
SQL
set @mysql=N'select  @PersonXML= CONVERT(xml, BulkColumn, 2) FROM 
OPENROWSET(Bulk '''+ @XMLFileName+''', SINGLE_BLOB ) [rowsetresults]'

2) I am using the following code to retrieve the information from the xml variable
SQL
(SELECT TempXML.Node.value('(email)[1]','nvarchar(50)') as Email,
' ', TempXML.Node.value('(name/n/given)[1]', 'nVARCHAR(50)') as FirstName,TempXML.Node.value('(name/n/family)[1]', 'VARCHAR(50)') as LastName,TempXML.Node.value('(name/fn)[1]','nvarchar(50)') as DisplayName, ' ',TempXML.Node.value('(email)[1]','nvarchar(50)') as DisplayEmail,' ',1,GETDATE(),0 FROM @PersonXML.nodes('/enterprise/person') TempXML (Node))

3) I create a temporary table and inserted the outcome to the temporary table
4) used merge table to merge the temporary table with the target table because target table had a unique key on email and I wanted to not to insert duplicate records and log those duplicate records in a log table
here is my code:
SQL
USE [SP3]
GO
/****** Object:  StoredProcedure [dbo].[usp_user_longimportInfo_FileName_trial2]    Script Date: 05/11/2010 15:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

---- Good One ---- for content 
ALTER procedure [dbo].[usp_user_longimportInfo_FileName_trial2] 
(@XMLFileName as nvarchar(100))
as
begin
set @XMLFileName='c:\trial\shortperson.xml'
create table #tempPerson ([UserID] [int] IDENTITY(1,1) NOT NULL,
	[Email] [nvarchar](50)  NULL,
	[Pass] [nvarchar](20)  NULL,
	[FirstName] [nvarchar](20) NULL,
	[LastName] [nvarchar](40) NULL,
	[DisplayName] [nvarchar](50) NULL,
	[Profile] [nvarchar](max) NULL,
	[DisplayEmail] [nvarchar](50) NULL,
	[CellPhone] [nvarchar](20) NULL,
	[UpdatedBy] [int] NULL,
	[UpdateDate] [datetime]  NULL,
	[Deleted] [bit]  NULL)

DECLARE @TempCurrentTime datetime
DECLARE @USERID int
Declare @UpdateDate datetime
Declare @Email nvarchar(50)
DECLARE @Pass NVARCHAR(20)
DECLARE @FirstName NVARCHAR(20)
DECLARE @LastName nvarchar(40)
Declare @DisplayName nvarchar(50)
Declare @Organization int
Declare @UserAccessRole int
Declare @OrgUserID nchar(12)
Declare @OrgPassword nvarchar(50)
Declare @OrgUserName nvarchar(50)
declare @mySQL nvarchar(max)
declare @PersonXML xml
declare @ParamDefinition nvarchar(500)
 
SELECT @TempCurrentTime = GETDATE()
 
		select @UpdateDate=@TempCurrentTime
		-- retrieve the file content as xml
					set @mysql=N'select  @PersonXML=
					 CONVERT(xml, BulkColumn, 2) FROM 
						OPENROWSET(Bulk '''+ @XMLFileName+''', SINGLE_BLOB ) [rowsetresults]'
					    
				  
					 Set @ParamDefinition = '@XMLFileName nvarchar(max),@PersonXML XML out'
					  Execute sp_Executesql	@mySql,
					   @ParamDefinition,
					   @XMLFileName,
					   @PersonXML out
 
insert into #tempPerson (Email,Pass,FirstName,LastName,DisplayName,[Profile],DisplayEmail,CellPhone,UpdatedBy,UpdateDate,deleted)

			(SELECT TempXML.Node.value('(email)[1]','nvarchar(50)') as Email,
			' ',
			  TempXML.Node.value('(name/n/given)[1]', 'nVARCHAR(50)') as FirstName,
			 TempXML.Node.value('(name/n/family)[1]', 'VARCHAR(50)') as LastName,
			  TempXML.Node.value('(name/fn)[1]','nvarchar(50)') as DisplayName,
			  ' ',
			  TempXML.Node.value('(email)[1]','nvarchar(50)') as DisplayEmail,
			  ' ',1,GETDATE(),0
			  FROM @PersonXML.nodes('/enterprise/person') TempXML (Node)) 
select * from #tempPerson
insert into dbo.longPersonError
select  USERID, Email 
from(
MERGE SP.UserTrial 
    USING (SELECT Email,Pass,FirstName,LastName,DisplayName,[Profile],DisplayEmail,CellPhone,UpdatedBy,UpdateDate,deleted from #tempPerson) AS source 
    ON (SP.UserTrial.Email = source.Email)
    WHEN Not MATCHED  THEN
    
    		INSERT (Email,Pass,FirstName,LastName,DisplayName,[Profile],DisplayEmail,CellPhone,UpdatedBy,UpdateDate,deleted)
			VALUES (source.Email,source.Pass,source.FirstName,source.LastName,source.DisplayName,source.[Profile],source.DisplayEmail,source.CellPhone,source.UpdatedBy,source.UpdateDate,source.deleted) 
	when matched then
	       UPDATE SET SP.UserTrial.Email = SP.UserTrial.Email
	       OUTPUT $ACTION, deleted.userid,deleted.Email ) AS CHANGES (Action,userid,email)
WHERE Action = 'UPDATE';
  
end


end
Posted
Updated 11-May-10 19:40pm
v2

1 solution

Temporary tables does hit performance big time!
Read about Table Valued Functions[^] and try to convert your temp tables part into that... It will have a good effect on performance.

There are lot's of example of TVF on web, have a look at them.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900