UDF Vs CTE

Question

Post reply

UDF Vs CTE

SQL_Surfer

SSCrazy Eights

Points: 8318
More actions
March 2, 2012 at 9:54 pm

#143221

Can we rewrite this UDF to CTE? I've got several thousdands of records that in the main table which passes id to the UDF below. It returns the date which is what is used to update the main table. But its taking very long time to do this function call. I was wondering if there is a work around for this.
UDF is attached.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

Hi,

A quick win here might be to use an inline-table-valued function, rather than a scalar.

Something llike ...

CREATE function [dbo].[ReturnDate]

( @id int)

returns table

as

return(

select max(c.SDate)

from (Select distinct b.id, b.mDate

from dbo.TableA b

join dbo.TableB c on b.srn = c.srn

where b.rn = @id) c

join dbo.TableA d on c.srn = d.srn

and @id <> d.rn)

)

Then use CROSS APPLY to 'call' the function in your main select

Untested , syntax might be a bit wrong...

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 2

SQL_Surfer (3/2/2012)
Can we rewrite this UDF to CTE? I've got several thousdands of records that in the main table which passes id to the UDF below. It returns the date which is what is used to update the main table. But its taking very long time to do this function call. I was wondering if there is a work around for this.
UDF is attached.

I agree with Dave. Converting the function to an iTVF (inline Table Value Function) might be a quick win.

However, if we strip out just the working part of the attached text, I see some problems that will keep this function from even being formed...

select max(c.SDate)

from (

Select distinct b.id, b.mDate

from dbo.TableA b

join dbo.TableB c on b.srn = c.srn

where b.rn = @id

) c

join dbo.TableA d on c.srn = d.srn

and @id <> d.rn

max(c.SDate) will fail because it is not returned in the "c" alias.

c.sm will fail because it is not returned in the "c" alias.

Yes, I know the code has been genericised for publication on this forum but I thought I'd make you aware that the genericised code just isn't going to work the way it is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)