存储过程调优

Question

CREATE PROCEDURE [dbo].[Army_CalcPopulation]
@SovereignID INT,
@CityID INT
AS
BEGIN
DECLARE @CurrentArmyPopulation INT SELECT 
@CurrentArmyPopulation=ISNULL(SUM(us.SoldierNum*s.[Population]),0)
FROM
UserSoldier(NOLOCK) us INNER JOIN Soldier(NOLOCK) s ON us.SoldierCode=s.SoldierCode
WHERE 
us.CityID=@CityID AND us.SovereignID=@SovereignID SELECT 
@CurrentArmyPopulation=@CurrentArmyPopulation+ISNULL(SUM(as1.SoldierNum*s.[Population]),0)
FROM
UserSirdar(NOLOCK) us INNER JOIN Army(NOLOCK) a ON us.ID=a.SirdarID
INNER JOIN ArmySoldier(NOLOCK) as1 ON a.ArmyID=as1.ArmyID AND as1.SirdarStatus=0
INNER JOIN Soldier(NOLOCK) s ON as1.SoldierCode=s.SoldierCode
WHERE 
us.CityID=@CityID AND us.SovereignID=@SovereignID AND us.SirdarStatus=1 SELECT 
@CurrentArmyPopulation=@CurrentArmyPopulation+ISNULL(SUM(as1.SoldierNum*s.[Population]),0)
FROM
UserSirdar(NOLOCK) us INNER JOIN Army(NOLOCK) a ON us.ID=a.SirdarID
INNER JOIN ArmySoldier(NOLOCK) as1 ON a.ArmyID=as1.ArmyID AND as1.SirdarStatus=0
INNER JOIN Soldier(NOLOCK) s ON as1.SoldierCode=s.SoldierCode
WHERE 
us.CityID=@CityID AND us.SovereignID=@SovereignID AND us.SirdarStatus=8 UPDATE UserCity SET CurrentArmyPopulation=@CurrentArmyPopulation WHERE CityID=@CityID
END
GO
这存储过程的SQL怎么优化?查询它占用CPU很大SELECT 
@CurrentArmyPopulation=@CurrentArmyPopulation+ISNULL(SUM(as1.SoldierNum*s.[Population]),0)
FROM
UserSirdar(NOLOCK) us INNER JOIN Army(NOLOCK) a ON us.ID=a.SirdarID
INNER JOIN ArmySoldier(NOLOCK) as1 ON a.ArmyID=as1.ArmyID AND as1.SirdarStatus=0
INNER JOIN Soldier(NOLOCK) s ON as1.SoldierCode=s.SoldierCode
WHERE 
us.CityID=@CityID AND us.SovereignID=@SovereignID AND us.SirdarStatus=1

Answer 1

CREATE OR REPLACE PROCEDURE PRO_Y619(VFACT   IN VARCHAR2,
                                              VYYYY   IN VARCHAR2,
                                              VOUTMSG OUT VARCHAR2) IS
  VPNL       HR_YYJC.PNL%TYPE;
  VNAME      HR_PERSON.NAME%TYPE;
  VINDATE    HR_PERSON.IN_DATE%TYPE;
  VI_HOLI_AB NUMBER;
  VYMONQ     HR_YYJC.MONQ%TYPE;
  VSEC_NO    HR_YYJC.SEC_NO%TYPE;
  VDD        NUMBER(3);
  VDD1       NUMBER(3);
  VDATE      VARCHAR2(50);
  CURSOR C_1 IS
    SELECT DISTINCT A.PNL,
                    A.NAME,
                    A.IN_DATE,
                    MON.HOLI_AB I_HOLI_AB,
                    A.SEC_NO
      FROM H_P A,
           (SELECT PNL PNL1, SUM(NVL(WKHOUR_HOLI, 0) / 8) HOLI_AB
              FROM HR_Y
             WHERE YYMM LIKE TO_CHAR(TO_NUMBER(VYYYY) - 1) || '%'
             GROUP BY PNL) MON
     WHERE A.PNL = MON.PNL1(+)
       AND A.PNLSTATUS = '1'
       AND SUBSTR(A.IN_DATE, 1, 6) <= TO_CHAR(TO_NUMBER(VYYYY) - 1) || '10'
       AND A.PNL NOT IN
           (SELECT PNL
              FROM HR_Y
             WHERE YYMM LIKE TO_CHAR(TO_NUMBER(VYYYY) - 1) || '%'
             GROUP BY PNL
            HAVING SUM(NVL(WKHOUR_HOLI, 0)) > 480);
  C1 C_1%ROWTYPE;
BEGIN
  DELETE FROM H_Y2016
   WHERE FACT_NO = VFACT
     AND YYYY = VYYYY;
  COMMIT;  OPEN C_1;
  LOOP
    VPNL       := '';
    VNAME      := '';
    VINDATE    := '';
    VI_HOLI_AB := 0;
    VSEC_NO    := '';
    VDD        := 0;
    VDATE      := '';
    FETCH C_1
      INTO VPNL, VNAME, VINDATE, VI_HOLI_AB, VSEC_NO;
    EXIT WHEN C_1%NOTFOUND;
  
    SELECT TRUNC(((((TO_NUMBER(TO_CHAR(TO_NUMBER(VYYYY) - 1)) -
                 TO_NUMBER(SUBSTR(VINDATE, 1, 4))) * 12) +
                 (12 - TO_NUMBER(SUBSTR(VINDATE, 5, 2)))) / 12),
                 0)
      INTO VDD
      FROM DUAL;
    IF SUBSTR(VINDATE, 1, 4) = TO_CHAR(TO_NUMBER(VYYYY) - 1) THEN
      IF SUBSTR(VINDATE, 7, 2) = '01' THEN
        VDD := ROUND((5 * (12 - TO_NUMBER(SUBSTR(VINDATE, 5, 2)) + 1) / 12),
                     0);
      ELSE
        VDD := ROUND(5 * (12 - TO_NUMBER(SUBSTR(VINDATE, 5, 2))) / 12, 0);
      END IF;
    ELSE
      IF VDD > 0 AND VDD < 10 THEN
        VDD := 5;
      ELSIF VDD >= 10 AND VDD < 20 THEN
        VDD := 10;
      ELSIF VDD <= 0 THEN
        VDD := 0;
      ELSE
        VDD := 15;
      END IF;
    END IF;
  
    IF SUBSTR(VINDATE, 1, 4) = TO_CHAR(TO_NUMBER(VYYYY) - 1) THEN
      IF SUBSTR(VINDATE, 7, 2) = '01' THEN
        VDATE := TO_CHAR(TO_NUMBER(SUBSTR(VINDATE, 1, 4)) + 1) ||
                 TO_CHAR(TO_NUMBER(SUBSTR(VINDATE, 5, 2)), '00') || '01' || '--' ||
                 TO_CHAR(TO_NUMBER(SUBSTR(VINDATE, 1, 4)) + 1) || '1231';
      ELSE
        VDATE := TO_CHAR(TO_NUMBER(SUBSTR(VINDATE, 1, 4)) + 1) ||
                 TO_CHAR(TO_NUMBER(SUBSTR(VINDATE, 5, 2)) + 1, '00') || '01' || '--' ||
                 TO_CHAR(TO_NUMBER(SUBSTR(VINDATE, 1, 4)) + 1) || '1231';
      END IF;
    ELSE
      VDATE := '';
    END IF;
  
    VDD1 := 0;
    SELECT COUNT(1)
      INTO VDD1
      FROM HR_YM
     WHERE FACT_NO = VFACT
       AND PNL = VPNL
       AND SUBSTR(YMDAY, 1, 4) = VYYYY;
    IF VDD1 > 0 THEN
      SELECT SUM(WKHOUR_NJ) / 8 YJ_DAY
        INTO VDD1
        FROM HR_YM
       WHERE FACT_NO = VFACT
         AND PNL = VPNL
         AND SUBSTR(YMDAY, 1, 4) = VYYYY
       GROUP BY PNL;
    END IF;
  
    INSERT INTO H_Y2016
      (FACT_NO,
       YYYY,
       PNL,
       NAME,
       IN_DATE,
       YS_DAY,
       ABSENT,
       KS_DATE,
       SS,
       WS,
       SEC_NO)
    VALUES
      (VFACT,
       VYYYY,
       VPNL,
       VNAME,
       VINDATE,
       VDD,
       VI_HOLI_AB,
       VDATE,
       VDD1,
       VDD - VDD1,
       VSEC_NO);
  
  END LOOP;
  CLOSE C_1;
  COMMIT;  VOUTMSG := '1';
  EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    VOUTMSG := SQLERRM || VPNL;
END PRO_Y619;
以上存儲過程怎麼優化一下，謝謝，因為運行起來好慢好慢