Option(RECOMPILE)
in [Programming SQL]
|
From: sam on 9 Mar 2010 04:30 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
From: Dave Ballantyne on 9 Mar 2010 04:47 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/ sam wrote: > 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
From: Uri Dimant on 9 Mar 2010 07:06 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.jangra2009(a)gmail.com> wrote in message news:f6026ee0-b11c-4e4e-9ece-81ebfd5304c4(a)s36g2000prh.googlegroups.com... >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
From: Plamen Ratchev on 9 Mar 2010 10:06 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
|
Pages: 1 Prev: Query Decimal Date field for Yesterday's Records Next: Full outer join on 3 tables. How to? |