locked
sp takes long time RRS feed

  • Question

  • Hi,

     

    below sp is taking long time to execute. i need to update only 65000 rows. Ple help me to resolve.

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    CREATE

     

    PROCEDURE [dbo].[update_option_staging_item]

    AS

    BEGIN

    SET

     

    NOCOUNT ON;

     

    declare

    @min_count

    int=1,

    @max_count

    int,

    @var_option_rule_type_id

    int = null

     

     

     

    Begin Tran

     

     

     

    If OBJECT_ID('tempdb..#temp_option_staging_item') is not null

     

    DROP TABLE #temp_option_staging_item;

     

    create table #temp_option_staging_item

     

     

    (

    temp_id

    int identity(1,1) primary key clustered,

    [OptionID] [int]

    NULL,

    [Value] [nvarchar]

    (255) NULL,

    [Name] [varchar]

    (255) NULL,

    [Key] [int]

    NULL,

    [Order] [smallint]

    NULL,

    [parentid] [int]

    NOT NULL,

    [relationtype] [tinyint]

    NULL,

    [item_description] [nvarchar]

    (255) NULL,

    [item_name] [nvarchar]

    (255) NULL,

    [item_name1] [nvarchar]

    (255) NULL,

    [option_item_flag] [char]

    (1) NULL,

    [option_rule_type_id] [int]

    NULL,

    [item_value_id] [int]

    NULL

    )

     

     

    insert into #temp_option_staging_item

     

    select * from option_staging_item;

     

     

    select @max_count=MAX(temp_id) from #temp_option_staging_item;

     

     

    while @min_count<=@max_count

     

    begin

     

    if (select option_rule_type_id from #temp_option_staging_item where temp_id=@min_count)is not null

     

     

    select @var_option_rule_type_id=option_rule_type_id

     

    from #temp_option_staging_item where temp_id=@min_count;

     

    else

     

    begin

     

    update option_staging_item

     

    set option_staging_item.option_rule_type_id = @var_option_rule_type_id

     

    where OptionID=(select OptionID from #temp_option_staging_item where temp_id=@min_count);

     

     

    end

     

     

    set @min_count=@min_count+1

     

    end

     

     

    If OBJECT_ID('tempdb..#temp_option_staging_item') is not null

     

    DROP TABLE #temp_option_staging_item;

    Commit

     

    tran

     

    END

     

    GO

    Thanks.


    pols
    Friday, December 31, 2010 6:26 AM

Answers

  • From the looks of your procedure, it looks like you're trying to fill in the Option_Rule_Type_ID column if it's NULL, setting it to the Option_Rule_Type_ID value of the previous row.

    You can do that in a single query like so:

    declare @t table (OptionID int primary key, OptRuleTypeID int null)
    insert @t select 10,345
    union all select 20,567
    union all select 30,null
    union all select 40,null
    union all select 50,789
    union all select 60,null
    
    update t
    set OptRuleTypeID=PrvOptRuleTypeID
    from @t t
    outer apply (select top 1 PrvOptRuleTypeID=OptRuleTypeID
           from @t
           where OptionID<t.OptionID and OptRuleTypeID is not null
           order by OptionID desc) x
    where OptRuleTypeID is null
    
    select * from @t
    /*
      OptionID OptRuleTypeID
    ----------- -------------
         10      345
         20      567
         30      567
         40      567
         50      789
         60      789
    */
    
    

     

     


    --Brad (My Blog)
    • Marked as answer by KJian_ Thursday, January 6, 2011 8:17 AM
    Friday, December 31, 2010 6:07 PM

All replies

  • check every operator in execution plan.... I think insertion in temp table taking the most time.
    --------------------------------- Devender Bijania
    Friday, December 31, 2010 6:33 AM
  • Quick note --

    1. Inside While loop, you have used three select statement. Instead of it use ONE select statement and assigned the value into the variable and use the same.

    2. Perform "Join" with table "#temp_option_staging_item"

    update option_staging_item
    set option_staging_item.option_rule_type_id = @var_option_rule_type_id
    from  option_staging_item
    inner join #temp_option_staging_item temp on option_staging_item.OptionID=temp.OptionID
    Where temp .temp_id=@min_count;

    Hope this help!

     

    Friday, December 31, 2010 9:41 AM
  • From the looks of your procedure, it looks like you're trying to fill in the Option_Rule_Type_ID column if it's NULL, setting it to the Option_Rule_Type_ID value of the previous row.

    You can do that in a single query like so:

    declare @t table (OptionID int primary key, OptRuleTypeID int null)
    insert @t select 10,345
    union all select 20,567
    union all select 30,null
    union all select 40,null
    union all select 50,789
    union all select 60,null
    
    update t
    set OptRuleTypeID=PrvOptRuleTypeID
    from @t t
    outer apply (select top 1 PrvOptRuleTypeID=OptRuleTypeID
           from @t
           where OptionID<t.OptionID and OptRuleTypeID is not null
           order by OptionID desc) x
    where OptRuleTypeID is null
    
    select * from @t
    /*
      OptionID OptRuleTypeID
    ----------- -------------
         10      345
         20      567
         30      567
         40      567
         50      789
         60      789
    */
    
    

     

     


    --Brad (My Blog)
    • Marked as answer by KJian_ Thursday, January 6, 2011 8:17 AM
    Friday, December 31, 2010 6:07 PM