Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Option(RECOMPILE)

7 views
Skip to first unread message

sam

unread,
Mar 9, 2010, 4:30:31 AM3/9/10
to
I have a UDF say Which is caluculating salary of an employee by using
Cusror.
Now i m using that UDF in a View in which i m passing it Id for a
value in view.
That's working fine if no of records are say around 1000 but if No are
Increasing then View is working very slow
Because foreach row in view that function is executing and Output is
produced

I have Searched for solution I Find an way to use Option(Recompile)
but don't know how to use with UDF.
Any Other Suggestion to Improve performance of view will bw welcomed

Thanx

Dave Ballantyne

unread,
Mar 9, 2010, 4:47:59 AM3/9/10
to
The simple answer is dont use Scalar UDFS

I give an example and your options on my blog here :
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx

I would imagine that the cursor inside the UDF is also not needed.

Post your full code for further advice


Dave Ballantyne
http://sqlblogcasts.com/blogs/sqlandthelike/

Uri Dimant

unread,
Mar 9, 2010, 7:06:33 AM3/9/10
to
sam
In addtion , perhaps if you show us DDL+ sample data + an expected result
we can advise you to writye the query without using a cursor

"sam" <sumesh.j...@gmail.com> wrote in message
news:f6026ee0-b11c-4e4e...@s36g2000prh.googlegroups.com...

Plamen Ratchev

unread,
Mar 9, 2010, 10:06:38 AM3/9/10
to
You can see if you can rewrite the scalar UDF to table valued UDF. See example here:
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/23/reuse-your-code-with-cross-apply.aspx

Or you can avoid the function at all if possible.

--
Plamen Ratchev
http://www.SQLStudio.com

0 new messages