none
Stored Procedure is very slow

    Question

  • I have a stored procedure that is very slow and would like more efficient SQL.  I want to get a selection of data put into a @SearchTbl that I send elsewhere for further manipulation.  I do these in two parts.  First, if the sending program sends a specific @UserName, @EntryTime, and/or @Type, I get all entries that meet these conditions.  If the user does not send an entrytime (If NULL), I get the latest entries from the @SearchTbl for each unique combination of certain variables.  Is there a way to do both in one step?

    Any suggestions on how to make these steps below faster?

    CREATE PROCEDURE MyProcedure             
     @UserName nvarchar(255) = NULL, @EntryTime datetime = NULL, @Type nvarchar(255) = NULL
    AS
    DECLARE @SQL nvarchar(4000)
    DECLARE @whereAdd nvarchar(4000)
    DECLARE @SearchTbl tempSearchTbl_tbltype
    DECLARE @TvuTbl TVU_tbltype
    DECLARE @t int
    DECLARE @tempCt int
    DECLARE @narCt int
    DECLARE @n int
    
    IF @Type IS NOT NULL AND @UserName IS NOT NULL
    BEGIN
       SET @whereAdd = ' WHERE Type = @Type AND UserName = @UserName'
    END
              
    IF @Type IS NOT NULL AND @UserName IS NULL
    BEGIN
      SET @whereAdd = ' WHERE Type = @Type'
    END
    
    IF @Type IS NULL AND @UserName IS NOT NULL
        BEGIN
          	SET @whereAdd = ' WHERE UserName = @UserName'
         END
    SET @SQL = 'SELECT CName, UserName, Type, Address, CardNum, CText, 
    EntryTime, Var1, Var2, Var3 FROM PTable' + @whereAdd
    
    IF @Type IS NULL AND @UserName IS NULL
    BEGIN
       SET @SQL = 'SELECT CName, UserName, Type, Address, CardNum, CText, 
     EntryTime, Var1, Var2, Var3 FROM PTable'
    END
    INSERT INTO @SearchTbl (CName, UserName, Type, Address, CardNum, CText, 
    EntryTime, Var1, Var2, Var3)
    EXEC sp_executesql @SQL,
    N'@UserName nvarchar(255), @Type nvarchar(255)'
    @UserName, @Type
    
    GetLatestLoop:
    IF @EntryTime IS NULL
     BEGIN
    With CTE As (SELECT CName, Type, CText, CardNum,
        Row_Number() OVER(Partition By CName, Type, CardNum, Type ORDER BY EntryTime Desc) AS rn
    FROM @SearchTbl)
    DELETE FROM CTE WHERE rn > 1
    END


    Gina





    Friday, July 04, 2014 6:56 PM

Answers

  • Looks like this would suffice

    CREATE PROCEDURE MyProcedure             
     @UserName nvarchar(255) = NULL, @EntryTime datetime = NULL, @Type nvarchar(255) = NULL
    AS
    DECLARE @SQL nvarchar(4000)
    DECLARE @whereAdd nvarchar(4000)
    DECLARE @SearchTbl tempSearchTbl_tbltype
    DECLARE @TvuTbl TVU_tbltype
    DECLARE @t int
    DECLARE @tempCt int
    DECLARE @narCt int
    DECLARE @n int
    SET @whereAdd = ''
    
    IF @Type IS NOT NULL 
    BEGIN
       SET @whereAdd =  @whereAdd + ' AND Type = @Type'
    END
              
    IF @UserName IS NOT NULL
    BEGIN
      SET @whereAdd = @whereAdd + ' AND UserName = @UserName'
    END
    
    IF @EntryTime IS NOT NULL
    BEGIN
      SET @whereAdd = @whereAdd + ' AND EntryTime = @EntryTime'
    END
    ELSE
    BEGIN
    SET @whereAdd = @whereAdd + 'AND NOT EXISTS ( SELECT 1
    FROM PTable
    WHERE CName = p.CName
    AND Type = p.Type
    AND CardNum = p.CardNum
    AND EntryTime > t.EntryTime
    )'
    END
    
    SET @SQL = 'SELECT CName, UserName, Type, Address, CardNum, CText, 
    EntryTime, Var1, Var2, Var3 FROM PTable p
    WHERE 1 = 1' + @whereAdd
    
    
    INSERT INTO @SearchTbl (CName, UserName, Type, Address, CardNum, CText, 
    EntryTime, Var1, Var2, Var3)
    EXEC sp_executesql @SQL,
    N'@UserName nvarchar(255), @Type nvarchar(255),@EntryTime datetime'
    @UserName, @Type,@EntryTime
    
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Gina Becker Friday, July 04, 2014 8:17 PM
    Friday, July 04, 2014 7:31 PM

All replies

  • Looks like this would suffice

    CREATE PROCEDURE MyProcedure             
     @UserName nvarchar(255) = NULL, @EntryTime datetime = NULL, @Type nvarchar(255) = NULL
    AS
    DECLARE @SQL nvarchar(4000)
    DECLARE @whereAdd nvarchar(4000)
    DECLARE @SearchTbl tempSearchTbl_tbltype
    DECLARE @TvuTbl TVU_tbltype
    DECLARE @t int
    DECLARE @tempCt int
    DECLARE @narCt int
    DECLARE @n int
    SET @whereAdd = ''
    
    IF @Type IS NOT NULL 
    BEGIN
       SET @whereAdd =  @whereAdd + ' AND Type = @Type'
    END
              
    IF @UserName IS NOT NULL
    BEGIN
      SET @whereAdd = @whereAdd + ' AND UserName = @UserName'
    END
    
    IF @EntryTime IS NOT NULL
    BEGIN
      SET @whereAdd = @whereAdd + ' AND EntryTime = @EntryTime'
    END
    ELSE
    BEGIN
    SET @whereAdd = @whereAdd + 'AND NOT EXISTS ( SELECT 1
    FROM PTable
    WHERE CName = p.CName
    AND Type = p.Type
    AND CardNum = p.CardNum
    AND EntryTime > t.EntryTime
    )'
    END
    
    SET @SQL = 'SELECT CName, UserName, Type, Address, CardNum, CText, 
    EntryTime, Var1, Var2, Var3 FROM PTable p
    WHERE 1 = 1' + @whereAdd
    
    
    INSERT INTO @SearchTbl (CName, UserName, Type, Address, CardNum, CText, 
    EntryTime, Var1, Var2, Var3)
    EXEC sp_executesql @SQL,
    N'@UserName nvarchar(255), @Type nvarchar(255),@EntryTime datetime'
    @UserName, @Type,@EntryTime
    
    END


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Gina Becker Friday, July 04, 2014 8:17 PM
    Friday, July 04, 2014 7:31 PM
  • Can you post the DDL, including indexes and constraints, for PTable?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, July 04, 2014 7:46 PM
  • Thank you!

    Gina

    Friday, July 04, 2014 8:18 PM