December 11, 2015 at 8:13 am
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
December 11, 2015 at 8:32 am
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.
December 11, 2015 at 8:34 am
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
December 11, 2015 at 9:41 am
Get rid of your cursor and use cross apply to iterate over provider_id,contract_id run_id etc..
Regards
Shrikant Kulkarni
December 11, 2015 at 10:28 am
Thanks Steve, John and Shrikant.
Very helpful info!
December 14, 2015 at 6:38 pm
chaaarliship (12/13/2015)
helloyou 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
----------------------------
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
Change is inevitable... Change for the better is not.
December 15, 2015 at 7:52 am
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