Table-valued Function

Question

I've got a Table-valued Function that used to be working, but not anymore or at least takes to much time.

The only thing that might have changed is the ammount of data on the tables the query reads.

This is the code for the function.

ALTER FUNCTION [dbo].[SUPERVISOR_NOMINA2] (@iniDATE DATE,@endDATE DATE)
RETURNS @TABLA TABLE (
     [ID]               INT         NOT NULL PRIMARY KEY CLUSTERED
    ,[EFEC]             FLOAT       NOT NULL
    ,[TOP]              FLOAT       NOT NULL
    ,[EXTRA]            FLOAT       NOT NULL
    ,[DEUDA]            FLOAT       NOT NULL
    )

AS 
BEGIN

    INSERT INTO @tempTABLE

    /*------RUN FROM HERE TO TEST-------
    DECLARE @iniDATE DATE,@endDATE DATE
    SET @iniDATE = '2013-02-20'
    --*/

    SELECT 
    SOURCE.[ID],SOURCE.[NAME],SOURCE.[CAMPAIGN],[SUPERVISOR],[FLOOR MANAGER],[LAST LOGON],[EFEC],[TOP]
        ,[HR+] AS EXTRA
        ,CASE
            WHEN [DIF] >= 0 THEN 0
            ELSE [DIF]
        END AS DEUDA
    FROM (
    SELECT  
           NM.[ID]
          ,NM.[NAME]
          ,NM.[CAMPAIGN]
          ,NM.[SUPERVISOR]
          ,SUM(CASE 
             WHEN [ATT ID] > 4 AND [ATT ID] <> 7 THEN '0'
             ELSE ROUND([EFEC],2)
          END) AS [EFEC]
          ,SUM(CASE 
             WHEN [ATT ID] > 4 AND [ATT ID] <> 7 THEN '0'
             ELSE [TOP]
          END) AS [TOP]
          ,SUM(CASE 
             WHEN [ATT ID] > 4 AND [ATT ID] <> 7 THEN '0'
             WHEN [EFEC] < [TOP] THEN 0
             WHEN [TOP]+[EXHR] > [EFEC] THEN ROUND([EFEC]-([TOP]+[EXHR]),0,1)
             ELSE [EXHR]
          END) AS [HR+]
          ,ROUND(SUM(CASE 
             WHEN [ATT ID] > 4 AND [ATT ID] <> 7 THEN '0'
             WHEN [EFEC] < [TOP] THEN ROUND([EFEC]-[TOP],2)
             WHEN [TOP]+[EXHR] > [EFEC] THEN ROUND([EFEC]-([TOP]+ROUND([EFEC]-([TOP]+[EXHR]),0,1)),2)
             ELSE ROUND([EFEC]-([TOP]+[EXHR]),2)
          END),2) AS [DIF]

      FROM [WFO].[dbo].[VIEW_NOMINA] NM
      FULL JOIN [WFO].[dbo].[VIEW_ATTENDANCE] AT ON NM.ID = AT.ID AND NM.DATE = AT.DATE
      WHERE NM.DATE < CONVERT(DATE,GETDATE()) AND NM.[DATE] BETWEEN @iniDATE AND @endDATE
      GROUP BY NM.[ID],NM.[NAME],NM.[CAMPAIGN],NM.[SUPERVISOR]) SOURCE
      LEFT JOIN (
          SELECT [ID],CONVERT(DATE,MAX(DATETIME)) AS [LAST LOGON]
          FROM [WFO].[dbo].[AGENT-LOGBOOK] 
          GROUP BY [ID]
      ) LT ON LT.ID = SOURCE.ID
      LEFT JOIN [WFO].[dbo].[SUPERVISORS] SP ON SP.NAME = SOURCE.SUPERVISOR
      ORDER BY [NAME]
RETURN
END

The funny part is that if I run just the query just after the insert statement and before the return statement the query works in seconds.

What do you suggest I'm doing wrong?

Please show us your execution plan. For future reference, the execution plan in something you will want to include in any question about database performance. — Abe Miessler, Mar 04 '13 at 21:32

score 0 · Answer 1 · answered Mar 04 '13 at 21:34

One possible issue is this SQL in your where clause:

NM.DATE < CONVERT(DATE,GETDATE())

Besides the fact that I'm pretty sure the CONVERT is unnecessary in this case, using functions in this manner can often make it so indexes that you have in place are not used.

score 0 · Answer 2 · answered Mar 04 '13 at 21:39

0

Does this even run? Many of your CASE statements return a char for one case a number for the other. This should fail. Change all lines like this

 WHEN [ATT ID] > 4 AND [ATT ID] <> 7 THEN '0'

to this

 WHEN [ATT ID] > 4 AND [ATT ID] <> 7 THEN 0

answered Mar 04 '13 at 21:39

Hogan

69,564
10
76
117

it does when i run the query, but doesn't when i try to run the function. I tried changing what you said and still the same. – ezamora Mar 04 '13 at 22:13

Table-valued Function

2 Answers2