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?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<not much of an answer, but...>

Guessing the udf will be slightly faster, as both scenarios make the call 1000x, but the cursor has the cursor overhead.

Either way it would help if both tables had covering index for these three columns.
0
ralmadaCommented:
you will have to check your execution plan for both scenarios.

I would still be inclined to avoid the cursor. But, I can't tell for sure because I'm not familiar with your case. However, I would question you why you need to use the function that way. I would suggest you read this article:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/fe5a0fc9-76b0-4dd4-9fb2-ff877a1724fe/cross-apply-slower-than-cursor
0
Scott PletcherSenior DBACommented:
If dbo.test_udf is an inline table function, you will get vastly better performance from it vs. a cursor and calling separately to a stored proc or function.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulfreelancerCommented:
I would probably use the cross joined query in preference to the cursor.

but as Scott suggests, more dramatic results might occur if the function itself can be tuned for performance.

can you share more info about the udf itself?
0
25112Author Commented:
you said it right.. i tested with some heavy parameters.. cursors were better than cross join in performance.

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.
0
ralmadaCommented:
Clearly your function doesn't seem to be optimized. If you have 96 CASE Statements, that will lead me to conclude that you have a design problem. If you can, I would strongly suggest you revise it. Maybe it's time to add some tables to get rid of those CASE statements...

If not, an your testing indicates that cursor perform better for you then go for it. But as a rule of thumb, try to use them as the last resort.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.