Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    81

    Unanswered: Function vs. Sub-Query

    When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?

    Bjorn

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    I've seen this too. In my case when I looked at the execution plan and the server trace it appears the udf is called for each row returned where the sub query doesn't. I was using a udf to calculate the status of the records. I ended up using a view instead to calculate the status and joined my original query to the view. This is similiar to a sub query and much much faster than the udf.

  3. #3
    Join Date
    Jan 2003
    Posts
    81
    Thanks! It makes sense. It seems that the sub query runs first and only once in an sproc, extracting all the data needed for the main query.

    Bjorn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •