Optimizing a table created at runtime

  • Hi,

    I am optimizing the script below and have a question. As you will see they are creating the table (zcap_workspace) below.

    I added the primary key in an effort to speed things up. I am assuming that was the correct thing to do.

    Should I also add indexes to this table? Or will it slow down the inserts?

    Any other performance improvements I can make?

    Set NoCount On

    DECLARE @CAP_METHOD_NUM_MEMint

    SELECT @CAP_METHOD_NUM_MEM = population_select_method_id

    from dbo.population_select_method

    where population_select_method_nm = 'Number of Members Assigned to PCP'

    DECLARE @CAP_METHOD_MEM_WITH_BEN_CON int

    SELECT @CAP_METHOD_MEM_WITH_BEN_CON = population_select_method_id

    from dbo.population_select_method

    where population_select_method_nm = 'All Members With Benefit Contract'

    DECLARE @CAP_METHOD_PCPint

    SELECT @CAP_METHOD_PCP = population_select_method_id

    from dbo.population_select_method

    where population_select_method_nm = 'All Members assigned to Provider'

    DECLARE @CAP_METHOD_PCP_IN_NETint

    SELECT @CAP_METHOD_PCP_IN_NET = population_select_method_id

    from population_select_method

    where population_select_method_nm = 'All Members whose PCP is in Network'

    if objectproperty(object_id('dbo.zcap_workspace'), 'IsTable') = 1

    drop table zcap_workspace

    Create Table zcap_workspace (

    cap_workspace_id int IDENTITY (1, 1) NOT NULL,

    cap_run_id int NULL,

    test_date datetime NULL,

    provider_id int NULL,

    payor_id int NULL,

    benefitplan_id int NULL,

    medical_group_id int NULL,

    network_id int NULL,

    contract_id int NULL,

    cap_schedule_id int NULL,

    vendor_id int NULL,

    account_profile_id int NULL,

    management_percent float NULL,

    management_fee_before_withhold tinyint Null,

    withhold_percent float NULL,

    benefit_contract_type_id int NULL,

    retro_member_id int NULL,

    elig_cap_adjustment_id int NULL,

    prov_cap_adjustment_id int NULL,

    return_status int NULL,

    num_pcp_members_amount money NULL,

    population_select_method_id int NULL,

    processed tinyint Not Null,

    -- The following entries are only filled in during test-date retroactive

    -- processing.

    retro_original_cap_run_id int NULL, -- Original cap run this refers to.

    retro_adjusted_entry_id int NULL, -- The entry the adjustment refers to.

    retro_period_start datetime NULL, -- The retro_begin_date.

    retro_period_end datetime NULL, -- The retro_end_date.

    retro_eligibility_id int NULL,

    --added by RGC 12/11/2015

    CONSTRAINT cap_workspace_id_pk PRIMARY KEY (cap_workspace_id)

    )

    Exec dbo.usp_cap_L00_get_contracts

    @cap_run_id,

    @cap_run_provider_id,

    @cap_run_payor_id,

    @cap_run_contract_id,

    @test_date

    Declare CapWorkspace_cur Cursor Static For

    Select

    cap_workspace_id,

    provider_id,

    contract_id,

    population_select_method_id

    From

    dbo.zcap_workspace

    Order By

    provider_id,

    contract_id Desc,

    payor_id Desc

    Open CapWorkspace_cur

    Declare

    @Return_Value int,

    @Member_Count int,

    @AdjustmentsMade int

    Select

    @NumContracts = 0,

    @DuplicateCount = 0,

    @TotalMembers = 0

    Declare

    @cap_workspace_id int,

    @provider_id int,

    @contract_id int,

    @population_select_method_id int

    Fetch CapWorkspace_cur Into

    @cap_workspace_id,

    @provider_id,

    @contract_id,

    @population_select_method_id

    While @@FETCH_STATUS = 0

    Begin

    If Exists (Select *

    From

    dbo.cap_per_member AS cpm

    Inner Join dbo.cap_run AS cr

    On cpm.original_cap_run_id = cr.cap_run_id

    And cr.begin_date = @begin_date

    And cpm.contract_id = @contract_id

    And cpm.provider_id = @provider_id )

    Begin

    --exec usp_cap_aaa_log @cap_run_id, '. Duplicate contract -', @iValue = @contract_id

    --exec usp_cap_aaa_log @cap_run_id, '. Provider -', @iValue = @provider_id

    --exec usp_cap_aaa_log @cap_run_id, '. Payor -', @iValue = @cap_run_payor_id

    Goto Next_Provider

    End

    If @population_select_method_id = @CAP_METHOD_NUM_MEM

    exec @member_count = dbo.usp_cap_L20_number_pcp_members @cap_workspace_id

    Else If @population_select_method_id = @CAP_METHOD_MEM_WITH_BEN_CON

    exec @member_count = dbo.usp_cap_L20_mem_with_ben_con @cap_workspace_id

    Else If @population_select_method_id = @CAP_METHOD_PCP_IN_NET

    exec @member_count = dbo.usp_cap_L20_members_pcp_in_net @cap_workspace_id

    Else If @population_select_method_id = @CAP_METHOD_PCP

    exec @member_count = dbo.usp_cap_L20_members_pcp @cap_workspace_id

    Else

    Begin

    --exec usp_cap_aaa_log @cap_run_id,'. Assigned contract method has not been implemented.'

    Goto Next_Provider

    End

    Select @NumContracts = @NumContracts + 1

    --exec usp_cap_aaa_log @cap_run_id, '. Members Capitated = ', @member_count

    If @member_count > 0

    Select @TotalMembers = @TotalMembers + IsNull(@member_count,0)

    Next_Provider:

    Fetch CapWorkspace_cur Into

    @cap_workspace_id,

    @provider_id,

    @contract_id,

    @population_select_method_id

    End -- While @@FETCH_STATUS = 0

    Close CapWorkspace_cur

    Deallocate CapWorkspace_cur

    -- The processed flag is used by retro to figure out which contracts have been looked at in the workspace table.

    -- At this point all of the contracts in the workspace table are current capitation contracts and can be marked

    -- as processed.

    Update dbo.zcap_workspace

    Set processed = 1

    Declare @map_stored_procedure varchar(50)

    -- Map Sequence 3210

    -- This map sequence is used to process retro outside of the contract

    Select @map_stored_procedure = null

    Select @map_stored_procedure = stored_procedure From dbo.client_map (NOLOCK)

    Where map_sequence_number = 3210

    If @map_stored_procedure is not null

    BEGIN

    --exec usp_cap_aaa_log @cap_run_id,'. Starting retro...'

    exec @AdjustmentsMade = @map_stored_procedure @cap_run_id

    Select @TotalMembers = @TotalMembers + IsNull(@AdjustmentsMade,0)

    --exec usp_cap_aaa_log @cap_run_id,'. Number of retro adjustments = ',@ivalue = @AdjustmentsMade

    END

    -- Map Sequence 3110

    -- This one is used for computing all the management fees, withholdings and creating

    -- the cap_run_detail records. This is done when the 3100 compute-per-contract method

    -- is not used.

    Select @map_stored_procedure = null

    Select @map_stored_procedure = stored_procedure From client_map (NOLOCK)

    Where map_sequence_number = 3110

    If @map_stored_procedure is not null

    BEGIN

    Exec dbo.usp_cap_L00_get_contracts

    @cap_run_id,

    @cap_run_provider_id,

    @cap_run_payor_id,

    @cap_run_contract_id,

    @test_date

    --exec usp_cap_aaa_log @cap_run_id,'. Computing totals...'

    exec @map_stored_procedure @cap_run_id

    --exec usp_cap_aaa_log @cap_run_id,'. Totals done.'

    END

    -- Summarize demographic breakdown.

    Insert Into dbo.cap_run_breakdown (

    cap_run_detail_id,

    cap_schedule_detail_id,

    cap_run_id,

    amount,

    cap_run_count,

    contract_id,

    payor_id,

    provider_id )

    Select

    cap_per_member.cap_run_detail_id,

    cap_per_member.cap_schedule_detail_id,

    @cap_run_id,

    Sum(cap_per_member.amount) AS SumOfamount,

    Count(cap_per_member.amount) AS CountOfamount,

    cap_run_detail.contract_id,

    cap_run_detail.payor_id,

    cap_run_detail.provider_id

    From

    dbo.cap_per_member

    Inner Join dbo.cap_run_detail

    On cap_per_member.cap_run_detail_id = cap_run_detail.cap_run_detail_id

    Where

    cap_per_member.cap_run_id = @cap_run_id

    Group By

    cap_per_member.cap_run_detail_id,

    cap_per_member.cap_schedule_detail_id,

    cap_run_detail.contract_id,

    cap_run_detail.payor_id,

    cap_run_detail.provider_id

    --exec usp_cap_aaa_log @cap_run_id,'. Total Capitated (with adjustments) = ',@iValue = @TotalMembers

  • Does the table exist and is used for a long time or just a bit of work?

    Short answer, add indexes if they speed up queries more than the time things slow down to create the index.

  • Why are you creating the table at runtime? Does it change from one run to the next? Indexes will slow down inserts, yes, although not necessarily by a noticeable amount. You'll need to test and see whether it's best to drop them before and recreate them after. By far the best performance you can make will almost certainly be to get rid of that WHILE loop. Without knowing what those stored procedures do, though, I can't advise you how to do that.

    John

  • Get rid of your cursor and use cross apply to iterate over provider_id,contract_id run_id etc..

    Regards
    Shrikant Kulkarni

  • Thanks Steve, John and Shrikant.

    Very helpful info!

  • chaaarliship (12/13/2015)


    hello

    you are Optimizing a table created at runtime with help of html . this process is very easy and simple , to learn html and created a table

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

    programming languages

    Jeez... if you're going to SPAM us, at least take a little time to put your best foot forward.

    Shifting gears, give me one example of how HTML can be used to create a simple 3 column table SQL Server Table with a primary key. Think of it as a chance to redeem yourself. 😉

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

  • lol...

    *crickets chirp*

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

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