none
performance is low for the sql user define function RRS feed

  • Question

  • i have one function that is called around 300 times and i used in the select statement.

    it tooks 4 minutes to get the result.

    what is the solution for this?

    here is the function

    ALTER FUNCTION [dbo].[FN_GetPointStatusStringForUser_temp]     
    (    
     @targetID INT,    
     @userID INT,    
     @year INT    
    )    
    RETURNS NVARCHAR(MAX)    
    AS    
    BEGIN    
         
     DECLARE @pointStatusStr NVARCHAR(MAX);  
     DECLARE @commentStr NVARCHAR(MAX);  
      
     DECLARE @MaxWeek INT, @TargetType INT, @PointValue NVARCHAR(16), @CommentValue NVARCHAR(1024)  
      
      SELECT @MaxWeek=[dbo].[FN_ISO_WEEK_OF_YEAR](GETDATE());  
       
     if(Year(getdate()) = @year)  
     begin  
      --SELECT @MaxWeek=[dbo].fn_GetWeekNumber(getdate());  
      
      if(Month(getdate()) = 12 and @MaxWeek = 1)  
       SET @MaxWeek = 52;  
     end  
     else  
     begin  
      --SELECT @MaxWeek=[dbo].fn_GetWeekNumber(STR(@year) + '-12-31');  
      SELECT @MaxWeek=[dbo].[FN_ISO_WEEK_OF_YEAR](STR(@year) + '-12-31');
      if(@MaxWeek=1)  
      SET @MaxWeek = 52;  
     end  
       
     SELECT @TargetType = TargetType FROM [Target]
     -- WITH (NOLOCK) 
     WHERE TargetID = @targetID    
      
     SET @PointValue = ''  
     SET @CommentValue = ''  
     WHILE @MaxWeek > 0  
     BEGIN  
      IF(@TargetType=2 OR @TargetType=3 OR @TargetType=4)    
      BEGIN       
       SELECT   
        @PointValue = UserPoints.CompTargetValue,  
        @CommentValue = UserPoints.Comment  
       FROM UserPoints --WITH (NOLOCK)  
       WHERE UserPoints.[Year]=@year and UserPoints.weekno = @MaxWeek and UserPoints.[UserID] = @userID and UserPoints.[TargetID] = @targetID and UserPoints.status=1  
      END    
      ELSE  
      BEGIN  
       SELECT   
        @PointValue = UserPoints.pointStatus,  
        @CommentValue = UserPoints.Comment  
       FROM UserPoints --WITH (NOLOCK)  
       WHERE UserPoints.[Year]=@year and UserPoints.weekno=@MaxWeek and UserPoints.[UserID] = @userID and UserPoints.[TargetID] = @targetID and UserPoints.status=1  
      END  
       
      if (@pointStatusStr = '' or @pointStatusStr is null)  
      begin  
       if ( @PointValue = '' or @PointValue is NULL)    
       --begin  
        SET @pointStatusStr = ' ' + ' , ';  
       --end  
       else  
       --begin  
        --SET @pointStatusStr = @PointValue + ' , ';  
        --SET @PointValue = '';  
          SET @pointStatusStr = @PointValue + ' , '; set @PointValue = '';
       --end  
      
       if ( @CommentValue = '' or @CommentValue is NULL)   
       --begin   
        SET @commentStr = ' ' + ' [DELIM] ';   
       --end  
       else  
       --begin  
        --SET @commentStr = @CommentValue + ' [DELIM] ';   
        --SET @CommentValue = '';     
       --end  
           SET @commentStr = @CommentValue + ' [DELIM] '; set @CommentValue = '';     
      end  
      else  
      begin  
       if ( @PointValue = '' or @PointValue is NULL)  
       --begin    
        SET @pointStatusStr = @pointStatusStr  + ' ' + ' , ';  
       --end  
       else  
       --begin  
        --SET @pointStatusStr = @pointStatusStr + @PointValue + ' , ';  
        --SET @PointValue = '';  
       --end  
           SET @pointStatusStr = @pointStatusStr + @PointValue + ' , '; set @PointValue = '';  
      
       if ( @CommentValue = '' or @CommentValue is NULL)  
       --begin    
        SET @commentStr = @commentStr + ' ' + ' [DELIM] ';   
       --end  
       else  
       --begin  
        --SET @commentStr = @commentStr + @CommentValue + ' [DELIM] ';      
        --SET @CommentValue = '';  
       --end  
      SET @commentStr = @commentStr + @CommentValue + ' [DELIM] '; set @CommentValue = '';  
      end  
        
       
      SET @MaxWeek = @MaxWeek - 1;  
     END  
        
     RETURN @pointStatusStr + ' [DELIMITER] ' + @commentStr  
       
        
    END  
      
      
      
      
      
    


    Brijesh Vaidya India

    Wednesday, June 12, 2013 11:23 AM

Answers

All replies