September 5, 2012 at 10:34 am
I have a udf that is taking forever to run.
I suspect also that the database is not set up correctly.
When I place a return 123; as the first line of the function and then execute the function it takes over two minutes to come back with the 123 value even though no code is executing??
No Op Function code...
ALTER FUNCTION [aoc_rpt].[udfStatCalcDvTotals] (@COL INT, @STARTDATE DATETIME, @ENDDATE DATETIME, @COUNT_TYPE CHAR(1),@REPORT CHAR(1) = NULL)
RETURNS INT
BEGIN
--COUNT UP DV CASES FOR REPORT SUBSECTIONS
DECLARE @DV_COUNT INT
--REMOVE AFTER TESTING
RETURN 123
...
T-sql to execute udf..
declare @DV_TOTAL int = 0
SET @DV_TOTAL = 0
SET @DV_TOTAL = aoc_rpt.udfStatCalcDvTotals(2,'01/01/12','04/30/12','F','C')
SELECT @DV_TOTAL
GO
RETURNS 123 AFTER 2:00 + MINUTES
September 5, 2012 at 11:14 am
well what you posted is a scalar function; ideally you want it to be an inline table valued function isntead.
after correcting some syntax, and putting it in my default dbo schema instead the query below returns instantly;
From there's i'd run it again, and this time include the actual execution plan as part of the results.
with that, we could tell you a lot more about what is going on, if you can post the .sqlplan xml file here.
CREATE FUNCTION [udfStatCalcDvTotals] (@COL INT, @STARTDATE DATETIME, @ENDDATE DATETIME, @COUNT_TYPE CHAR(1),@REPORT CHAR(1) = NULL)
RETURNS INT
AS
BEGIN
--COUNT UP DV CASES FOR REPORT SUBSECTIONS
DECLARE @DV_COUNT INT
--REMOVE AFTER TESTING
RETURN 123
END
GO
declare @DV_TOTAL int = 0
SET @DV_TOTAL = 0
SET @DV_TOTAL = dbo.udfStatCalcDvTotals(2,'01/01/12','04/30/12','F','C')
SELECT @DV_TOTAL
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply