| Author |
Topic |
|
nvakeel
Yak Posting Veteran
52 Posts |
 Posted - 2008-01-30 : 11:12:35
|
| Hello All,I have two procedures being run one after the other. when I run proc1 it runs for about 15 min.Now the proc2 is dependent on proc1, when I run proc2 it runs for 45 min. If I run both the proc's simultaneously through .net code it takes more than 1 hour. Can anyone of you tell me where would be the problem.Thanks in Advance. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2008-01-30 : 11:35:23
|
| you have given us nothing to go on. we will need ddl of tables and stored procs, sample data, etc. in order to help.-ec |
 |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2008-01-30 : 11:53:17
|
| I have something that doesn't work. Why?;) |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-01-30 : 18:49:22
|
| I also have one thing in my mind which is not working. Could you help me why? lol |
|
|
|
jdaman
Constraint Violating Yak Guru
354 Posts |
Posted - 2008-01-30 : 19:06:13
|
| nvakeel,all kidding aside, if you will provide the information eyechart requested we would be happy to give suggestions. |
|
|
|
nvakeel
Yak Posting Veteran
52 Posts |
Posted - 2008-02-01 : 15:52:40
|
| CREATE PROCEDURE [dbo].[sp_prnj615m_Populate_Intran] (@job_inst_id VARCHAR(100) , @date varchar(20) , ---Date format: MM/DD/YYYY @password VARCHAR(8) )ASBEGIN IF (LEFT(@date,2) > 0 AND LEFT(@date,2) < 13 AND SUBSTRING(@date,3,2) > 0 AND SUBSTRING(@date,3,2) < 32 AND SUBSTRING(@date,5,4) = 20) OR LEN(@password) <= 8 BEGIN DELETE FROM MPAS_TMP.DBO.TMP_POP_PRNA_SSN WHERE JOB_INST_ID =@JOB_INST_ID INSERT INTO MPAS_TMP.DBO.TMP_POP_PRNA_SSN SELECT DISTINCT SS_NBR , @JOB_INST_ID FROM LOB_PRSN LP , LOB_RCP_ACCT LR , LOB_BEN_ACCT LB WHERE LP.LOB_PRSN_ID = LR.LOB_PRSN_ID AND LB.LOB_BEN_ACCT_ID = LR.LOB_BEN_ACCT_ID --AND LR.RCD_CD = '1' --AND SS_NBR = '001140899' ORDER BY SS_NBR DELETE FROM MPAS_TMP.DBO.TMP_POP_PRNA WHERE JOB_INST_ID = @JOB_INST_ID EXEC SP_POP_PRNA @JOB_INST_ID INSERT INTO mpas_tmp.dbo.TMP_PRNJ615M_INTRAN (JOB_INST_ID, SYSTEM , LOCATION , SSN , MULT_RECORD_CODE_THIS_SSN , STATUS_CD , STATUS_DATE , LAST_NAME , FIRST_NAME , MIDDLE_NAME , RETIRE_TYPE , OPTION_RM , RETIRE_DATE , B_DATE , SEX , PRIOR_SERV_MTHS , MEMB_SERV_MTHS , SSN_SUFIX , REL_TYPE , OLD_MIL_MTHS , NEW_MIL_MTHS , PENS_RES_PMTH , ADD_PENS_RES_PMTH , ANN_RES_PMTH , VOL_ANN_RES_PMTH , TSA_ANN_PMTH , GROSS , MAX_MTH_ALLOW , PENSN_RES_OPT , ADD_PENSN_RES_OPT , SUPPL_PMTH , CPI_AMT_PMTH , FED_TAX_FLAT , ST_WHTAX_PMTH , ANN_MIN_PMTH , NET_MTH_CHK , PART_PAY_FLAG , RETRO_MTHS , RETRO_DAYS , JOB_CODE , SICK_LEAVE_MTHS , RET_DTH_DATE , BEN_DTH_DATE , NORM_ANN_FACTOR , VOL_ANN_FACTOR , TSA_ANN_FACTOR , PENS_REDUCE_FACTOR , NORM_OPT_FACTOR , VOL_OPT_FACTOR , TSA_OPT_FACTOR , TSA_ANN_FLAG , ADDR_LINE_1 , ADDR_LINE_2 , ADDR_STATE , ADDR_ZIP , INSUR_CARR_CODE , INSUR_COV_CODE , VOL_DEDUCT_A , DEDUCT_CODE_A , VOL_DEDUCT_B , DEDUCT_CODE_B , VOL_DEDUCT_C , DEDUCT_CODE_C , VOL_DEDUCT_D , DEDUCT_CODE_D , VOL_DEDUCT_E , DEDUCT_CODE_E , SAL_MNTH1 , SAL_MNTH2 , SAL_MNTH3 , SAL_MNTH4 , SAL_MNTH5 , BEN_SSN , BEN_BD , BEN_SEX , BEN_REL_CODE , BEN_RET_ALLOW , YGST_CHILD_BDATE , YGST_CHILD_SEX , JAN_CONTRIB , FEB_CONTRIB , MAR_CONTRIB , APR_CONTRIB , MAY_CONTRIB , JUN_CONTRIB , JUL_CONTRIB , AUG_CONTRIB , SEP_CONTRIB , OCT_CONTRIB , NOV_CONTRIB , DEC_CONTRIB , JUL_PRIOR_CONTR , AUG_PRIOR_CONTR , SEP_PRIOR_CONTR , OCT_PRIOR_CONTR , NOV_PRIOR_CONTR , DEC_PRIOR_CONTR , RET_ADDR_LINE1 , RET_ADDR_LINE2 , RET_ADDR_STATE , RET_ADDR_ZIP , MTH_10_FLG , SSN_FLAG , NO_CALC_FLAG , LEGIS_PLAN_TYPE , WORKMAN_COMP , SS_SURVIVE_BEN , MTHS_SERV_PRIOR , MTHS_SERV_AFTER , DFI_ACCT_NUM , TRANSIT_ROUT_NUM , TRN_CHK , EFT_FLAG , RETIRE_NUM , MULT_PAY_FLAG , MULT_PAY_SSN , TTL_FIX_TERM_AMT , MTHLY_FIX_TERM_AMT , MISC_CODE , CPI_CODE , CPI_FACTOR_RETIRE , EMP_BILL_PCNT , ANUITY_PRSP_BNFT , PENSION_PRSP_BNFT , WORK_COMP_FLAG , VOL_OPT , TSA_OPT , WORK_COMP_MTHLY , SSN_R , ANNL_MIN_FLAG , NORM_RES_OPT , VOL_RES_OPT , TSA_RES_OPT , B_NOR_AF , B_VOL_AF , B_TSA_AF , BEN_FNAME , BEN_MNAME , BEN_LNAME , DATE_ADDED , POST_DATE , STOP_CHK_FLAG , ELIG_MOS , FED_EXEMP , MARITAL_ST , FED_EX_TAX , FED_WHTAX_PMTH , PRE_EXIST_DIS , MAX_PENS_MO , MAX_ADDL_PENS_MO , MAX_ANN_MO , MAX_VOL_MO , MAX_TSA_MO , HOSP_SHR_CODE , PLAN_SELECT , MEMB_SERV_AFTER , PRIOR_SERV_AFTER , OLD_MIL_AFTER , NEW_MIL_AFTER , SICK_LVE_AFTER , CPI_ADJUST_FACTOR , BIF_REDCT_FCTR , BIF_PENS_RSRV , BIF_ADDL_PENS , BIF_ANN_RERV , BIF_VOL_ANN , BIF_TSA_ANN , BIF_SUPL , BIF_COST_LIV , BIF_ANUAL_MIN , BIF_MAX_MTHLY , BIF_CPI_ADJ_FCT , PEN_TX_PCT , NON_RELEASE_FLG , EFT_TRANS_CODE , VISION_INS_AMT_DENTAL , VISION_COV_CODE , DRUG_INS_AMT , DRUG_COV , VOL_DEDUCT_F , DEDUCT_CODE_F , VOL_DEDUCT_G , DEDUCT_CODE_G , VOL_DEDUCT_H , DEDUCT_CODE_H , VOL_DEDUCT_I , DEDUCT_CODE_I , VOL_DEDUCT_J , DEDUCT_CODE_J , GROSS_BASE_WAGES , EMP_PICKUP , LAD_405 , EP_BAL_REMAIN , CRY_OVR_TAXFRE , BOND_DED_AMT , TRANSFER_DATE , MONTHLY_RECOVERY , RETMNT_INCENT_FLAG , RETMNT_INCENT_SERV , RETMNT_INCENT_CASH_PAY , NORM_CONTRIB , NORM_INT , VOL_CONTRIB , VOL_INT , TSA_CONTRIB , TSA_INT , MIL_CONTRIB , MIL_INT , CONTRIB_DEFIC , TTL_ACC_CONTRIB , TTL_NTAX_CONTRIB , PRES_VAL_OPT , PART_PAY_AMT , AVG_3YR_SAL , SAL_1 , SAL_2 , SAL_3 , SAL_4 , SAL_5 , ANNL_EARN_LIMIT , PLAN_SEL_DATE , RET_ABOL_FLAG , /*RET_INCN_MNTH_PRIR , RET_INCN_MNTH_AFTR ,*/ SSIL , ADV_FLAG , MED_INSUR_AMT , TOT_SV , SER_A_TOT , bif_cpi , C_2 , C_3 , YTD , ADDR_CITY , RET_ADDR_CITY , RCP_ID ,RET_INCN_MNTH_PRIR , RET_INCN_MNTH_AFTR) SELECT @job_inst_id AS JOB_INST_ID , SYS AS SYSTEM , LOC AS LOCATION , SSN AS SSN , RCD_CD as MULT_RECORD_CODE_THIS_SSN , S AS STATUS_CD , SD AS STATUS_DATE , LNAME AS LAST_NAME , FNAME AS FIRST_NAME , MI as MIDDLE_NAME , TY AS RETIRE_TYPE , NOP AS OPTION_RM , CONVERT(VARCHAR,RD,112) AS RETIRE_DATE , BD AS B_DATE , SEX AS SEX , ISNULL(SER_PR,0) AS PRIOR_SERV_MTHS , ISNULL(SER_MEM,0) AS MEMB_SERV_MTHS , ISNULL(SSN_SUFIX,'') As SSN_SUFIX , SPACE(1) as REL_TYPE , ISNULL(SER_OM,0) AS OLD_MIL_MTHS , ISNULL(SER_NM,0) AS NEW_MIL_MTHS , ISNULL(PR,0.0) AS PENS_RES_PMTH , ISNULL(AP,0.0) AS ADD_PENS_RES_PMTH , ISNULL(AR,0.0) AS ANN_RES_PMTH , ISNULL(VA,0.0) AS VOL_ANN_RES_PMTH , ISNULL(TA,0.0) AS TSA_ANN_PMTH , ISNULL(GROSS,0.0) AS GROSS , ISNULL(MX_M,0.0) AS MAX_MTH_ALLOW , space(11) as PENSN_RES_OPT , space(11) as ADD_PENSN_RES_OPT , ISNULL(SL,0.0) AS SUPPL_PMTH , ISNULL(CL,0.0) AS CPI_AMT_PMTH , ISNULL(F_FLAT,0) As FED_TAX_FLAT , ISNULL(SWT,0) AS ST_WHTAX_PMTH , ISNULL(MN,0.0) AS ANN_MIN_PMTH , space(11) AS NET_MTH_CHK , space(1) AS PART_PAY_FLAG , space(2) AS RETRO_MTHS , space(2) AS RETRO_DAYS , ISNULL(JOB,'') AS JOB_CODE , ISNULL(SER_SL,0) AS SICK_LEAVE_MTHS , ISNULL(DD,'') AS RET_DTH_DATE , isnull(BEN_DD,'') AS BEN_DTH_DATE , ISNULL(NAF,0.0) AS NORM_ANN_FACTOR , ISNULL(VAF,0.0) AS VOL_ANN_FACTOR , ISNULL(TAF,0.0) AS TSA_ANN_FACTOR , RED_F AS PENS_REDUCE_FACTOR , ISNULL(NOF,0.0) AS NORM_OPT_FACTOR , ISNULL(VOF,0.0) AS VOL_OPT_FACTOR , ISNULL(TOF,0.0) AS TSA_OPT_FACTOR , ISNULL(FLAG,'') AS TSA_ANN_FLAG , ISNULL(CHK_2,'') AS ADDR_LINE_1 , ISNULL(CHK_1,'') AS ADDR_LINE_2 , ISNULL(CHK_ST,'') AS ADDR_STATE , ISNULL(CHK_ZIP,'') AS ADDR_ZIP , isnull(C_1,'') AS INSUR_CARR_CODE , isnull(CV_1,'') AS INSUR_COV_CODE , ISNULL(D_A,0.0) AS VOL_DEDUCT_A , isnull(CD_A,0) AS DEDUCT_CODE_A , ISNULL(D_B,0.0) AS VOL_DEDUCT_B , isnull(CD_B,0) AS DEDUCT_CODE_B , ISNULL(D_C,0.0) AS VOL_DEDUCT_C , isnull(CD_C,0) AS DEDUCT_CODE_C , ISNULL(D_D,0.0) AS VOL_DEDUCT_D , isnull(CD_D,0) AS DEDUCT_CODE_D , ISNULL(D_E,0.0) AS VOL_DEDUCT_E , isnull(CD_E,0) AS DEDUCT_CODE_E , ISNULL(MOS_1,'') AS SAL_MNTH1 , ISNULL(MOS_2,'') AS SAL_MNTH2 , ISNULL(MOS_3,'') AS SAL_MNTH3 , ISNULL(MOS_4,'') AS SAL_MNTH4 , ISNULL(MOS_5,'') AS SAL_MNTH5 , ISNULL(BEN_SSN,'') AS BEN_SSN , isnull(BEN_BD,'') AS BEN_BD , ISNULL(BEN_SEX,'') as BEN_SEX , ISNULL(BEN_CD,0) AS BEN_REL_CODE , space(9) AS BEN_RET_ALLOW , space(6) AS YGST_CHILD_BDATE , space(1) AS YGST_CHILD_SEX , ISNULL(jan,0.0) AS JAN_CONTRIB , ISNULL(feb,0.0) AS FEB_CONTRIB , ISNULL(mar,0.0) AS MAR_CONTRIB , ISNULL(apr,0.0) AS APR_CONTRIB , ISNULL(may,0.0) AS MAY_CONTRIB , ISNULL(jun,0.0) AS JUN_CONTRIB , ISNULL(jul,0.0) AS JUL_CONTRIB , ISNULL(aug,0.0) AS AUG_CONTRIB , ISNULL(sep,0.0) AS SEP_CONTRIB , ISNULL(oct,0.0) AS OCT_CONTRIB , ISNULL(nov,0.0) AS NOV_CONTRIB , ISNULL(dec,0.0) AS DEC_CONTRIB , space(9) AS JUL_PRIOR_CONTR , space(9) AS AUG_PRIOR_CONTR , space(9) AS SEP_PRIOR_CONTR , space(9) AS OCT_PRIOR_CONTR , space(9) AS NOV_PRIOR_CONTR , space(9) AS DEC_PRIOR_CONTR , ISNULL(HM_2,'') AS RET_ADDR_LINE1 , ISNULL(HM_1,'') AS RET_ADDR_LINE2 , ISNULL(HM_ST,'') AS RET_ADDR_STATE , ISNULL(HM_ZIP,'') AS RET_ADDR_ZIP , ISNULL(_10_FL,'') AS MTH_10_FLG , space(1) AS SSN_FLAG , space(1) AS NO_CALC_FLAG , ISNULL(LP,'') AS LEGIS_PLAN_TYPE , ISNULL(WORK,0.0) AS WORKMAN_COMP , space(9) AS SS_SURVIVE_BEN , space(3) AS MTHS_SERV_PRIOR , space(3) AS MTHS_SERV_AFTER , ISNULL(EFTACNUMB,'') AS DFI_ACCT_NUM , ISNULL(EFTROUTNO,'') AS TRANSIT_ROUT_NUM , ISNULL(TRN_CHK,'') AS TRN_CHK , ISNULL(eft_flg,'') AS EFT_FLAG , ISNULL(RET_N,'') AS RETIRE_NUM , space(1) as MULT_PAY_FLAG , space(9) as MULT_PAY_SSN , space(9) AS TTL_FIX_TERM_AMT , space(9) AS MTHLY_FIX_TERM_AMT , space(2) AS MISC_CODE , ISNULL(CPI_C,'') AS CPI_CODE , ISNULL(CPI_B,0.0) AS CPI_FACTOR_RETIRE , ISNULL(EB_PRCNT,'0.00') AS EMP_BILL_PCNT , space(9) as ANUITY_PRSP_BNFT , space(9) as PENSION_PRSP_BNFT , ISNULL(W_FLG,'') AS WORK_COMP_FLAG , ISNULL(VOP,'') AS VOL_OPT , ISNULL([TOP],'') AS TSA_OPT , ISNULL(WK_M,00000.00) AS WORK_COMP_MTHLY , ISNULL(SSN_R,0) AS SSN_R , space(1) AS ANNL_MIN_FLAG , space(11) AS NORM_RES_OPT , space(11) AS VOL_RES_OPT , space(11) AS TSA_RES_OPT , space(7) AS B_NOR_AF , space(7) AS B_VOL_AF , space(7) AS B_TSA_AF , ISNULL(LTRIM(RTRIM(BEN_FNAME)),'') as BEN_FNAME , ISNULL(LTRIM(RTRIM(BEN_MI)),'') as BEN_MNAME , ISNULL(LTRIM(RTRIM(BEN_LNAME)),'') AS BEN_LNAME , space(4) AS DATE_ADDED , space(7) AS POST_DATE , space(1) AS STOP_CHK_FLAG , ISNULL(ELG_MO,0) AS ELIG_MOS , ISNULL(F_EX,0) As FED_EXEMP , ISNULL(M_ST,'') AS MARITAL_ST , ISNULL(F_EX_$,0) AS FED_EX_TAX , ISNULL(FWT,0) AS FED_WHTAX_PMTH , ISNULL(DIS_FLG,'') AS PRE_EXIST_DIS , space(9) AS MAX_PENS_MO , space(9) AS MAX_ADDL_PENS_MO , space(9) AS MAX_ANN_MO , space(9) AS MAX_VOL_MO , space(9) AS MAX_TSA_MO , ISNULL(H_CODE,'') AS HOSP_SHR_CODE , ISNULL(RIGHT(RTRIM([PLAN]),1),'') AS PLAN_SELECT , ISNULL(SER_A_MEM,0) AS MEMB_SERV_AFTER , ISNULL(SER_A_PR,0) AS PRIOR_SERV_AFTER , ISNULL(SER_A_OM,0) AS OLD_MIL_AFTER , ISNULL(SER_A_NM,0) AS NEW_MIL_AFTER , ISNULL(SER_A_SL,0) AS SICK_LVE_AFTER , ISNULL(CPI_ADJ,0.0) AS CPI_ADJUST_FACTOR , ISNULL(BIF_RED_F,0.0) AS BIF_REDCT_FCTR , ISNULL(BFPR,0.0) AS BIF_PENS_RSRV , ISNULL(BFAP,0.0) AS BIF_ADDL_PENS , ISNULL(BFAR,0.0) AS BIF_ANN_RERV , ISNULL(BFVA,0.0) AS BIF_VOL_ANN , ISNULL(BFTA,0.0) AS BIF_TSA_ANN , ISNULL(BFSL,0.0) AS BIF_SUPL , ISNULL(BFCL,0.0) AS BIF_COST_LIV , ISNULL(BFMN,0.0) AS BIF_ANUAL_MIN , ISNULL(BFMAX,0.0) AS BIF_MAX_MTHLY , ISNULL(BIF_CPI_ADJ,0.0) AS BIF_CPI_ADJ_FCT , ISNULL(TAX_PCT,0.0) as PEN_TX_PCT , ISNULL(ADD_FLG,'') AS NON_RELEASE_FLG , ISNULL(TRAN_CD,'') AS EFT_TRANS_CODE , ISNULL(DENTAL,0.0) AS VISION_INS_AMT_DENTAL , ISNULL(CV_3,'') AS VISION_COV_CODE , ISNULL(DRUG,0.0) AS DRUG_INS_AMT , ISNULL(CV_2,'') AS DRUG_COV , ISNULL(D_F,0.0) AS VOL_DEDUCT_F , ISNULL(cd_f,'') AS DEDUCT_CODE_F , ISNULL(d_g,0.0) AS VOL_DEDUCT_G , ISNULL(cd_g,'') AS DEDUCT_CODE_G , ISNULL(d_h,0.0) AS VOL_DEDUCT_H , ISNULL(cd_h,'') AS DEDUCT_CODE_H , ISNULL(d_i,0.0) AS VOL_DEDUCT_I , ISNULL(cd_i,'') AS DEDUCT_CODE_I , ISNULL(d_j,0.0) AS VOL_DEDUCT_J , ISNULL(cd_j,'') AS DEDUCT_CODE_J , ISNULL(BASE_WAGES,0.0) AS GROSS_BASE_WAGES , ISNULL(BAL_EP,0.0) AS EMP_PICKUP , ISNULL(_405_LAD,'') AS LAD_405 , ISNULL(BAL_EP_REMAIN,0.0) as EP_BAL_REMAIN , ISNULL(TXFRCO,0.0) AS CRY_OVR_TAXFRE , space(2) AS BOND_DED_AMT , space(8) AS TRANSFER_DATE , ISNULL(SAFHAR_AMT,0.0) AS MONTHLY_RECOVERY , ISNULL(I_FLAG,'') AS RETMNT_INCENT_FLAG , ISNULL(I_MOS,0) AS RETMNT_INCENT_SERV , space(9) AS RETMNT_INCENT_CASH_PAY , ISNULL(b_nc,0.0) AS NORM_CONTRIB , ISNULL(b_ni,0.0) AS NORM_INT , ISNULL(b_vc,0.0) AS VOL_CONTRIB , ISNULL(b_vi,0.0) AS VOL_INT , ISNULL(b_tc,0.0) AS TSA_CONTRIB , ISNULL(b_ti,0.0) AS TSA_INT , ISNULL(b_mc,0.0) AS MIL_CONTRIB , ISNULL(b_mi,0.0) AS MIL_INT , ISNULL(DEF,0.0) AS CONTRIB_DEFIC , ISNULL(tac,0.0) AS TTL_ACC_CONTRIB , ISNULL(COST,0.0) AS TTL_NTAX_CONTRIB , ISNULL(PV,0.0) AS PRES_VAL_OPT , ISNULL(PART,0.0) AS PART_PAY_AMT , ISNULL(THREE_YEAR_AVERAGE_SALARY,0.0) AS AVG_3YR_SAL , ISNULL(SAL_1,0.0) AS SAL_1 , ISNULL(SAL_2,0.0) AS SAL_2 , ISNULL(SAL_3,0.0) AS SAL_3 , ISNULL(SAL_4,0.0) AS SAL_4 , ISNULL(SAL_5,0.0) AS SAL_5 , ISNULL(LMIT,0.0) AS ANNL_EARN_LIMIT , ISNULL(PLNSLDT,'') AS PLAN_SEL_DATE , ISNULL(ABOL_FL,'') AS RET_ABOL_FLAG , ISNULL(SSIL,0) AS SSIL , space(1) AS ADV_FLAG , ISNULL(INS,0.0) as MED_INSUR_AMT , TOT_SRV AS TOT_SV , SER_A_TOT AS SER_A_TOT , space(2) AS BIF_CPI , C_2 AS C_2 , C_3 AS C_3 , YTD AS YTD , ISNULL(CHK_CITY,'') AS ADDR_CITY , ISNULL(HM_CITY,'') AS RET_ADDR_CITY , ISNULL(LOB_RCP_ACCT_ID,0) as RCP_ID, 0, 0-- CONVERT(INT,TOT_SRV) - CASE WHEN CONVERT(INT,TOT_SRV) <> 0 THEN-- CONVERT(INT,TOT_SRV) /( ISNULL(CONVERT(INT,TOT_SRV),0)+ISNULL(CONVERT(INT,SER_A_TOT),0)) *CONVERT(INT,I_MOS)-- ELSE 0 END,-- (CONVERT(INT,TOT_SRV) -(CASE WHEN CONVERT(INT,TOT_SRV) <> 0 THEN-- CONVERT(INT,TOT_SRV) /( ISNULL(CONVERT(INT,TOT_SRV),0)+ISNULL(CONVERT(INT,SER_A_TOT),0)) *CONVERT(INT,I_MOS)-- ELSE 0 END)) FROM MPAS_TMP.DBO.TMP_pop_PRNA WHERE JOB_INST_ID = @job_inst_id AND LTRIM(RTRIM(S)) IN('R','RB','RD','BD','D') ORDER BY SSN , rcd_cd update xset transfer_date=ISNULL(LEFT(REPLACE(CONVERT(VARCHAR,trfr_dt,112),'-',''),8),' '),date_added=ISNULL(LEFT(CONVERT(VARCHAR,ret_added_dt,10),2)+RIGHT(CONVERT(VARCHAR,ret_added_dt,10),2),' '),post_date=ISNULL(REPLACE(CONVERT(VARCHAR,post_dt,10),'-',''),' '),pensn_res_opt=ltrim(rtrim(pen_res_opt)),add_pensn_res_opt=ltrim(rtrim(add_pen_opt)),norm_res_opt=ltrim(rtrim(nrml_res_opt)),mths_Serv_after=mnth_srv_aft,mths_serv_prior=mnth_srv_pri,BEN_RET_ALLOW =beny_ret_allow ,max_pens_mo= max_pen_mo,max_addl_pens_mo=max_addl_pen_mo,max_ann_mo=max_anul_mofrom MPAS_TMP.DBO.TMP_PRNJ615M_INTRAN x, lob_ret_aux_info iwhere i.lob_rcp_acct_id=x.rcp_id--and ssn='001140899'and JOB_INST_ID = @job_inst_id UPDATE X SET ADV_FLAG= Y.ADV_FLGfrom MPAS_TMP.DBO.TMP_PRNJ615M_INTRAN x, LOB_RCP_ACCT ywhere x.rcp_id=y.lob_rcp_acct_idand JOB_INST_ID = @job_inst_id UPDATE X SET ssn_r= owner.ss_nbr,b_date=OWNER.brth_dt,RET_DTH_DATE=OWNER.dth_dtfrom MPAS_TMP.DBO.TMP_PRNJ615M_INTRAN x, lob_rcp_acct lrac , lob_prsn owner, lob_ben_acct benwhere lrac.lob_ben_acct_id = ben.lob_ben_acct_idand owner.lob_prsn_id=ben.lob_prsn_idand lrac.lob_rcp_acct_id=x.rcp_idand JOB_INST_ID = @job_inst_id ----------------updating net_chk_amt select rcp_id into #tmp_main from MPAS_TMP.DBO.TMP_PRNJ615M_INTRAN xwhere JOB_INST_ID = @job_inst_id SELECT rcp_id,lob_disb_idinto #tmp_net1 FROM lob_ben_acct, lob_ben_pmt, #tmp_main tmp, lob_rcp_acct, lob_disb, ref_disb_typ WHERE lob_rcp_acct.lob_rcp_acct_id = lob_ben_pmt.lob_rcp_acct_id and lob_rcp_acct.lob_rcp_acct_id=tmp.rcp_id AND lob_rcp_acct.lob_ben_acct_id = lob_ben_acct.lob_ben_acct_id AND lob_ben_pmt.lob_ben_pmt_id = lob_disb.lob_ben_pmt_id AND lob_disb.ref_disb_typ_id=ref_disb_typ.ref_disb_typ_id and (convert(varchar,pmt_dt,112)) = (select max(convert(varchar,a.pmt_dt,112)) from lob_disb a,lob_ben_pmt b where a.lob_ben_pmt_id = b.lob_ben_pmt_id and tmp.rcp_id=b.lob_rcp_acct_id) AND ref_disb_typ.disb_typ_cd = 'BNFT' SELECT rcp_id, SUM(CASE WHEN disb_adj_typ_cd = 'PR' THEN adj_amt END) AS PR, SUM(CASE WHEN disb_adj_typ_cd = 'AP' THEN adj_amt END) AS AP, SUM(CASE WHEN disb_adj_typ_cd = 'AR' THEN adj_amt END) AS AR, SUM(CASE WHEN disb_adj_typ_cd = 'SUPP' THEN adj_amt END) AS SUPP, SUM(CASE WHEN disb_adj_typ_cd = 'VOL' THEN adj_amt END) AS VOL, SUM(CASE WHEN disb_adj_typ_cd = 'COLA' THEN adj_amt END) AS COLA, SUM(CASE WHEN disb_adj_typ_cd = 'MIN' THEN adj_amt END) AS MIN, SUM(CASE WHEN disb_adj_typ_cd = 'TSA' THEN adj_amt END) AS TSA, SUM(CASE WHEN disb_adj_typ_cd = 'GROSS' THEN adj_amt END) AS GROSS, SUM(CASE WHEN disb_adj_typ_cd = 'NET' THEN adj_amt END) AS NET, SUM(CASE WHEN disb_adj_typ_cd = 'HEALTH' THEN adj_amt END) AS HEALTH, SUM(CASE WHEN disb_adj_typ_cd = 'DRUG' THEN adj_amt END) AS DRUG, SUM(CASE WHEN disb_adj_typ_cd = 'DENTAL' THEN adj_amt END) AS DENTAL, SUM(CASE WHEN disb_adj_typ_cd = 'LONG' THEN adj_amt END) AS LONG, SUM(CASE WHEN disb_adj_typ_cd = 'SECU' THEN adj_amt END) AS SECU, SUM(CASE WHEN disb_adj_typ_cd = 'VISION' THEN adj_amt END) AS VISION, SUM(CASE WHEN disb_adj_typ_cd = 'TRPSUR' THEN adj_amt END) AS TRPSUR, SUM(CASE WHEN disb_adj_typ_cd = 'F. TAX' THEN adj_amt END) AS F_TAX, SUM(CASE WHEN disb_adj_typ_cd = 'S. TAX' THEN adj_amt END) AS S_TAX, SUM(CASE WHEN disb_adj_typ_cd = 'MSTA' THEN adj_amt END) AS MSTA, SUM(CASE WHEN disb_adj_typ_cd = 'MRTA' THEN adj_amt END) AS MRTA, SUM(CASE WHEN disb_adj_typ_cd = 'MTA' THEN adj_amt END) AS MTA, SUM(CASE WHEN disb_adj_typ_cd = 'UWAY' THEN adj_amt END) AS UWAY, SUM(CASE WHEN disb_adj_typ_cd = 'LIFINS' THEN adj_amt END) AS LIFINS, SUM(CASE WHEN disb_adj_typ_cd = 'FOP' THEN adj_amt END) AS FOP, SUM(CASE WHEN disb_adj_typ_cd = 'MCEA-D' THEN adj_amt END) AS MCEA_D, SUM(CASE WHEN disb_adj_typ_cd = 'MCEA-I' THEN adj_amt END) AS MCEA_I, SUM(CASE WHEN disb_adj_typ_cd = 'AFSM-D' THEN adj_amt END) AS AFSM_D, SUM(CASE WHEN disb_adj_typ_cd = 'AFSM-I' THEN adj_amt END) AS AFSM_I into #tmp_net2 FROM lob_disb_adj ldaj, #tmp_net1 net, ref_disb_adj_typ WHERE ldaj.ref_disb_adj_typ_id = ref_disb_adj_typ.ref_disb_adj_typ_id and net.lob_disb_id=ldaj.lob_disb_id GROUP BY rcp_id select rcp_id, ((isnull(adj.pr,0)+ isnull(adj.ap,0)+ isnull(adj.ar,0)+ isnull(adj.supp,0)+ isnull(adj.vol,0)+ isnull(adj.cola,0)+ isnull(adj.min,0)+ isnull(adj.tsa,0)) - (isnull(adj.health,0)+ isnull(adj.drug,0)+ isnull(adj.dental,0)+ isnull(adj.long,0)+ isnull(adj.secu,0)+ isnull(adj.trpsur,0)+ isnull(adj.f_tax,0)+ isnull(adj.vision,0)+ isnull(adj.s_tax,0)+ isnull(adj.msta,0)+ isnull(adj.mta,0)+ isnull(adj.mrta,0)+ isnull(adj.uway,0)+ isnull(adj.lifins,0)+ isnull(adj.fop,0)+ isnull(adj.mcea_d,0)+ isnull(adj.mcea_i,0)+ isnull(adj.afsm_d,0)+ isnull(adj.afsm_i,0)))as net into #tmp_net3 from #tmp_net2 adj UPDATE X SET NET_MTH_CHK = Y.netfrom MPAS_TMP.DBO.TMP_PRNJ615M_INTRAN x, #tmp_net3 ywhere x.rcp_id=y.rcp_idand JOB_INST_ID = @job_inst_id UPDATE mpas_tmp.dbo.TMP_PRNJ615M_INTRAN SET RET_INCN_MNTH_PRIR = isnull(RETMNT_INCENT_SERV - ROUND(((ISNULL(PRIOR_SERV_MTHS,0)+ISNULL(MEMB_SERV_MTHS,0)+ISNULL(SICK_LEAVE_MTHS,0)+ISNULL(OLD_MIL_MTHS,0) +ISNULL(NEW_MIL_MTHS,0) +ISNULL(RETMNT_INCENT_SERV,0)) /((NULLIF(PRIOR_SERV_MTHS,0)+NULLIF(MEMB_SERV_MTHS,0)+NULLIF(SICK_LEAVE_MTHS,0)+NULLIF(OLD_MIL_MTHS,0) +NULLIF(NEW_MIL_MTHS,0) +NULLIF(RETMNT_INCENT_SERV,0)) + (NULLIF(PRIOR_SERV_AFTER,0)+NULLIF(MEMB_SERV_AFTER,0)+NULLIF(SICK_LVE_AFTER,0)+NULLIF(OLD_MIL_AFTER,0) +NULLIF(NEW_MIL_AFTER,0))) * RETMNT_INCENT_SERV),0),0), RET_INCN_MNTH_AFTR = isnull(ROUND(((ISNULL(PRIOR_SERV_MTHS,0)+ISNULL(MEMB_SERV_MTHS,0)+ISNULL(SICK_LEAVE_MTHS,0)+ISNULL(OLD_MIL_MTHS,0) +ISNULL(NEW_MIL_MTHS,0) +ISNULL(RETMNT_INCENT_SERV,0)) /((NULLIF(PRIOR_SERV_MTHS,0)+NULLIF(MEMB_SERV_MTHS,0)+NULLIF(SICK_LEAVE_MTHS,0)+NULLIF(OLD_MIL_MTHS,0) +NULLIF(NEW_MIL_MTHS,0) +NULLIF(RETMNT_INCENT_SERV,0)) + (NULLIF(PRIOR_SERV_AFTER,0)+NULLIF(MEMB_SERV_AFTER,0)+NULLIF(SICK_LVE_AFTER,0)+NULLIF(OLD_MIL_AFTER,0) +NULLIF(NEW_MIL_AFTER,0))) * RETMNT_INCENT_SERV),0),0) WHERE JOB_INST_ID = @JOB_INST_ID SELECT TMP_PRNJ615M_INTRAN_ID , PRIOR_SERV_MTHS , Mths_Serv_Prior , MEMB_SERV_MTHS , OLD_MIL_MTHS , NEW_MIL_MTHS , SICK_LEAVE_MTHS , Mths_Serv_After , SYSTEM , LOCATION , STATUS_CD , RETIRE_TYPE , RETIRE_DATE , INSUR_COV_CODE , INSUR_COV_CODE , DEDUCT_CODE_A , DEDUCT_CODE_B , DEDUCT_CODE_C , DEDUCT_CODE_D , DEDUCT_CODE_E , Mth_10_Flg , Hosp_Shr_Code , Memb_Serv_After , Prior_Serv_After , Old_Mil_After , New_Mil_After , Sick_Lve_After , Vision_Ins_Amt_Dental , Vision_Cov_Code , Drug_Ins_Amt , Drug_Cov , Deduct_Code_F , Med_Insur_Amt FROM mpas_tmp.dbo.TMP_PRNJ615M_INTRAN WHERE JOB_INST_ID =@JOB_INST_ID END ELSE BEGIN INSERT INTO mpas_tmp.dbo.TMP_PRNJ615M_SYSOUR ( JOB_INST_ID , ERR_MSG ) VALUES(@JOB_INST_ID , '** PRN01227 - INVALID OR MISSING CONTROL CARD.') END --------------------DELETING THE OLD PRNA RECORD---------------------------------------- DELETE FROM MPAS_TMP.DBO.TMP_POP_PRNA WHERE JOB_INST_ID = @JOB_INST_ID DELETE FROM MPAS_TMP.DBO.TMP_POP_PRNA_SSN WHERE JOB_INST_ID = @JOB_INST_IDEND |
|
|
|
jordanam
Yak Posting Veteran
62 Posts |
Posted - 2008-02-04 : 11:24:39
|
| It would be helpful to know if INSERTS, DELETES, or SELECTS were taking up the brunt of your time. You use a few temporary tables, and if those aren't indexed, then depending on the number of results you have in them, those could be your killers. That's just a shot in the dark though.The code you have presented is a little too hard (some of the more experienced users on this forum would probably disagree) to read and find problems with, mostly because the underlying data sources are nowhere to be seen. Without having the tables and sample data, it's not very reasonable to ask for performance tuning.Look up some general performance tuning, and you can always throw some of the code into the Database Tuning Advisor.Good luck |
|
|
|
|
|
|
slow running proc's