Cursor removal question.
-
I inherited a stored procedure that unions 4 tables and inserts the results into another table. The result of which is about 1 million rows. It uses a cursor to do this and it takes forever to process. The cursor does a commit every 5,000 rows. I need to modify this to get rid of the cursor but keep the commit every 5,000 rows. Any suggestions.
-
post your query...and lets see how its structured, is the validating on a row bases prior to inserting or something?
-
Good advice, it's a bit hard to tune/rewrtie something if you don't know what's in it
A lesson for all seeking advice I think.
-
Sorry I should have know better. Here it is.
COMMIT TRANSACTION
BEGIN TRANSACTION
DECLARE @ls_patientid varchar(32), @ldt_fromdate smalldatetime, @ls_claimno varchar(42),
@li_stayid integer, @ls_cpt4 char(5), @ls_gcn char(5), @ls_diagnosis1 char(6),
@ls_diagnosis2 char(6), @ls_diagnosis3 char(6), @ls_diagnosis4 char(6),
@ls_providerid varchar(16), @li_linenum integer, @li_runid integer,
@li_quantity integer, @ln_serviceamt1 numeric(12,2), @ln_serviceamt2 numeric (12,2),
@ln_serviceamt3 numeric(12,2), @ls_case_type char(6), @ls_dx1_discharge char(6),
@ls_dx2_discharge char(6), @ls_dx3_discharge char(6), @ls_dx4_discharge char(6),
@ls_cpt4_primary char(5), @ls_attending_md_id varchar(16), @li_counter integer,
@ls_facility_id varchar(16),
@ls_status char(2),
@ls_statusreason char(2),
@ls_serviceplace char(2),
@ls_user_proccode varchar(10),
@ldt_paiddate smalldatetime,
@ls_admissiontype char(2),
@ls_authnumber varchar(20),
@ls_icd9procedure1 varchar(10),
@ls_icd9procedure2 varchar(10),
@ln_copaydeductible numeric(12,2),
@li_patientstatus smallint,
@ls_drg char(3),
@ls_ndc char(11),
@ls_modifier char(2),
@ls_modifier2 char(2),
@ls_servicetype char(2),
@ldt_admission smalldatetime,
@ldt_discharge smalldatetime,
@ls_mgmtproviderid varchar(16),
@ldt_fromdate_actual smalldatetime,
@ldt_thrudate smalldatetime
TRUNCATE TABLE dmv_service
SELECT @li_counter = 0
DECLARE pm_diseases_cursor CURSOR FOR
select distinct
s.patientid, s.fromdate, s.claimno, s.stayid, s.cpt4, s.gcn, s.diagnosis1, s.diagnosis2, s.diagnosis3, s.diagnosis4,
s.providerid, s.linenum, s.runid,s.quantity, s.serviceamt1, s.serviceamt2, s.serviceamt3 ,NULL as case_type,
NULL as dx1_pm, NULL as dx2_pm, NULL as dx3_pm, NULL as dx4_pm, NULL as cpt4_pm,NULL as attending_md_id,
s.facility_id,s.status,s.statusreason, s.serviceplace,s.user_proccode,s.paiddate,s.admissiontype,s.authnumber,
s.icd9procedure1,s.icd9procedure2,s.copaydeductible,s.patientstatus,s.drg,s.ndc,s.modifier,s.modifier2,
s.servicetype,s.admission,s.discharge,s.mgmtproviderid,s.fromdate as fromdate_actual,s.thrudate
from service s
where (s.stayid is null or s.stayid = 0)
UNION
select distinct s.patientid, s.fromdate, s.claimno, s.stayid, s.cpt4, s.gcn, s.diagnosis1, s.diagnosis2,
s.diagnosis3, s.diagnosis4, s.providerid, s.linenum, s.runid, s.quantity, s.serviceamt1,
s.serviceamt2,s.serviceamt3, NULL, NULL as dx1_pm, NULL as dx2_pm, NULL as dx3_pm,
NULL as dx4_pm, NULL as cpt4_pm, NULL as attending_md_id,
s.facility_id,s.status, s.statusreason,s.serviceplace,s.user_proccode,s.paiddate,s.admissiontype,
s.authnumber,s.icd9procedure1, s.icd9procedure2,s.copaydeductible,s.patientstatus,
s.drg,s.ndc,s.modifier,s.modifier2,s.servicetype,s.admission,s.discharge,
s.mgmtproviderid,s.fromdate as fromdate_actual,s.thrudate
from nonadj_service s
where (s.stayid is null or s.stayid = 0)
OPEN pm_diseases_cursor
FETCH NEXT FROM pm_diseases_cursor
INTO @ls_patientid, @ldt_fromdate, @ls_claimno, @li_stayid, @ls_cpt4, @ls_gcn,
@ls_diagnosis1, @ls_diagnosis2, @ls_diagnosis3, @ls_diagnosis4, @ls_providerid,
@li_linenum, @li_runid, @li_quantity, @ln_serviceamt1, @ln_serviceamt2,
@ln_serviceamt3, @ls_case_type, @ls_dx1_discharge, @ls_dx2_discharge,
@ls_dx3_discharge, @ls_dx4_discharge, @ls_cpt4_primary, @ls_attending_md_id,
@ls_facility_id,@ls_status,@ls_statusreason,@ls_serviceplace, @ls_user_proccode,
@ldt_paiddate,@ls_admissiontype,@ls_authnumber,@ls_icd9procedure1,
@ls_icd9procedure2, @ln_copaydeductible,@li_patientstatus,@ls_drg,@ls_ndc,
@ls_modifier,@ls_modifier2,@ls_servicetype,@ldt_admission,@ldt_discharge,
@ls_mgmtproviderid, @ldt_fromdate_actual,@ldt_thrudate
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO DMV_SERVICE (patientid, fromdate, claimno, stayid,
cpt4, gcn, diagnosis1, diagnosis2, diagnosis3, diagnosis4, providerid,
linenum, runid, quantity, serviceamt1, serviceamt2, serviceamt3, case_type,
dx1_discharge, dx2_discharge, dx3_discharge, dx4_discharge, cpt4_primary,
attending_md_id,facility_id,status,statusreason,serviceplace,user_proccode,paiddate,
admissiontype,authnumber,icd9procedure1,icd9procedure2,copaydeductible,
patientstatus,drg,ndc,modifier,modifier2,servicetype,admission,
discharge, mgmtproviderid,fromdate_actual,thrudate
 
VALUES (@ls_patientid, @ldt_fromdate, @ls_claimno, @li_stayid, @ls_cpt4,
@ls_gcn, @ls_diagnosis1,
@ls_diagnosis2, @ls_diagnosis3, @ls_diagnosis4, @ls_providerid, @li_linenum,
@li_runid, @li_quantity, @ln_serviceamt1, @ln_serviceamt2,@ln_serviceamt3,
@ls_case_type, @ls_dx1_discharge, @ls_dx2_discharge, @ls_dx3_discharge,
@ls_dx4_discharge, @ls_cpt4_primary, @ls_attending_md_id,
@ls_facility_id,@ls_status,@ls_statusreason,@ls_serviceplace, @ls_user_proccode,
@ldt_paiddate,@ls_admissiontype,@ls_authnumber, @ls_icd9procedure1,
@ls_icd9procedure2, @ln_copaydeductible,@li_patientstatus,
@ls_drg,@ls_ndc,@ls_modifier, @ls_modifier2,@ls_servicetype,@ldt_admission,
@ldt_discharge,@ls_mgmtproviderid,@ldt_fromdate_actual,@ldt_thrudate
 
SELECT @li_counter = @li_counter +1
IF @li_counter >= 5000
BEGIN
COMMIT TRANSACTION
BEGIN TRANSACTION
SELECT @li_counter = 0
END
FETCH NEXT FROM pm_diseases_cursor
INTO @ls_patientid, @ldt_fromdate, @ls_claimno, @li_stayid, @ls_cpt4, @ls_gcn,
@ls_diagnosis1, @ls_diagnosis2, @ls_diagnosis3, @ls_diagnosis4, @ls_providerid,
@li_linenum, @li_runid, @li_quantity, @ln_serviceamt1, @ln_serviceamt2,
@ln_serviceamt3, @ls_case_type, @ls_dx1_discharge, @ls_dx2_discharge,
@ls_dx3_discharge, @ls_dx4_discharge, @ls_cpt4_primary, @ls_attending_md_id,
@ls_facility_id,@ls_status,@ls_statusreason,@ls_serviceplace, @ls_user_proccode,
@ldt_paiddate,@ls_admissiontype,@ls_authnumber,@ls_icd9procedure1, @ls_icd9procedure2,
@ln_copaydeductible,@li_patientstatus,@ls_drg,@ls_ndc,@ls_modifier,
@ls_modifier2,@ls_servicetype,@ldt_admission,@ldt_discharge,
@ls_mgmtproviderid, @ldt_fromdate_actual, @ldt_thrudate
END
CLOSE pm_diseases_cursor
DEALLOCATE pm_diseases_cursor
COMMIT TRANSACTION
-
It looks like this is already set up to use a WHILE loop.
WHILE @li_counter <= @previous_li_counter [you may have to drop the setting of @il_counter to zero and make it one and at the same time set @previous_il_counter to zero].
I am assuming @ls_patientid is unique. Just keep getting the next one by SELECT @ls_patientid = (SELECT ls_patientid FROM nonadj_services WHERE ls_patientid > @ls_patientid). This should go in the same postion where you are incrementing @il_counter.
Good luck.
I wasn't born stupid - I had to study.
-
I want to remove the cursor and do a insert from the unions and keep the commit every 5,000 rows.
-
I only glanced at your T-SQL, but I think what you want to do is the following. HTH!
-Pick an ID that is unique for each insert, say PatientID, and then do your inserts for every 5000 rows.
Then use code like the following:
declare @MaxPatientID
declare @loopPatientID int
declare @increment int
set @MaxPatientID = (select max(patientid) from service)
set @increment = 5000
set @loopPatientID = @increment
while @loopPatientID < @MaxPatientID
BEGIN
BEGIN TRANSACTION
-- put your insert statement here ---
set @loopPatientID = @loopPatientID + @increment
if @@error = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply