Optimizar Stored Procedure

[miguelangel23]

Hola a todos, espero que se encuentren muy bien, mi consulta es la siguiente: Tengo un Stored Procedure que me han pedido optimizar. Este es:

Código SQL:

USE [TeamTemplate]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[Entity_SelectContact_pa]
  @WorkorderID VARCHAR(10)
, @WorkorderMod VARCHAR(5) = '00'
, @FilterBy VARCHAR(15) = 'Team'
, @SearchBy VARCHAR(50) = NULL
, @band VARCHAR(3) = NULL
 
AS
 
SET NOCOUNT ON
 
IF @FilterBy = 'Team'
SELECT @FilterBy = NULL
 
IF @SearchBy = '' OR @SearchBy = 'undefined'
SELECT @SearchBy = NULL
 
IF @band = ''
SELECT @band = NULL
 
IF @band IS NULL OR @band <> 'All'  
BEGIN
    IF ISNUMERIC(@FilterBy) = 1
    BEGIN
        
        CREATE TABLE #VendorId
        (
            VendorId        INT
        )   
 
        INSERT  INTO #VendorId
        SELECT  q.VendorID 
        FROM    Quotes q
                INNER JOIN CostCode cc ON q.DefaultCC = cc.CostCodeID
        WHERE   q.WorkorderID = @WorkorderID    AND q.QuoteStatus IN ('Draft','Committed','Closed') AND q.DefaultCC = @FilterBy
 
        INSERT  INTO #VendorId
        SELECT  b.VendorID 
        FROM    buyout b 
                INNER JOIN CostCode cc ON b.OrderCostCode = cc.CostCodeID
        WHERE   WorkorderID = @WorkorderID  AND b.STATUS IN ('Draft','Committed','Executed','Closed')AND b.OrderCostCode    = @FilterBy 
 
        SELECT  DISTINCT u.UserId,(e.ShortName +' - '+ u.FirstName +' '+ u.LastName) AS UserName,
                '('+ isnull(u.EmailAddress,'') + ')' AS EmailAddress, u.STATUS
        FROM    Users u
                INNER JOIN Entity e         ON  u.EntityID  =   e.EntityID
                INNER JOIN Vendor v         ON  e.EntityID  =   v.EntityID
        WHERE   u.EntityID IN   (   
                                    SELECT  et.EntityID 
                                    FROM    EntityType et 
                                    WHERE   et.EntityID = e.EntityID AND et.EntityTypeName = 'Vendor' AND et.STATUS = 'on' 
                                )   AND 
                v.Vendor# IN ( SELECT DISTINCT VendorID FROM #VendorId )
        ORDER   BY e.ShortName +' - '+ u.FirstName +' '+ u.LastName
 
        DROP TABLE #VendorId
    END 
    ELSE
    BEGIN
        IF @FilterBy IS NULL OR @FilterBy <> 'All'
        BEGIN
            SELECT  DISTINCT u.UserID,(e.ShortName +' - '+ u.FirstName +' '+ u.LastName) AS UserName ,
                    '('+ isnull(u.EmailAddress,'') + ')' AS EmailAddress, u.STATUS
            FROM    Users u  
                    INNER JOIN WorkorderTeam wt ON wt.UserID = u.UserID  
                    INNER JOIN Entity e ON u.EntityID = e.EntityID  
            WHERE   wt.WorkorderID = @WorkorderID  AND
                    wt.WorkorderMod = @WorkorderMod AND
                    wt.STATUS = 'show' AND 
                    (@FilterBy IS NULL OR wt.TeamMemberType = @FilterBy)  
            ORDER   BY e.ShortName +' - '+ u.FirstName +' '+ u.LastName
        END
 
        IF @FilterBy = 'All'
        BEGIN
            SELECT  u.UserId, (e.ShortName +' - '+ u.FirstName +' '+ u.LastName) AS UserName,
                    '('+ isnull(u.EmailAddress,'') + ')' AS EmailAddress, u.STATUS
            FROM    Users u
                    INNER JOIN Entity e ON e.EntityID = u.EntityID
            WHERE   u.EntityID IN   (
                                        SELECT  et.EntityID 
                                        FROM    EntityType et 
                                        WHERE   et.EntityID = e.EntityID AND 
                                                et.EntityTypeName IN ('Customer','Occupant','ThirdParty','Vendor','SP') AND 
                                                et.STATUS = 'on'
                                    )   AND 
                    (@SearchBy IS NULL OR e.ShortName +' - '+ u.FirstName +' '+ u.LastName LIKE '%' + @SearchBy + '%')
            ORDER   BY u.FirstName, u.LastName
        END
    END 
END
 
IF @band = 'All'
BEGIN
    SELECT  u.UserId, (e.ShortName +' - '+ u.FirstName +' '+ u.LastName) AS UserName,'('+ isnull(u.EmailAddress,'') + ')' AS EmailAddress,u.STATUS
    FROM    Users u
            INNER JOIN Entity e ON e.EntityID = u.EntityID
    WHERE   u.STATUS = 'on' AND 
            u.EntityID IN (SELECT et.EntityID FROM EntityType et WHERE et.EntityID = e.EntityID AND et.STATUS = 'on') AND 
            (@SearchBy IS NULL OR e.ShortName +' - '+ u.FirstName +' '+ u.LastName LIKE '%' + @SearchBy + '%')
    ORDER   BY u.FirstName, u.LastName
END
 

[/CODE]

Podrían decirme como puedo hacerlo más óptimo. Gracias.

[Libras]

para optimizar un procedure, deberias de revisar los indices de tus tablas, verificar si tu plan de ejecucion asi como el tiempo de duracion del procedure, con solo ver el query no se te puede ayudar demasiado.

saludos!