Post reply

Any idea why this is slow

  • June 22, 2015 at 10:07 am

    #305322

    This is very slow

    Update #acrc

    Set sbp_mbp = [RESEARCH].[dbo].[determine_num_of_mins_exceed_5_where_SBP_MAP_is_less_than_a_certain_value](Case_ID);

    However if I do a

    Select @sbp_mbp= [RESEARCH].[dbo].[determine_num_of_mins_exceed_5_where_SBP_MAP_is_less_than_a_certain_value]('12345');

    --This gets executed within a few seconds

  • June 22, 2015 at 10:08 am

    #1807098

    How many rows in the temp table?

    What does that function do?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 22, 2015 at 11:22 am

    #1807119

    901

  • June 22, 2015 at 11:23 am

    #1807121

    Also it has an index on the column case_id

  • June 22, 2015 at 11:46 am

    #1807128

    mw112009 (6/22/2015)

    Select @sbp_mbp= [RESEARCH].[dbo].[determine_num_of_mins_exceed_5_where_SBP_MAP_is_less_than_a_certain_value]('12345');

    --This gets executed within a few seconds

    I made some emphasis on a phrase because it can tell you what's wrong.

    Imagine that few seconds mean 2 seconds. Two times 901 equals 1802, that means that it will take 30 minutes to go through all the rows. Of course, I'm just making an assumption, but it probably will be doing a row by row execution of the function just to define the value to update the column.

    With more information about the function, we might be able to help a little bit further.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 22, 2015 at 1:47 pm

    #1807183

    Luis Cazares (6/22/2015)

    Of course, I'm just making an assumption, but it probably will be doing a row by row execution of the function just to define the value to update the column.

    That's not an assumption, that's exactly how scalar user-defined functions work. With 901 rows in the table, that function has to execute 901 times. That's going to be the cause of the problem here and the solution is to remove the function and write the logic all in one query, or possibly turn it into an in-line table-valued function (which are the only ones that perform well)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 22, 2015 at 10:54 pm

    #1807241

    mw112009 (6/22/2015)

    This is very slow

    Update #acrc

    Set sbp_mbp = [RESEARCH].[dbo].[determine_num_of_mins_exceed_5_where_SBP_MAP_is_less_than_a_certain_value](Case_ID);

    However if I do a

    Select @sbp_mbp= [RESEARCH].[dbo].[determine_num_of_mins_exceed_5_where_SBP_MAP_is_less_than_a_certain_value]('12345');

    --This gets executed within a few seconds

    Gail and the others are correct. If you look at the SELECT above, you're saying that a single execution of the function "gets executed within a few seconds". The update that you're trying to pull of works against 901 rows. If a "few" seconds follows the classic definition of being at least 3 seconds, that will be a total of 901*3 or 2,703 seconds or at least 45 minutes.

    You need to post the function so we can have a look at it because that's where the problem is.

    --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.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply