calling func within cursor vs join

Posted on 2013-08-28
Medium Priority
Last Modified: 2013-09-03
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
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?
Question by:25112
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 total points
ID: 39445769
<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.
LVL 41

Assisted Solution

ralmada earned 800 total points
ID: 39445795
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:

LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 39445867
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.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 49

Assisted Solution

PortletPaul earned 400 total points
ID: 39447902
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?

Author Comment

ID: 39452389
you said it right.. i tested with some heavy parameters.. cursors were better than cross join in performance.

Here is the UDF logic:
     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.
any comments welcome.
LVL 41

Accepted Solution

ralmada earned 800 total points
ID: 39452418
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.

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question