|
|
本帖最后由 569026369 于 2017-10-25 15:32 编辑
1服务器环境：1、SQLServer2008数据库，64位 Windows2008操作系统，64G内存，E5-2603CPU
用户批量勾选单位，点击生成费用俺就，每个单位生成费用就是执行下面一段sql语句， HR_WF_CalD1表里面的数据在600万-1000万之间，其他的表数据都在10万-50万之间，
点击生成时，
1、用户自己的浏览器直接卡死
2、其他用户无法打开和HR_WF_CalD1表相关的查询功能
3、直接在sql分析器里面执行 select top 10 * from HR_WF_CalD1 也无法执行，要等费用生成的执行完之后才能执行
而单个单位费用表生成需要1分钟到5分钟之间，批量生成的话往往要30分钟到2小时才能完成，这时候其他用户就无法使用系统
String sql="insert into HR_WF_CalD1 d(d,d.version,d.fMasterID,d.fEmpName,d.fEmpID,d.fIDCard,d.fWFItemName,d.fWFItemID,d.fWFSchemeName,d.fWFSchemeID,d.fShortName,d.fShortID,d.fPayKind,"
+" d.fAccountSchemeName,d.fAccountSchemeID,d.fBaseMoney,d.fOrgPayValue,d.fPerPayValue,d.fOrgPayMoney,d.fPerPayMoney,d.fYear,d.fMonth,d.fBelongYear,d.fBelongMonth, "
+" d.fWorkState,d.fisFirstAccount,d.fisSecondAccount,d.fUintSchemeID,d.fLxMoney,d.fBeginYear,d.fBeginMonth,d.fStopYear,d.fStopMonth,d.fOtherMoney,"
+" d.fChangeState,d.fSuppleName,d.fSuppleMoney,d.fAccountYear,d.fAccountMonth,d.fMinistry,d.fSex,d.fCode,d.fYearMonth)"
+" (select guid(),0,'"+id+"', c.fEmpName,c.fEmpID,c.fIDCard,c.fWFItemName,c.fWFItemID,c.fWFSchemeName,c.fWFSchemeID,"
+" c.fShortName,c.fShortID,c.fPayKind,c.fAccountSchemeName,c.fAccountSchemeID,c.fBaseMoney,"
+" case when si.sValidState=1 then si.fOrgPayValue else s.fOrgPayValue end,"
+" case when si.sValidState=1 then si.fPerPayValue else s.fPerPayValue end,"
//企业缴费
+" case when s.fisInt=0 then "
//非逢角进元
+" case when s.fPayMode='年缴' then "
+" case when c.fAccountMonth=12 then "
+" case when si.sValidState=1 then round(round(si.fOrgPayValue*c.fBaseMoney,s.fPlaceNumber+2),s.fPlaceNumber) "
+" else round(round(s.fOrgPayValue*c.fBaseMoney,s.fPlaceNumber+2),s.fPlaceNumber) "
+" end "
+" else 0"
+" end "
+" else "
+" case when si.sValidState=1 then round(round(si.fOrgPayValue*c.fBaseMoney,s.fPlaceNumber+2),s.fPlaceNumber) "
+" else round(round(s.fOrgPayValue*c.fBaseMoney,s.fPlaceNumber+2),s.fPlaceNumber) "
+" end "
+" end "
+" else "
//逢角进元
+" case when s.fPayMode='年缴' then "
+" case when c.fAccountMonth=12 then "
+" case when si.sValidState=1 then SQL.Ceiling(si.fOrgPayValue*c.fBaseMoney) "
+" else SQL.Ceiling(s.fOrgPayValue*c.fBaseMoney) "
+" end "
+" else 0"
+" end "
+" else "
+" case when si.sValidState=1 then SQL.Ceiling(si.fOrgPayValue*c.fBaseMoney) "
+" else SQL.Ceiling(s.fOrgPayValue*c.fBaseMoney) "
+" end "
+" end "
+" end,"
//个人缴费
+" case when s.fisInt=0 then "
//非逢角进元
+" case when s.fPayMode='年缴' then "
+" case when c.fAccountMonth=12 then "
+" case when si.sValidState=1 then round(round(si.fPerPayValue*c.fBaseMoney,s.fPlaceNumber+2),s.fPlaceNumber) "
+" else round(round(s.fPerPayValue*c.fBaseMoney,s.fPlaceNumber+2),s.fPlaceNumber) "
+" end "
+" else 0"
+" end "
+" else "
+" case when si.sValidState=1 then round(round(si.fPerPayValue*c.fBaseMoney,s.fPlaceNumber+2),s.fPlaceNumber) "
+" else round(round(s.fPerPayValue*c.fBaseMoney,s.fPlaceNumber+2),s.fPlaceNumber) "
+" end "
+" end "
+" else "
//逢角进元
+" case when s.fPayMode='年缴' then "
+" case when c.fAccountMonth=12 then "
+" case when si.sValidState=1 then SQL.Ceiling(si.fPerPayValue*c.fBaseMoney) "
+" else SQL.Ceiling(s.fPerPayValue*c.fBaseMoney) "
+" end "
+" else 0"
+" end "
+" else "
+" case when si.sValidState=1 then SQL.Ceiling(si.fPerPayValue*c.fBaseMoney) "
+" else SQL.Ceiling(s.fPerPayValue*c.fBaseMoney) "
+" end "
+" end "
+" end ,"
+fYear+","+fMonth+","
+" case when c.fBelongMonth=12 then c.fBelongYear+1 else c.fBelongYear end ,"
+" case when c.fBelongMonth=12 then 1 else c.fBelongMonth+1 end , "
+"'是','未初审','未复审','"+fUintSchemeID+"',0,c.fBeginYear,c.fBeginMonth,c.fStopYear,c.fStopMonth,0,"
//状态
+" '在职',"
+" s.fSuppleName,"
//补充险
+" case when s.fSuppleMoney>0 then "
+" case when s.fSuppleKind='月缴' then s.fSuppleMoney"
+" when s.fSuppleKind='年缴' then "
+" case when c.fAccountMonth=12 then s.fSuppleMoney else 0 end"
+" when s.fSuppleKind='其它' then "
+" case when (c.fAccountMonth+1)=s.fSuppleMonth then s.fSuppleMoney else 0 end"
+" else 0 end"
+" else 0 "
+" end,"
+" case when c.fAccountMonth=12 then c.fAccountYear+1 else c.fAccountYear end ,"
+" case when c.fAccountMonth=12 then 1 else c.fAccountMonth+1 end,"
+" c.fMinistry,c.fSex,c.fCode,c.fAccountYear*12+c.fAccountMonth+1"
+" from HR_WF_CalD1 c join V_HR_WF_SchemeItems s on s.fWFSchemeID=c.fWFSchemeID and s.fWFItemID=c.fWFItemID "
+" OPTIONAL JOIN HR_OG_SchemeItems si on si.fWFSchemeID=c.fWFSchemeID and si.fShortID=c.fShortID and si.fWFItemID=c.fWFItemID "
+" where c.fShortID='"+fShortID+"' and c.fAccountSchemeID='"+fAccountSchemeID+"' and c.fPayKind='正常' and c.fWFItemName<>'制卡费' "
+" and c.fYear="+fUpYear+" and c.fMonth="+fUpMonth+" and c.fChangeState='在职' "
+" and ((c.fAccountYear*12+c.fAccountMonth+1)<(c.fStopYear*12+c.fStopMonth) or (c.fStopYear*12+c.fStopMonth)=0)"
+")";
KSQL.executeUpdate(sql, null, "/HR/welfare/data", null);
|
|
IP卡
狗仔卡
发表于 2017-10-25 15:15:54
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
显身卡
发表于 2017-10-25 15:53:40
