April 22, 2016 at 11:07 am
hello, I have a stored procedure that it's slow, somebody can help me to optimize this stored procedure. I'm using Sql server 2014. Thanks
USE [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
April 22, 2016 at 12:01 pm
The top 1 in your select statement may not always bring back the same record each time, since there is no order by clause on the query. The WITH(NOLOCK) hints are likely to cause similar un-reproduceable problems, as well.
I doubt it will help much, but unless you have a non-default collation, you can get rid of the UPPER function calls. These will invalidate any underlying indexes.
Have you narrowed down how much time each statement takes, or checked on the execution plan generated for the procedure to determine which of the three statements is taking the most time?
April 22, 2016 at 12:28 pm
I agree on the part that UPPER should be removed if the collation is case insensitive.
The other thing that would improve the query is if you avoid joining tables from a linked server with tables from the local database. Unless you can avoid that, the performance won't be optimal.
Can you confirm that the SELECT...INTO statement is the one with the problem?
If it's not, then post DDL for tables involved including indexes and actual execution plan. To know how to do it, check this article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply