SQL Server - Procedimiento almacenado lento

USE [SIE_DESA]
GO
/****** Object: StoredProcedure [dbo].[P_Migracion_TraspasoPedido] Script Date: 22/04/2016 10:23:47 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[P_Migracion_TraspasoPedido]
AS
BEGIN
IF OBJECT_ID ('tempdb..[#Tmp_TRASPASO]') IS NOT NULL DROP TABLE #Tmp_TRASPASO

DECLARE @TipoOrigenTraspaso_Id INT
SET @TipoOrigenTraspaso_Id = ( SELECT TOP 1 TD.[id]
FROM [Tipo] T INNER JOIN [TipoDetalle] TD on T.Id = TD.Tipo_id
WHERE UPPER(T.[Descripcion]) = UPPER('Tipo Origen Traspaso')
AND UPPER(TD.[Descripcion]) LIKE UPPER('%PEDIDO%'))

SELECT DISTINCT T.[Codigo]
, PD.[Codigo] AS [DocumentoOrigen]
INTO #Tmp_TRASPASO
FROM [ESTLMODB02].[RP3].[dbo].[Traspaso] T WITH(NOLOCK)
INNER JOIN [Traspaso] TR WITH(NOLOCK) ON T.[Codigo] = TR.[Codigo]
INNER JOIN [ESTLMODB02].[RP3].[dbo].[TraspasoDetalle] TD WITH(NOLOCK) ON TD.[Codigo] = T.[Codigo]
INNER JOIN [ESTLMODB02].[RP3].[dbo].[PedidoDetalle] PD WITH(NOLOCK) ON PD.[Codigo] = T.[DocumentoOrigen] AND PD.[Sku] = TD.[Sku]

UPDATE TR SET TR.[Pedido_Id] = P.[Id],
TR.[TipoOrigen_Id] = @TipoOrigenTraspaso_Id
FROM #Tmp_TRASPASO T
INNER JOIN [Traspaso] TR ON T.[Codigo] = TR.[Codigo]
INNER JOIN [Pedido] P ON T.[DocumentoOrigen] = P.[Codigo]

IF OBJECT_ID ('tempdb..[#Tmp_TRASPASO]') IS NOT NULL DROP TABLE #Tmp_TRASPASO

END