SQL Server 2012: In need of query optimization!

Experts,
This sql statement takes over 2 hours to complete. The SQL plan indicates a LOT of sorting--which I recognize is in itself a performance hitter.  I am contemplating switching over to a combination of update/insert statements, however, this MERGE statement does all that I want it to do in a single pass. I'd rather find a way to optimize it than iterate over the tables multiple times. Your advice would be helpful.


INSERT INTO integration.BuyingUnit_pld
(   [BuyingUnitDK]			
	,[BuyingUnitInternalKey]
	,[RecordVersion]				
	,[ExternalBuyingUnit]
	,[CountryID]
	,[Country]
	,[StateID]
	,[State]
	,[City]
	,[Street1]
	,[Street2]
	,[AddressKana]
	,[StreetNum]
	,[PostalCode]
	,[POBox]
	,[PhonePrefix]
	,[HomePhone]
	,[EMailAddress]
	,[SendEmail]
	,[BuyingUnitRemarks]
	,[VirtualBuyingUnitInternalKey]
	,[Extension]
	,[LastName]
	,[IsEmployee]
	,[DisableReward]
	,[ExportedRowVersion]
	,[LoyaltyRecordVersion]
	,[MatrixMemberId]
	,[ClubInternalKey]
	--,[RecordFromDate]
	--,[RecordToDate]
	,[RecordIsCurrent]
	,[RecordisDeleted]
	--,[InsertedBy]
	,[InsertedDate]
	--,[ModifiedBy]
	,[ModifiedDate]
		      )
SELECT
		 MERGE_OUT.BuyingUnitDK	
		,MERGE_OUT.BuyingUnitInternalKey
		,MERGE_OUT.RecordVersion	+ 1				
		,MERGE_OUT.[ExternalBuyingUnit]
		,MERGE_OUT.[CountryID]
		,MERGE_OUT.[Country]
		,MERGE_OUT.[StateID]
		,MERGE_OUT.[State]
		,MERGE_OUT.[City]
		,MERGE_OUT.[Street1]
		,MERGE_OUT.[Street2]
		,MERGE_OUT.[AddressKana]
		,MERGE_OUT.[StreetNum]
		,MERGE_OUT.[PostalCode]
		,MERGE_OUT.[POBox]
		,MERGE_OUT.[PhonePrefix]
		,MERGE_OUT.[HomePhone]
		,MERGE_OUT.[EMailAddress]
		,MERGE_OUT.[SendEmail]
		,MERGE_OUT.[BuyingUnitRemarks]
		,MERGE_OUT.[VirtualBuyingUnitInternalKey]
		,MERGE_OUT.[Extension]
		,MERGE_OUT.[LastName]
		,MERGE_OUT.[IsEmployee]
		,MERGE_OUT.[DisableReward]
		,MERGE_OUT.[ExportedRowVersion]
		,MERGE_OUT.[LoyaltyRecordVersion]
		,MERGE_OUT.[MatrixMemberId]
		,MERGE_OUT.[ClubInternalKey]			   
	--	,@CurrentDate					 -- RecordFromDate - this is a continuation record due to a type 2 change, so the start date = the old record end date (curent date)
	--	,@RecordMaxDate					 -- RecordToDate
		,1								 -- RecordIsCurrent
		,0								 -- RecordisDeleted
	--	,@ExecutedBy					 -- InsertedBy
		,GetDate()						 -- InsertedDate
	--	,@ExecutedBy					 -- ModifiedBy
		,GetDate()						 -- ModifiedDate

FROM 

(
    MERGE integration.BuyingUnit_pld AS TARGET

	USING (SELECT  [BuyingUnitInternalKey]
				  ,[ExternalBuyingUnit]
				  ,[CountryID]
				  ,[Country]
				  ,[StateID]
				  ,[State]
				  ,[City]
				  ,[Street1]
				  ,[Street2]
				  ,[AddressKana]
				  ,[StreetNum]
				  ,[PostalCode]
				  ,[POBox]
				  ,[PhonePrefix]
				  ,[HomePhone]
				  ,[EMailAddress]
				  ,[SendEmail]
				  ,[BuyingUnitRemarks]
				  ,[VirtualBuyingUnitInternalKey]
				  ,[Extension]
				  ,[LastName]
				  ,[IsEmployee]
				  ,[DisableReward]
				  ,[ExportedRowVersion]
				  ,[LoyaltyRecordVersion]
				  ,[MatrixMemberId]
				  ,[ClubInternalKey]
			  FROM [etl].[BuyingUnit]
          ) AS Source
                  ([BuyingUnitInternalKey]
				  ,[ExternalBuyingUnit]
				  ,[CountryID]
				  ,[Country]
				  ,[StateID]
				  ,[State]
				  ,[City]
				  ,[Street1]
				  ,[Street2]
				  ,[AddressKana]
				  ,[StreetNum]
				  ,[PostalCode]
				  ,[POBox]
				  ,[PhonePrefix]
				  ,[HomePhone]
				  ,[EMailAddress]
				  ,[SendEmail]
				  ,[BuyingUnitRemarks]
				  ,[VirtualBuyingUnitInternalKey]
				  ,[Extension]
				  ,[LastName]
				  ,[IsEmployee]
				  ,[DisableReward]
				  ,[ExportedRowVersion]
				  ,[LoyaltyRecordVersion]
				  ,[MatrixMemberId]
				  ,[ClubInternalKey])
		ON (    Source.BuyingUnitInternalKey		= Target.BuyingUnitInternalKey			-- THIS IS THE RETALIX BUSINESS KEY FOR THIS DATA SET
		    AND Source.MatrixMemberId				= Target.MatrixMemberId
		   )

		-- NO MATCH MEANS NEW DATA - PERFORM AN INSERT
		WHEN NOT MATCHED THEN
	    INSERT (		
			    [BuyingUnitInternalKey]
			   ,[RecordVersion]				
			   ,[ExternalBuyingUnit]
			   ,[CountryID]
			   ,[Country]
			   ,[StateID]
			   ,[State]
			   ,[City]
			   ,[Street1]
			   ,[Street2]
			   ,[AddressKana]
			   ,[StreetNum]
			   ,[PostalCode]
			   ,[POBox]
			   ,[PhonePrefix]
			   ,[HomePhone]
			   ,[EMailAddress]
			   ,[SendEmail]
			   ,[BuyingUnitRemarks]
			   ,[VirtualBuyingUnitInternalKey]
			   ,[Extension]
			   ,[LastName]
			   ,[IsEmployee]
			   ,[DisableReward]
			   ,[ExportedRowVersion]
			   ,[LoyaltyRecordVersion]
			   ,[MatrixMemberId]
			   ,[ClubInternalKey]
			 --  ,[RecordFromDate]
			 -- ,[RecordToDate]
			   ,[RecordIsCurrent]
			   ,[RecordisDeleted]
			 -- ,[InsertedBy]
			   ,[InsertedDate]
			  -- ,[ModifiedBy]
			   ,[ModifiedDate]
		      )
        VALUES
             (
			    Source.BuyingUnitInternalKey
			   ,1											
			   ,Source.[ExternalBuyingUnit]
			   ,Source.[CountryID]
			   ,Source.[Country]
			   ,Source.[StateID]
			   ,Source.[State]
			   ,Source.[City]
			   ,Source.[Street1]
			   ,Source.[Street2]
			   ,Source.[AddressKana]
			   ,Source.[StreetNum]
			   ,Source.[PostalCode]
			   ,Source.[POBox]
			   ,Source.[PhonePrefix]
			   ,Source.[HomePhone]
			   ,Source.[EMailAddress]
			   ,Source.[SendEmail]
			   ,Source.[BuyingUnitRemarks]
			   ,Source.[VirtualBuyingUnitInternalKey]
			   ,Source.[Extension]
			   ,Source.[LastName]
			   ,Source.[IsEmployee]
			   ,Source.[DisableReward]
			   ,Source.[ExportedRowVersion]
			   ,Source.[LoyaltyRecordVersion]
			   ,Source.[MatrixMemberId]
			   ,Source.[ClubInternalKey]
			--   ,@RecordMinDate					-- RecordFromDate
			--   ,@RecordMaxDate					-- RecordToDate
			   ,1								-- RecordIsCurrent
			   ,0								-- RecordisDeleted
			--   ,@ExecutedBy						-- InsertedBy
			   ,GetDate()						-- InsertedDate
			--   ,@ExecutedBy						-- ModifiedBy
			   ,GetDate()						-- ModifiedDate
		     )

			 WHEN MATCHED																		-- The WHEN MATCHED can only do updates and deletes, so we need take care of type 2 columns here  
                  AND TARGET.RecordIsCurrent = 1											-- Only compare to the CURRENT record
				  AND ( ISNULL(Target.CountryID,'XXX')		<> ISNULL(Source.CountryID,'XXX')	-- Initailly, most of the Retalix columns are going to be considered to be type II
					  OR ISNULL(Target.Country,'XXX')		<> ISNULL(Source.Country,'XXX')
					  OR ISNULL(Target.City,'XXX')			<> ISNULL(Source.City,'XXX')
					  OR ISNULL(Target.StateID,'XXX')		<> ISNULL(Source.StateID,'XXX')
					  OR ISNULL(Target.[State],'XXX')		<> ISNULL(Source.[State],'XXX')
					  OR ISNULL(Target.Street1,'XXX')		<> ISNULL(Source.Street1,'XXX')
					 -- OR ISNULL(Target.Street2,'XXX')		<> ISNULL(Source.Street2,'XXX')			-- Not populated initially
					  OR ISNULL(Target.StreetNum,'XXX')		<> ISNULL(Source.StreetNum,'XXX')
					  OR ISNULL(Target.PostalCode,'XXX')	<> ISNULL(Source.PostalCode,'XXX')
					 -- OR ISNULL(Target.POBox,'XXX')			<> ISNULL(Source.POBox,'XXX')		-- Not populated initially
					 -- OR ISNULL(Target.PhonePrefix,'XXX')	<> ISNULL(Source.PhonePrefix,'XXX')		-- Not populated initially
					 -- OR ISNULL(Target.HomePhone,'XXX')		<> ISNULL(Source.HomePhone,'XXX')	-- Not populated initially
					  OR ISNULL(Target.EmailAddress,'XXX')	<> ISNULL(Source.EmailAddress,'XXX')
					  OR ISNULL(Target.LastName,'XXX')		<> ISNULL(Source.LastName,'XXX')
				      )

             THEN
			 UPDATE		-- This update just "closes out" the current record that's about to be replaced
                 --   SET TARGET.ModifiedBy			= @ExecutedBy
				SET
					   TARGET.ModifiedDate			= GetDate()
					 -- , TARGET.RecordToDate			= @CurrentDate  
					  , TARGET.RecordIsCurrent   	= 0

             OUTPUT $Action -- Output used to insert new record for type 2 changes (spit all the columns out)
			        Action_Out 
					   ,DELETED.BuyingUnitDK					--TARGET.BuyingUnitDK
					   ,Source.BuyingUnitInternalKey
					   ,DELETED.Recordversion 					
					   ,Source.[ExternalBuyingUnit]
					   ,Source.[CountryID]
					   ,Source.[Country]
					   ,Source.[StateID]
					   ,Source.[State]
					   ,Source.[City]
					   ,Source.[Street1]
					   ,Source.[Street2]
					   ,Source.[AddressKana]
					   ,Source.[StreetNum]
					   ,Source.[PostalCode]
					   ,Source.[POBox]
					   ,Source.[PhonePrefix]
					   ,Source.[HomePhone]
					   ,Source.[EMailAddress]
					   ,Source.[SendEmail]
					   ,Source.[BuyingUnitRemarks]
					   ,Source.[VirtualBuyingUnitInternalKey]
					   ,Source.[Extension]
					   ,Source.[LastName]
					   ,Source.[IsEmployee]
					   ,Source.[DisableReward]
					   ,Source.[ExportedRowVersion]
					   ,Source.[LoyaltyRecordVersion]
					   ,Source.[MatrixMemberId]
					   ,Source.[ClubInternalKey]

			 ) AS MERGE_OUT
    WHERE MERGE_OUT.Action_Out = 'UPDATE';

Open in new window

Paula DiTalloIntegration developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paula DiTalloIntegration developerAuthor Commented:
I thought I posted the query plan. Looks like I didn't. Here it is.
Dimension-TypeII.sqlplan
0
Scott PletcherSenior DBACommented:
Yeah, MERGE has been shown to perform very poorly at times.  

It would likely be much faster to pull the source data into a temp table, clustered appropriately on ( BuyingUnitInternalKey, MatrixMemberId ), then do the matching UPDATEs and finally the INSERTs using the temp table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.