25112
asked on
calling func within cursor vs join
is there a drastic performance benefit to calling the below way,
SELECT * FROM (SELECT column1, column2, column3 FROM test ) AS U
cross apply (SELECT dbo.test_udf (U.column1, U.column2, U.column3) Derived_Column) AS t
compared to
putting
SELECT column1, column2, column3 FROM test
in a cursor, and then passing the call to the function from within a cursor loop, one at a time?
let's say there are 1000 records returned from the query
SELECT column1, column2, column3 FROM test
the cursor will loop 1000 times. the cross join also executes/calls the function 1000 times, right?
SELECT * FROM (SELECT column1, column2, column3 FROM test ) AS U
cross apply (SELECT dbo.test_udf (U.column1, U.column2, U.column3) Derived_Column) AS t
compared to
putting
SELECT column1, column2, column3 FROM test
in a cursor, and then passing the call to the function from within a cursor loop, one at a time?
let's say there are 1000 records returned from the query
SELECT column1, column2, column3 FROM test
the cursor will loop 1000 times. the cross join also executes/calls the function 1000 times, right?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is the UDF logic:
CREATE FUNCTION test_udf
(
Params
)
RETURNS TABLE
AS
RETURN
(
SELECT
columns,
96 CASE Statements,
/*
CASE @Variable WHEN 'Value' THEN dbo.UDF2 (96different parameters hard coded and some passed) ELSE '' END AS [ColumnName]
test_udf returns table
UDF2 returns VARCHAR(20) -
UDF2 has lot of calculations - 65 ELSE IF THEN Statements to find the value of the final percentage.
*/
FROM
@UserDefinedTableType
);
any comments welcome.