Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Table-valued Function

Answered Table-valued Function

  • Monday, March 04, 2013 9:14 PM
     
      Has Code
    Sign In to Vote
    0

    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.

    USE [WFO]
GO
/****** Object:  UserDefinedFunction [dbo].[SUPERVISOR_NOMINA]    Script Date: 03/04/2013 15:05:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SUPERVISOR_NOMINA] (@iniDATE DATE,@endDATE DATE)
RETURNS @tempTABLE TABLE (
		 [ID]				INT			NOT NULL PRIMARY KEY CLUSTERED
		,[NAME]				VARCHAR(50)	NOT NULL
		,[CAMPAIGN]			VARCHAR(50)	NOT NULL
		,[SUPERVISOR]		VARCHAR(50)	NOT NULL
		,[FLOOR MANAGER]	VARCHAR(50)	NOT NULL
		,[LAST LOGON]		DATE		NULL
		,[EFEC]				FLOAT		NOT NULL
		,[TOP]				FLOAT		NOT NULL
		,[EXTRA]			FLOAT		NOT NULL
		,[DEUDA]			FLOAT		NOT NULL
		

		
		)
AS
BEGIN

		INSERT INTO @tempTABLE
		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??

     

All Replies

  • Monday, March 04, 2013 9:43 PM
     
     Answered Has Code
    Sign In to Vote
    0

    How many rows does the query return?  I have seen many times that table variables do not perform very well with more than a couple thousand rows.  The workaround in a stored procedure is to switch to temp tables (or physical tables, depending on your situation).  That of course does not help you in a function.

    However, you can ditch the @Table altogether, and do this:

    CREATE FUNCTION [dbo].[SUPERVISOR_NOMINA] (@iniDATE DATE,@endDATE DATE)
RETURNS TABLE
AS
RETURN
	(
		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]
	)

    Not sure if it will help performance or not, but the return will be the same.

     
  • Monday, March 04, 2013 9:46 PM
     
     
    Sign In to Vote
    0

    If an increase in the volume of data being returned has caused the problem it is a good chance that switching from a table valued function to an indexed parameterized view or to a stored procedure. In a stored procedure you can explicitly or implicity create a temp table, which is better at handling larger rowsets than the table variable which is your return parameter. A stored procedure can simply execute the query and implicitly return the rowset.

    Have you run a query plan on the query this is made from? Or a select statement using the table function?

    Is there an index on or including VIEW_NOMINA.DATE? Have you tried comparing the performance of an inner join in place of the full join?

    If you're happy and you know it vote and mark.

     
  • Tuesday, March 05, 2013 12:04 AM
     
     
    Sign In to Vote
    0
    Good SQL programmer do not use functions; we know that SQL is a declarative language and do not write highly proprietary dialect procedural code like this. 

    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 amount of data on the tables the query reads.

    >> This is the code for the function. <<

    But no DDL for the tables? Why did you violate ISO-11179 rules for data element names? I see you learned to program on punch cards!! The uppercase code that people cannot read and the leading commas are sure signs of a 1960's programmer. You use a generic magical “id” that no SQL programmer would. And lots of magical VARCHAR(50) for everything to mimic a COBOL record. Or were you a FORTRAN programmer? We use DECIMAL and not FLOAT today; it is illegal in many cases, slow and has rounding errors. That is why your code is full of ROUND() function calls. 

    Why do you think that “name”, “top”, etc are clear, precise and useful data element names? Why are you formatting column headers in the database? FULL OUTER JOINs? You almost never see that in a good schema. 

    Why did you put an ORDER BY on an INSERT statement? Tables have no ordering; you are stilling writing 1960's mag tape programs in T-SQL! 

    The short answer is to do this as a CREATE VIEW instead. The real answer is that your code is badly written and cannot be optimized. I tried to correct your code but without DDL and specs, I cannot do it. Want to throw out this mess and try again, following good practices and industry standards?  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL