Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Scalar UDF different performance on different server Expand / Collapse
Author
Message
Posted Friday, November 08, 2013 5:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 1,654, Visits: 5,209
shahgols (11/8/2013)
This is a shot in the dark, but I am putting it out there just in case.

I compared the actual execution plans and the XML of the staging execution plan contains these weird characters after the WHERE clause that don't exist in my query (or the query would obviously fail). Anyone know what these are?

StatusId not in ('S','O','L') "


Just CR/LF pairs ...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1512863
    Posted Friday, November 08, 2013 5:32 PM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Tuesday, April 15, 2014 2:45 PM
    Points: 619, Visits: 4,128
    You are right, that's what they were....

    I was hoping they were something more important, and they would be the reason why my query runs faster in staging.



    Post #1512865
    Posted Friday, November 08, 2013 5:37 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 6:35 AM
    Points: 1,654, Visits: 5,209
    What does the UDF in question do? It may be pertinent.

    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1512871
    Posted Friday, November 08, 2013 8:17 PM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Friday, March 14, 2014 2:19 AM
    Points: 2,820, Visits: 3,916
    TheSQLGuru (11/8/2013)
    the engine LIES to SSMS when UDFs are in play.
    please elaborate. Or ANy article reference.


    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done
    Post #1512880
    Posted Saturday, November 09, 2013 9:31 AM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Yesterday @ 5:07 PM
    Points: 4,128, Visits: 5,840
    shahgols (11/8/2013)
    I really appreciate your response and patience!

    If I run Profiler, the sql statement will never finish. haha

    I am using sp_whoisacitve. the CPU usage is much, muuuuch higher on production than it is on staging. And the "reads" column is somewhat higher. On staging, where the script runs in 25 seconds, the CPU shows 19K cycles, and reads shows 45K reads. On production, after 3 hours of running the same script, the CPU is at 9M cycles, and reads is at 64K reads.

    I hope that answers your question, otherwise let me know what to do (that doesn't involve Profiler) please.


    My guess is that you are running Profiler with STATEMENT completed as opposed to BATCH completed events. NEVER run profiler with STATEMENT completed if you have cursors, UDFs, etc in your production system. BATCH completed won't cause the query to run slowly by trying to capture every execution of the guts of the UDF like STATEMENT completed will.


    Best,

    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru at GMail
    Post #1512917
    Posted Saturday, November 09, 2013 1:22 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 7:19 AM
    Points: 35,978, Visits: 30,270
    Bhuvnesh (11/8/2013)
    TheSQLGuru (11/8/2013)
    the engine LIES to SSMS when UDFs are in play.
    please elaborate. Or ANy article reference.


    Here's the proverbial tip of the iceberg on that subject...
    http://www.sqlservercentral.com/articles/T-SQL/91724/


    --Jeff Moden
    "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    "Change is inevitable. Change for the better is not." -- 04 August 2013
    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1512924
    Posted Sunday, November 10, 2013 2:52 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Sunday, November 10, 2013 2:48 PM
    Points: 3, Visits: 4
    Thanks to knowing about it
    Post #1512988
    Posted Tuesday, November 12, 2013 10:12 AM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Tuesday, April 15, 2014 2:45 PM
    Points: 619, Visits: 4,128
    Bhuvnesh (11/8/2013)
    TheSQLGuru (11/8/2013)
    the engine LIES to SSMS when UDFs are in play.
    please elaborate. Or ANy article reference.


    Below is the UDF:

    CREATE FUNCTION [dbo].[CheckDigit](@vol varchar(50))
    returns int
    as
    begin


    declare @trdx bigint,
    @i int

    select @i = 0,
    @trdx = 0,
    @vol = replace(@vol,'.','')

    if isNumeric(@vol) = 1
    begin

    while len(@vol) > 0
    begin

    if @i in (0,1)
    begin
    select @i = 3
    end
    else if @i = 3
    begin
    select @i = 7
    end
    else if @i = 7
    begin
    select @i = 1
    end


    select @trdx = @trdx + left(@vol,1) * @i

    if LEN(@vol) = 1
    begin
    select @vol = ''
    end
    else
    begin
    select @vol = substring(@vol,2,len(@vol))
    end

    end
    end


    return case when @trdx % 11 = 0 then 1
    when @trdx % 11 = 1 then 0
    else 11 - @trdx % 11
    end

    end



    Post #1513544
    Posted Tuesday, November 12, 2013 10:19 AM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Yesterday @ 5:07 PM
    Points: 4,128, Visits: 5,840
    1) Yep - that's a scalar UDF alright

    2) At least it doesn't have ancillary data access!

    3) Looks like some kind of funky iterative logic that might be able to take advantage of a Numbers table

    4) How is it used in the query? If in the WHERE clause you are pretty much guaranteed to be hosed.

    5) I wonder if a persisted calculated column would be workable here...


    Best,

    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru at GMail
    Post #1513551
    Posted Tuesday, November 12, 2013 10:32 AM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Tuesday, April 15, 2014 2:45 PM
    Points: 619, Visits: 4,128
    Those are good workarounds, and it might be what we will end up doing. The SQL statement is something like this:

    select someID, right ('0000000000' + convert(varchar(10), a.someID, 10)) +
    convert (varchar, dbo.CheckDigit(right('0000000000' _ convert(varchar, someID),10)))
    from my_table as a inner join some_other_table b on blah = blah
    where some_column not in ('a', 'b', 'c');


    EDIT: Both "blah" columns are bigint, and have a clustered index on them.



    Post #1513555
    « Prev Topic | Next Topic »

    Add to briefcase ««123»»

    Permissions Expand / Collapse