SQL Server Performance

UDF ..constant scan

Discussion in 'General DBA Questions' started by vijaygarapati, Apr 27, 2006.

  1. vijaygarapati New Member

    Hi all,

    We have a UDF which is returning the result in 2 seconds.

    When i copy the code(just two select stmts) in the UDF and hardcode the values, and execute these two stmts in QA its taking 0 sec to execute.[ when I looked at the execution plans, it is using the clustered index seeks] so far so good.

    But when i pass the parameters on to the UDF ,and call it from a select stmt, the execution plan shows that its using a constant scan instead of the indexe seek. resulting in 2 seconds of execution time.

    i was wondering why it using a constant scan when called as a UDF while its actually using index seeks when i seperately execute the two select stmts in QA.

    Any help will be appreiated

    Regards.
  2. Adriaan New Member

    Try declaring a local variable inside the UDF, into which you pass the parameter values, then use the variables instead of the parameters in the criteria. "Parameter sniffing" is a frequent subject here on the site.

    By the way, if the UDF is just doing two select statements, then why are you using a UDF in the first place? You should use UDFs only for stuff that you normally cannot do in a SELECT (like a comma-separated list of values from a childtable).
  3. vijaygarapati New Member

    Thanks for you quick responce

    I tried using a local variable and passing on the value of the parameter into this local variable and using this ocal variable from there on , but there is no change in either the execution time or execution plan( its still going for a constant scan)

    We are using UDF so that we can call it from several other SPs as an inline stmt.

    Thanks.
  4. Madhivanan Moderator

    Can you post the code you used in the UDF?

    Madhivanan

    Failing to plan is Planning to fail
  5. FrankKalis Moderator

  6. Adriaan New Member

    Like Madhivanan said, post the UDFs, and also the relevant data structure from where you're calling the UDF.
  7. vijaygarapati New Member

    here is the UDF.

    As far as parameter sniffing is concerned , I don't know how far it holds true incase of UDF, all the parameter sniffing articles explain it with a stored proc example.


    CREATE FUNCTION dbo.GetMCByUCI
    (
    @UCI bigint
    )
    RETURNS Char(15)
    AS
    BEGIN
    Declare @MC char(15)
    Declare @ActiveUCI bigint

    Select@MC = MatchCode
    FromWebDataBridge.dbo.CMK
    WhereActiveUCI = (Select ActiveUCI
    From WebDataBridge.dbo.CMK
    Where UCI =@UCI) and Active = 0

    Return @MC

    END

    we are calling it as : select dbo.getmcbyuci(2000001234564).

    Thanks.
  8. Adriaan New Member

    So you're sure you're not calling it like this:

    SELECT T.MyColumn, dbo.getmcbyuki(T.MyColumn)
    FROM MyTable T

    ?
  9. vijaygarapati New Member

    Yes I am sure
  10. vijaygarapati New Member

    On the second thought, we do have an sp which calls the UDF in the way you represented
  11. Adriaan New Member

    Why are you using a subquery here? This will work just fine:

    Select @MC = MatchCode
    From WebDataBridge.dbo.CMK
    Where ActiveUCI = @UCI and Active = 0

  12. mmarovic Active Member

    How about:
    CREATE FUNCTION dbo.GetMCByUCI
    (
    @UCI bigint
    )
    RETURNS Char(15)
    AS
    BEGIN
    Declare @MC char(15)

    Select @MC = inactive.MatchCode
    From WebDataBridge.dbo.CMK given
    join WebDataBridge.dbo.CMK inactive on given.activeUci = inactive.ActiveUci
    where inactive.active = 0
    and given.uci = @uci

    Return @MC

    END
  13. Adriaan New Member

    Another question is whether the MatchCode column of your table is actually of the BIGINT data type. If not, then convert/cast the criteria value.

    If you're calling this in a SELECT for a rowset, then you might as well just add WebDataBridge.dbo.CMK to your FROM clause, joining on the column from which you were taking the parameter. This allows SQL to work out the optimum way to handle the lookup, which it can't really do if you're using a UDF.
  14. Adriaan New Member

    Mirko, what are you seeing that I'm missing? Or do you know the system?
  15. mmarovic Active Member

    Adriaan: First, I want to mentioned that your recommendation not to use udf, but to join the table is something I wanted to recommend as well, but you were faster.

    About the query: Based on the query uci is the unique row identifier, activeUci doesn't have to be. So he wants for the given 'uci' to find matcheCode based on inactive row containing the same "activeUci" as the row maching input parameter.
  16. mmarovic Active Member

    Btw. we were typing our solutions at the same time, so I haven seen your post before posting mine. You obviously type faster. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  17. Adriaan New Member

    Should have seen that - correct, he needs the subquery. When transforming the UDF into a straight SELECT query, it would be something like this:

    SELECT T.*, inactive.MatchCode
    FROM dbo.BaseTable T
    INNER JOIN WebDataBridge.dbo.CMK given
    ON T.uci = given.uci
    INNER JOIN WebDataBridge.dbo.CMK inactive
    ON given.activeUci = inactive.ActiveUci
    WHERE inactive.active = 0

    However, I wonder what happens if there is no inactive record, and whether and how that should be covered.
  18. vijaygarapati New Member

    Thanks for all your replies.

    by testing your solutions on the code, i was able to gain on the execution time by a sec or two but i am still waiting for an answr as to why its going for a constant scan as opposed to using the indexes.

    Thank you all once again.
  19. Adriaan New Member

    How many rows in that CMK thingy - and is it a table, or perhaps a view, or perhaps a view that fakes a table like this:

    SELECT <expression1>
    UNION ALL
    SELECT <expression2>
    UNION ALL
    SELECT <expression3>

    etc. etc.
  20. cmdr_skywalker New Member

    I don't think it will matter much in terms of performance. The "oerhead" is caused when SQL performs the substitution and looks for the existing plan on the cache compared to hard code where it goes directly to the existing plan.


    May the Almighty God bless us all!
    www.empoweredinformation.com
  21. cmdr_skywalker New Member

    well, it will matter when your dealing with a lot of records. sorry, its late <img src='/community/emoticons/emotion-1.gif' alt=':)' />. But the caused is how SQL performs the substitution, check the cache and resolve the final plan compared to using an hard-coded SQL.<br /><br />Clustered Index Seek<br />The clustered index is used to find matching rows. This is optimal behaviour. <br /><br />Constant Scan<br />The query requires a constant value in some (or all) rows <br /><br />check this out:<br /<a target="_blank" href=http://www.dotnetjohn.com/articles.aspx?articleid=152>http://www.dotnetjohn.com/articles.aspx?articleid=152</a><br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  22. FrankKalis Moderator

    quote:
    Clustered Index Seek
    The clustered index is used to find matching rows. This is optimal behaviour.

    Constant Scan
    The query requires a constant value in some (or all) rows
    What should this tell us now?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  23. mmarovic Active Member

    quote:Originally posted by vijaygarapati

    Thanks for all your replies.

    by testing your solutions on the code, i was able to gain on the execution time by a sec or two but i am still waiting for an answr as to why its going for a constant scan as opposed to using the indexes.

    Thank you all once again.
    When building query estimated execution plan and calculating cost, query optimizer doesn't take into consideration udf code. I guess It uses "magic number" for estimation of cpu time but doesn't expand udf code to calculate i/o costs. I think it is good idea to page Joe Chang, he worked on reverse engineering query optimizer's cost calculating formulas.
  24. vijaygarapati New Member

    -----------------
    When building query estimated execution plan and calculating cost, query optimizer doesn't take into consideration udf code. I guess It uses "magic number" for estimation of cpu time but doesn't expand udf code to calculate i/o costs.
    ------------------

    I think you are right, because even though I tried to force the SQL server to use the Indexes present on the table (by using the index hints) its still going for a constant scan and interestingly its taking 7 seconds to retrieve the result set as opposed to 2 sec when the index hit is not specified.

  25. vijaygarapati New Member

    ----------------

    How many rows in that CMK thingy - and is it a table, or perhaps a view, or perhaps a view that fakes a table like this:

    SELECT <expression1>
    UNION ALL
    SELECT <expression2>
    UNION ALL
    SELECT <expression3>

    etc. etc.

    ------------------

    CMK is a regular table with 10.5 mil rows
  26. mmarovic Active Member

    Aside from joining the cmk table the way Adriaan described, which is better solution for stored procedure that uses udf in select, you can also try next implementation:

    CREATE FUNCTION dbo.GetMCByUCI
    (
    @UCI bigint
    )
    RETURNS Char(15)
    AS
    BEGIN
    Declare @MC char(15)

    Set @MC = (select top 1 inactive.MatchCode
    From WebDataBridge.dbo.CMK given
    join WebDataBridge.dbo.CMK inactive on given.activeUci = inactive.ActiveUci and inactive.active = 0
    where given.uci = @uci)

    Return @MC

    END

    [edited]Moved condition inactive.active = 0 from where to join clause.
  27. Adriaan New Member

    Splitting hairs, perhaps, but I wonder if the JOIN is less appropriate here than a subquery to filter on the one record. Does the TOP 1 clause have priority with SQL over the JOIN?

    What would happen if we split this into two TOP 1 queries?

    Also,
    quote:Originally posted by vijaygarapati

    -----------------
    When building query estimated execution plan and calculating cost, query optimizer doesn't take into consideration udf code. I guess It uses "magic number" for estimation of cpu time but doesn't expand udf code to calculate i/o costs.
    ------------------

    I think you are right, because even though I tried to force the SQL server to use the Indexes present on the table (by using the index hints) its still going for a constant scan and interestingly its taking 7 seconds to retrieve the result set as opposed to 2 sec when the index hit is not specified.
    Well, this is what we've been saying already: the main query will use the indexes for JOINs and the WHERE clause, but the UDF is handled separately and cannot take advantage of the index entries already sorted out in the main query.
  28. mmarovic Active Member

    Subquery to filter just one record from original post may be efficient too, if again top 1 query is used:

    CREATE FUNCTION dbo.GetMCByUCI
    (
    @UCI bigint
    )
    RETURNS Char(15)
    AS
    BEGIN
    Declare @MC char(15)
    Declare @ActiveUCI bigint

    Set @MC = (select top 1 MatchCode
    From WebDataBridge.dbo.CMK
    Where ActiveUCI = (Select ActiveUCI
    From WebDataBridge.dbo.CMK
    Where UCI =@UCI) and Active = 0

    Return @MC

    END
    However there is no need for another top 1 query because uci is obviously unique row identifier, if it is what you mean.
  29. joechang New Member

    per above, disregard the constant scan in the execution plan for the UDF,
    that is not the real execution plan

    what is the execution plan for the query below?
    what are the estimated rows count at each step in the execution plan?

    DECLARE @UCI bigint
    Declare @MC char(15)
    Declare @ActiveUCI bigint

    Select @MC = MatchCode
    From WebDataBridge.dbo.CMK
    Where ActiveUCI = (Select ActiveUCI
    From WebDataBridge.dbo.CMK
    Where UCI =@UCI) and Active = 0

    what are the indexes on the table,
    what is the data type for UCI?
  30. vijaygarapati New Member

    quote:Originally posted by joechang

    per above, disregard the constant scan in the execution plan for the UDF,
    that is not the real execution plan

    what is the execution plan for the query below?
    what are the estimated rows count at each step in the execution plan?

    DECLARE @UCI bigint
    Declare @MC char(15)
    Declare @ActiveUCI bigint

    Select @MC = MatchCode
    From WebDataBridge.dbo.CMK
    Where ActiveUCI = (Select ActiveUCI
    From WebDataBridge.dbo.CMK
    Where UCI =@UCI) and Active = 0

    what are the indexes on the table,
    what is the data type for UCI?

    Q) what is the execution plan for the query below?

    A)
    Index scan on Active coulmn (Estimated row count = 1, query cost = 87 %),
    Bookmark lookup (Estimated row count = 1,query cost = 13%) ,
    Index seek on Active UCI (Estimated row count = 1) ,
    Inner joib(Estimated row count = 1),
    gather Streams(Estimated row count = 1),
    Bookmark lookup(Estimated row count = 1),
    filter(Estimated row count = 1) and
    the select(Estimated row count = 1)

    Q) what are the indexes on the table?

    A)
    Clustered Index on UCI
    Non-Clustered on MatchCode
    Non-Clustered on Active
    Non-Clustered on ActiveUCI

    Q) what is the data type for UCI?

    A)
    Decimal9(13,0)


  31. mmarovic Active Member

    Having composite index on ActiveUci and Active column would speed up the query. Even more efficient if matchCode is the third column in such index, however it is not very clever idea to design indexes based on just one query especially if this query is not critical for application performance.

    Anyway, I think your question about the query plan seems to be answered now.
  32. vijaygarapati New Member

    yes thats true.

    Thank you all for you valuable responses.

Share This Page