October 8, 2015 at 2:17 am
I have a procedure, that has two nested cursors with large number of variables.
Also there are some other functions calls and inserts with aggregates taking place.
When i executed this for few 20 rows, it takes few minutes, thousands of rows, it is taking hours and when lakhs its days.
please help it need to execute this code for large data.
October 8, 2015 at 2:35 am
Without seeing the code, impossible to give any specific suggestions.
In general, you probably need to:
Replace the cursors with set-based queries (and no, don't just replace with a while loop). This is likely to be the most important.
If you have scalar or multi-statement table valued functions, then either convert them to in-line table valued functions or get rid of them entirely
Make sure all queries can use indexes
Make sure the tables have indexes to support the queries.
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
October 8, 2015 at 4:39 am
I have deleted many attributes in inserting,fetching
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:<This procedure GETS CT DEVIATED COILS FROM COOL MEASURE RAW THEN
-- GETS THE RELAVANT INFO FROM HRM_STAND_RAW, THEN GETS 1 ROW PER SEGMENT USING MAX() OF ALL THE COLUMN
-- AND INSERTS IN dbo.DEFECT_CT_STAND_SEG_MAX>
-- =============================================
CREATE PROCEDURE [dbo].[All_max_stand_per_coilR2]
-- Add the parameters for the stored procedure here
AS
BEGIN
declare
@fm_coilid varchar(50),
@IDENTITY_KEY numeric(16, 0) ,@HRM_COIL_ID varchar(50) ,@HRM_SEG_NUM1 numeric(4, 0) ,@TIME_SINCE_THREAD_EN numeric(8, 2) ,@LEN_SINCE_THREAD_EN numeric(8, 2) ,@MEA_TEM_FRONT_FM_AVG numeric(8, 2) ,@SLAB_TRANSFER_SPD numeric(8, 2) ,@STATE_SPRAY_HEAD1 numeric(6, 0) ,@STATE_SPRAY_HEAD2 numeric(6, 0) ,@HEADER1_ACT_PR numeric(8, 2) ,@HEADER2_ACT_PR numeric(8, 2) ,@DSP12DESCF1ON numeric(6, 0) ,
@DSP12DESCF2ON numeric(6, 0) ,@TIME_THREAD1 numeric(8, 2) ,@LEN_THREAD1 numeric(8, 2) ,@AGC_PCFC_THER_CROWN1 numeric(8, 2) ,@PDC_PCFC_REF_ADD_FB1 numeric(8, 2) ,@AGC_PCFC_WEAR_TOT1 numeric(8, 2) ,@HGC_ACT_FR_TOT1 numeric(8, 2) ,@HGC_DIFF_FR1 numeric(8, 2) ,@HGC_SRG_ACT1 numeric(8, 2) ,@HGC_REF_OD_MAN1 numeric(8, 2) ,@ALC_ADD_LEVEL1 numeric(8, 2) ,@ACT_HX1 numeric(8, 2) ,@ACT_ROLL_WEAR1 numeric(8, 2) ,@ACT_THERM_EXP_COMP1 numeric(8, 2) ,
@WR_BEN_COMP1 numeric(8, 2) ,@WR_LIN_SPD1 numeric(8, 2) ,@WR_COOL_TEMP1 numeric(8, 2) ,@WRB_FB_LIM_REACH_MIN1 numeric(8, 2) ,@WRB_FB_LIM_REACH_MAX1 numeric(8, 2) ,@WRB_REF_DEST1 numeric(8, 2) ,@WRB_ACT_FB_MEAN1 numeric(8, 2) ,@WRB_REF_MAN1 numeric(8, 2) ,@WRB_ADD_REF_APC1 numeric(8, 2) ,@WRB_LFC_FLAT_CORR_FB1 numeric(8, 2) ,@WRB_REF_ADD_PGM1 numeric(8, 2) ,@WRB_REF_ADD_PGM_HX1 numeric(8, 2) ,@WRB_PCFC_REF_ADD_FB1 numeric(8, 2) ,@WRB_FLAT_CORR1 numeric(8, 2) ,
@FTFlag int,
@FTValue numeric(16,0)
-- this cursors iterates in setup raw
DECLARE cool_cursor CURSOR FAST_FORWARD FOR
SELECT distinct fm_coil_id
from dbo.cool_measure_raw
OPEN cool_cursor
FETCH NEXT FROM cool_cursor INTO
@fm_coilid
WHILE @@FETCH_STATUS = 0
BEGIN
-- this cursors iterates cool_measure_raw
DECLARE STAND_CURSOR CURSOR FAST_FORWARD FOR
--SELECT MEAS_STRIP_TEMP2,identity_key FROM COOL_MEASURE_RAW WHERE FM_COIL_ID = @FM_COIL_ID1
select hrm_seg_num1,max(identity_key),max(TIME_SINCE_THREAD_EN),max(LEN_SINCE_THREAD_EN),max(MEA_TEM_FRONT_FM_AVG),max(SLAB_TRANSFER_SPD),max(STATE_SPRAY_HEAD1),max(STATE_SPRAY_HEAD2),max(HEADER1_ACT_PR),max(HEADER2_ACT_PR),max(DSP12DESCF1ON),max(DSP12DESCF2ON),max(TIME_THREAD1),max(LEN_THREAD1),max(AGC_PCFC_THER_CROWN1),max(PDC_PCFC_REF_ADD_FB1),max(AGC_PCFC_WEAR_TOT1),max(HGC_ACT_FR_TOT1),max(HGC_DIFF_FR1),max(HGC_SRG_ACT1),max(HGC_REF_OD_MAN1),max(ALC_ADD_LEVEL1),max(ACT_HX1),max(ACT_ROLL_WEAR1),max(ACT_THERM_EXP_COMP1),max(WR_BEN_COMP1),max(WR_LIN_SPD1),max(WR_COOL_TEMP1),max(WRB_FB_LIM_REACH_MIN1),max(WRB_FB_LIM_REACH_MAX1),max(WRB_REF_DEST1),max(WRB_ACT_FB_MEAN1),max(WRB_REF_MAN1),max(WRB_ADD_REF_APC1),max(WRB_LFC_FLAT_CORR_FB1),max(WRB_REF_ADD_PGM1),max(WRB_REF_ADD_PGM_HX1),max(WRB_PCFC_REF_ADD_FB1),max(WRB_FLAT_CORR1),max(WRB_REF_ADD_TFFC1),max(ACT_POS_TWR_MEAN1),max(FM_ACT_HX_HOT1),max(LOO_POS_STRIP_DETECT1),max(LOO_ACT_TENSION1),max(LOO_ACT_POS1),max(SEG_ARR_STN_ACT_DS1),max(SEG_ARR_STN_ACT_OS1),max(MDC_MOT_CURR_PER1),max(MDC_MOT_TEMP1),max(ACT_TRQ_DRV_INV_FLT1),max(AGC_ACT_GAUGE_CORR1),max(SLIP_FORWARD1),max(TOT_ROLL_FRC_SRC1),max(DIF_ROLL_FRC_SRC1),max(CC_WR_CL_IN_TMP1),max(CC_WR_CF_ES1),max(CC_WR_CF_XS1),max(CC_INTER_STD_CF1),max(RGL_OIL_FLOW_TOP1),max(RGL_OIL_FLOW_BOT1),max(WATER_FLOW_TOP1),max(WATER_FLOW_BOT1),max(FM_RG_LUB_ON1),max(FM_RG_LUB_MODE1),max(FM_RG_LUB_CONT_SEL1),max(FM_RG_LUB_FAULT_INDIC1),max(FM_RG_LUB_TOP_ADJ_SW1),max(FM_RG_LUB_BOT_ADJ_SW1),max(TIME_THREAD2),max(LEN_THREAD2),max(AGC_PCFC_THER_CROWN2),max(PDC_PCFC_REF_ADD_FB2),max(AGC_PCFC_WEAR_TOT2),max(HGC_ACT_FR_TOT2),max(HGC_DIFF_FR2),max(HGC_SRG_ACT2),max(HGC_REF_OD_MAN2),max(ALC_ADD_LEVEL2),max(ACT_HX2),max(ACT_ROLL_WEAR2),max(ACT_THERM_EXP_COMP2),max(WR_BEN_COMP2),max(WR_LIN_SPD2),max(WR_COOL_TEMP2),max(WRB_FB_LIM_REACH_MIN2),max(WRB_FB_LIM_REACH_MAX2),max(WRB_REF_DEST2),max(WRB_ACT_FB_MEAN2),max(WRB_REF_MAN2),max(WRB_ADD_REF_APC2),max(WRB_LFC_FLAT_CORR_FB2),max(WRB_REF_ADD_PGM2),max(WRB_REF_ADD_PGM_HX2),max(WRB_PCFC_REF_ADD_FB2),max(WRB_FLAT_CORR2),max(WRB_REF_ADD_TFFC2),max(ACT_POS_TWR_MEAN2),max(FM_ACT_HX_HOT2),max(LOO_POS_STRIP_DETECT2),max(LOO_ACT_TENSION2),max(LOO_ACT_POS2),max(SEG_ARR_STN_ACT_DS2),max(SEG_ARR_STN_ACT_OS2),max(MDC_MOT_CURR_PER2),max(MDC_MOT_TEMP2),max(ACT_TRQ_DRV_INV_FLT2),max(AGC_ACT_GAUGE_CORR2),max(SLIP_FORWARD2))
from HRM_STAND_RAW where HRM_COIL_ID = @fm_coilid
group by HRM_SEG_NUM1 order by HRM_SEG_NUM1
OPEN STAND_CURSOR
FETCH NEXT FROM STAND_CURSOR INTO
@HRM_SEG_NUM1,@identity_key,@TIME_SINCE_THREAD_EN,@LEN_SINCE_THREAD_EN,@MEA_TEM_FRONT_FM_AVG,@SLAB_TRANSFER_SPD,@STATE_SPRAY_HEAD1,@STATE_SPRAY_HEAD2,@HEADER1_ACT_PR,@HEADER2_ACT_PR,@DSP12DESCF1ON,@DSP12DESCF2ON,@TIME_THREAD1,@LEN_THREAD1,@AGC_PCFC_THER_CROWN1,@PDC_PCFC_REF_ADD_FB1,@AGC_PCFC_WEAR_TOT1,@HGC_ACT_FR_TOT1,@HGC_DIFF_FR1,@HGC_SRG_ACT1,@HGC_REF_OD_MAN1,@ALC_ADD_LEVEL1,@ACT_HX1,@ACT_ROLL_WEAR1,@ACT_THERM_EXP_COMP1,@WR_BEN_COMP1,@WR_LIN_SPD1,@WR_COOL_TEMP1,@WRB_FB_LIM_REACH_MIN1,@WRB_FB_LIM_REACH_MAX1,@WRB_REF_DEST1,@WRB_ACT_FB_MEAN1,@WRB_REF_MAN1,@WRB_ADD_REF_APC1,@WRB_LFC_FLAT_CORR_FB1,@WRB_REF_ADD_PGM1,@WRB_REF_ADD_PGM_HX1,@WRB_PCFC_REF_ADD_FB1,@WRB_FLAT_CORR1,@WRB_REF_ADD_TFFC1,@ACT_POS_TWR_MEAN1,@FM_ACT_HX_HOT1,@LOO_POS_STRIP_DETECT1,@LOO_ACT_TENSION1,@LOO_ACT_POS1,@SEG_ARR_STN_ACT_DS1,@SEG_ARR_STN_ACT_OS1,@MDC_MOT_CURR_PER1,@MDC_MOT_TEMP1,@ACT_TRQ_DRV_INV_FLT1,@AGC_ACT_GAUGE_CORR1,@SLIP_FORWARD1,@TOT_ROLL_FRC_SRC1,@DIF_ROLL_FRC_SRC1,@CC_WR_CL_IN_TMP1,@CC_WR_CF_ES1,@CC_WR_CF_XS1,@CC_INTER_STD_CF1,@RGL_OIL_FLOW_TOP1,@RGL_OIL_FLOW_BOT1,@WATER_FLOW_TOP1,
WHILE @@FETCH_STATUS = 0
BEGIN
set @FTValue = dbo.FTcomparison(@MEA_EXIT_STRIP_TEMP_P1,@MEA_EXIT_STRIP_TEMP_P2)
set @FTFlag = dbo.returnFlagForTemp(@FTValue,@fm_coilid,'F')
insert into DEFECT_CT_STAND_SEG_MAX values(@identity_key,@fm_coilid,@HRM_SEG_NUM1,@TIME_SINCE_THREAD_EN,@LEN_SINCE_THREAD_EN,@MEA_TEM_FRONT_FM_AVG,@SLAB_TRANSFER_SPD,@STATE_SPRAY_HEAD1,@STATE_SPRAY_HEAD2,@HEADER1_ACT_PR,@HEADER2_ACT_PR,@DSP12DESCF1ON,@DSP12DESCF2ON,@TIME_THREAD1,@LEN_THREAD1,@AGC_PCFC_THER_CROWN1,@PDC_PCFC_REF_ADD_FB1,@AGC_PCFC_WEAR_TOT1,@HGC_ACT_FR_TOT1,@HGC_DIFF_FR1,@HGC_SRG_ACT1,@HGC_REF_OD_MAN1,@ALC_ADD_LEVEL1,@ACT_HX1,@ACT_ROLL_WEAR1,@ACT_THERM_EXP_COMP1,@WR_BEN_COMP1,@WR_LIN_SPD1,@WR_COOL_TEMP1,@WRB_FB_LIM_REACH_MIN1,@WRB_FB_LIM_REACH_MAX1,@WRB_REF_DEST1,@WRB_ACT_FB_MEAN1,@WRB_REF_MAN1,@WRB_ADD_REF_APC1,@WRB_LFC_FLAT_CORR_FB1,@WRB_REF_ADD_PGM1,@WRB_REF_ADD_PGM_HX1
END
CLOSE stand_cursor
DEALLOCATE stand_cursor
FETCH NEXT FROM cool_cursor INTO @fm_coilid
END
CLOSE cool_cursor
DEALLOCATE cool_cursor
END
October 8, 2015 at 4:56 am
As good an example of cowboy coding as I've ever seen. It's probably a half-day of work for a competent developer to replace this pile of doom with properly-written, scalable, tested code.
If you can't tell what to do from looking at it, do you have another dev who could take it on? Alternatively set up some sample data scripts as Gail suggested, and folks here will offer you some suggestions.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2015 at 5:27 am
Can't make specific, useful suggestions without seeing the definitions of FTcomparison and returnFlagForTemp, but in general you need to rip those cursors out, convert them and the function calls to set-based processes, instead of running row by row.
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
October 8, 2015 at 7:10 am
In general, look up the INSERT ... SELECT syntax. That's your buddy when dealing with stuff like this. Your current structure, as everyone else has already said, is broken.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 12, 2015 at 12:28 am
Dear all,
sorry to post the whole code.
1) I have to take max of all attributes in HRM_STAND_RAW
for each coilid in cool_measure_raw
2) FTValue returns the highest value amongst the two values passed
3) this value if passed to returnFlag For Temp to get a character as H,L
4) finally all the max values,ftvalue and the flag is inserted in DEFECT_CT_STAND_SEG_MAX
here, both the tables involved have 500 -700 recored per coilid
October 12, 2015 at 2:42 am
If you want people to help you, you're going to have to post more than that. Start with table definitions, sample data (in the form of insert statements) and your expected results.
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
October 12, 2015 at 11:24 am
Like Grant said.
Do not use the cursor but put the data directly into the table with a select.
October 12, 2015 at 3:02 pm
I don't have time to line up all the columns, but here's the general format of what you need. I don't see where the computed @FT* values are being used in the INSERT, but if you need to calc those after the INSERT for some reason, you can use an OUTPUT statement to dump the INSERTed rows to a temp table and compute them.
insert into DEFECT_CT_STAND_SEG_MAX
select max(identity_key), hrm_coil_id, hrm_seg_num1, max(TIME_SINCE_THREAD_EN), max(LEN_SINCE_THREAD_EN),
max(MEA_TEM_FRONT_FM_AVG), max(SLAB_TRANSFER_SPD), max(STATE_SPRAY_HEAD1), max(STATE_SPRAY_HEAD2), max(HEADER1_ACT_PR), max(HEADER2_ACT_PR), ...rest_of_columns_to_match_whats_needed_for_insert
from HRM_STAND_RAW
where HRM_COIL_ID in ( SELECT distinct fm_coil_id from dbo.cool_measure_raw )
group by HRM_SEG_NUM1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 14, 2015 at 3:22 am
thank you very much, i shall execute it tonight.
by the way ftvalue is there in insert, never mind, i can update it later
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply