2 yang2948443 yang2948443 于 2016.03.15 12:17 提问

存储过程执行时间过长,怎么改进!求大神指导 50C
 INAME(MONTH,@StartDate)+'-1'
                SET @EndTime=DATEADD(DAY,-1,DATEADD(MONTH,1,@StartTime));
                IF(@EndTime<=@EndDate)

                            SET @Days=365.0/12;

                        END
                    END                 
    AR(@StartTime));

me),YEAR(@StartTime));

                        END
                    END
MONTH,@StartDate)+'-1'
                SET @EndTime=DATEADD(DAY,-1,DATEADD(MONTH,1,@StartTime));
                IF(@EndTime<=@End)


                            BREAK;

                SET @num=0;

                    BEGIN
                        IF(@EndTime<=@EndDate)
                        BEGIN
                            IF(@num=0 AND @StartTime<@Start)
                            BEGIN
                                SET @Days=365.0/12-DATEDIFF(DAY,@StartTime,@Start);
                                INSERT INTO #Satistic VALUES (@Days,@CYRentArea,@Start,@EndTime,@Start,@End,@BuildingID,@ProjectID,MONTH(@Start),YEAR(@Start));
                            END
                            ELSE
                            BEGIN
                                SET @Days=365.0/12;
                                INSERT INTO #Satistic VALUES (@Days,@CYRentArea,@StartTime,@EndTime,@Start,@End,@BuildingID,@ProjectID,MONTH(@StartTime),YEAR(@StartTime));
                            END

                            SET @StartTime=DATEADD(MONTH,1,@StartTime);
                            SET @EndTime=DATEADD(DAY,-1,DATEADD(MONTH,1,@StartTime));
                            SET @num=@num+1;
                        END
                        ELSE IF(@StartTime<=@EndDate)
                        BEGIN
                            IF(@num=0 AND @StartTime<@Start)
                            BEGIN
                                SET @Days=365.0/12-DATEDIFF(DAY,@StartTime,@Start);
                                INSERT INTO #Satistic VALUES (@Days,@CYRentArea,@Start,@EndTime,@Start,@End,@BuildingID,@ProjectID,MONTH(@Start),YEAR(@Start));
                            END
                            ELSE
                            BEGIN
                                SET @Days=DATEDIFF(DAY,@StartTime,@EndDate)+1;
                                INSERT INTO #Satistic VALUES (@Days,@CYRentArea,@StartTime,@EndDate,@Start,@End,@BuildingID,@ProjectID,MONTH(@StartTime),YEAR(@StartTime));
                            END
                            BREAK;
                        END
                        ELSE
                        BEGIN
                            BREAK;
                        END
                    END
                END
                ELSE IF(@Start<=@EndDate)
                BEGIN
                    SET @Days=DATEDIFF(DAY,@Start,@EndDate)+1;
                    insert INTO #Satistic VALUES (@Days,@CYRentArea,@Start,@EndDate,@Start,@End,@BuildingID,@ProjectID,MONTH(@Start),YEAR(@Start));
                END
            END
            ELSE IF(@Start>=@StartDate AND @End<@EndDate)
            BEGIN
                SET @num=0;
                SET @StartTime=DATENAME(Year,@Start)+'-'+DATENAME(MONTH,@Start)+'-1'
                SET @EndTime=DATEADD(DAY,-1,DATEADD(MONTH,1,@StartTime));
                IF(@EndTime<=@End)
                BEGIN
                    WHILE(1=1)
                    BEGIN
                        IF(@EndTime<=@End)
                        BEGIN
                            IF(@num=0 AND @StartTime<@Start)
                            BEGIN
                                SET @Days=365.0/12-DATEDIFF(DAY,@StartTime,@Start);
                                INSERT INTO #Satistic VALUES (@Days,@CYRentArea,@Start,@EndTime,@Start,@End,@BuildingID,@ProjectID,MONTH(@Start),YEAR(@Start));
                            END
                            ELSE
                            BEGIN
                                SET @Days=365.0/12;
                                insert INTO #Satistic VALUES (@Days,@CYRentArea,@StartTime,@EndTime,@Start,@End,@BuildingID,@ProjectID,MONTH(@StartTime),YEAR(@StartTime));
                            END

                            SET @StartTime=DATEADD(MONTH,1,@StartTime);
                            SET @EndTime=DATEADD(DAY,-1,DATEADD(MONTH,1,@StartTime));
                            SET @num=@num+1;
                        END
                        ELSE IF(@StartTime<=@End)
                        BEGIN
                            IF(@num=0 AND @StartTime<@Start)
                            BEGIN
                                SET @Days=365.0/12-DATEDIFF(DAY,@StartTime,@Start);
                                INSERT INTO #Satistic VALUES (@Days,@CYRentArea,@Start,@EndTime,@Start,@End,@BuildingID,@ProjectID,MONTH(@Start),YEAR(@Start));
                            END
                            ELSE
                            BEGIN
                                SET @Days=DATEDIFF(DAY,@StartTime,@End)+1;
                                INSERT INTO #Satistic VALUES (@Days,@CYRentArea,@StartTime,@End,@Start,@End,@BuildingID,@ProjectID,MONTH(@StartTime),YEAR(@StartTime));
                            END
                            BREAK;
                        END
                        ELSE
                        BEGIN
                            BREAK;
                        END
                    END
                END
                ELSE IF(@Start<=@End)
                BEGIN
                    SET @Days=DATEDIFF(DAY,@Start,@End)+1;
                    INSERT INTO #Satistic VALUES (@Days,@CYRentArea,@Start,@End,@Start,@End,@BuildingID,@ProjectID,MONTH(@Start),YEAR(@Start));
                END
            END
        END

1个回答

u012216727
u012216727   Ds   Rxr 2016.03.15 12:23

这存储过程,还不如不用存储过程呢!

yang2948443
yang2948443 别人写的,现在需要改。
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!