When UDF is used in Where clause

• pratapkrg

  SSC Enthusiast

  Points: 157

  More actions

  September 1, 2003 at 5:21 am

  #82412

  we trying to use a UDF in Where Clause. This is taking too much of time. when we replaced the UDF with a subquery the query is fast.

  Eg:

  select Name, Designation, Address From Employee Where dbo.GetAge(EmpId) > 25

  This is taking very long to fetch 12 records from 22,00,000 records.

  When the same query has been converted to

  select E.Name, E.Designation, E.Address From Employee E Where (select datediff("YY", EP.DOB, GETDATE()) from EmpPersonaldetail EP where EP.Empid= E.EmpId)

  this gets executed very fast.

  What could be the reason?

  Pls help.

  Thanks In Advance,

  Pratap Kumar Gogineni

• NPeeters

  SSChampion

  Points: 12535

  More actions

  September 1, 2003 at 5:31 am

  #472094

  This is a general 'feature' of UDFs. They tend to be slower than a native T-SQL solution.

  The reason for this is the fact that the query optimiser treats a UDF differently than a subquery.