What is the optimal way to compare old XML values with New XML values in a SQL Query?

Im trying to optimize SQL and XQuery  stored procedure to be as quick and efficient as possible in comparing old and new values.

The biggest slow down comes down to the FULL OUTER JOIN when comparing old folder counts to new folder counts.

Does anyone know a better way to write that? Or is that the best way to go?

	DECLARE 	@paramVcAccountID INT = 2,
	@paramVcUserID	INT = 2,
	@paramFolderUnreadCountDtoXml XML  =N'<?xml version="1.0"?>
<root xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <FolderUnreadCountDto>
    <FolderID>30</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>31</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>32</FolderID>
    <UnreadCount>361</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>33</FolderID>
    <UnreadCount>9</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12109</FolderID>
    <UnreadCount>6</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12125</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12143</FolderID>
    <UnreadCount>4</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12145</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12144</FolderID>
    <UnreadCount>191</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12192</FolderID>
    <UnreadCount>4</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12235</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12233</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12213</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12223</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12234</FolderID>
    <UnreadCount>72</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12264</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12297</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12282</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12335</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12329</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12333</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12310</FolderID>
    <UnreadCount>1</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12334</FolderID>
    <UnreadCount>1</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12362</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12360</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12395</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
  <FolderUnreadCountDto>
    <FolderID>12406</FolderID>
    <UnreadCount>0</UnreadCount>
  </FolderUnreadCountDto>
</root>',

@paramOutputXml XML 
	
	DECLARE @userIndex INT = (SELECT a.UserIndex FROM [dbo].[tblUser] a WHERE a.VcUserId = @paramVcUserID),
			@folderTrash INT = (SELECT [dbo].[udsfGetFolderByName]('Trash', @paramVcAccountID)),
			@folderUnfiled INT = (SELECT [dbo].[udsfGetFolderByName]('Unfiled', @paramVcAccountID)),
			@FolderChangeCount INT = 0
	
	-- ****************
	-- *** Get Data ***
	-- ****************
	SET @paramOutputXml = (
			SELECT 
				x.FolderID,
				x.UnreadCount
			FROM
				(
					SELECT 	a.FolderID,
							a.UnreadCount
					FROM 	[dbo].[tblFolderUserUnreadCount] a WITH (NOLOCK)
					WHERE	a.VcUserID = @paramVcUserID
				) x
			FOR XML PATH ('FolderUnreadCountDto')
		)
	
		--Check if the FolderUnreadCountDto being passed in is the same as the one in the database
		--NOTE: XML folder Structure ordering must match
		;WITH XML1 AS  --Folder XML structure passed in via parameter 
		(
		  SELECT T.N.value('local-name(.)', 'nvarchar(100)') AS NodeName,
				 T.N.value('.', 'nvarchar(100)') AS OldValue
		  FROM @paramFolderUnreadCountDtoXml.nodes('/root/FolderUnreadCountDto/*') AS T(N)
		),
		XML2 AS  --Folder XML structure from the database 
		(
		  SELECT T.N.value('local-name(.)', 'nvarchar(100)') AS NodeName,
				 T.N.value('.', 'nvarchar(100)') AS NewValue
		  FROM @paramOutputXml.nodes('/FolderUnreadCountDto/*') AS T(N)
		)
	 
[b]	 	SELECT @FolderChangeCount=COUNT(*)
		FROM XML1
		  FULL OUTER JOIN XML2
			ON XML1.NodeName = XML2.NodeName AND XML1.OldValue = XML2.NewValue
	    WHERE COALESCE(XML1.OldValue, '') <> COALESCE(XML2.NewValue, '')  [/b]
	    
	    --if there has been no change return what was passed in 
	    IF @FolderChangeCount = 0 AND @paramFolderUnreadCountDtoXml IS NOT NULL
	    BEGIN
			DECLARE @paramFolderUnreadCountDtoOut NVARCHAR(MAX)
			DECLARE @paramFolderUnreadCountDtoXmlOut XML
			SET @paramFolderUnreadCountDtoOut = 
				REPLACE(
					REPLACE(
						REPLACE(
							CONVERT(NVARCHAR(MAX),@paramFolderUnreadCountDtoXml), 
						'<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">', '')
					, '<root xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">', '')
				, '</root>','')
			SELECT @paramFolderUnreadCountDtoXmlOut = CONVERT(XML,@paramFolderUnreadCountDtoOut)
			SET @paramOutputXml = @paramFolderUnreadCountDtoXmlOut
		END	

Select allOpen in new window