set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go/*EsTime为算法中的o,默认为100
Id为路线代号 */
ALTER PROCEDURE [dbo].[Write4_2_E](@EsTime SMALLINT=100,@Id SMALLINT,@m SMALLINT,@n SMALLINT,@Time SMALLINT) --EsTime为算法中的oASBEGIN
/*
i为循环中的变量i;
j为i-1;
Down_Count为1_2_F;
ST1——前一站站点代号;
ST2——后一站站点代号;
*/
DECLARE @i SMALLINT,@j SMALLINT,@Down_Count TINYINT,@ST1 SMALLINT,@ST2 SMALLINT SET @i = 2
SET @Down_Count = (SELECT Down_Count FROM Line WHERE Id = @Id)-- PRINT 'BEGIN'
-- PRINT @Down_Count WHILE(@i <= @Down_Count)
BEGIN/*while循环*/
--声明时间
-- PRINT 'WHILE BEGIN' SET @j = @i-1 DECLARE @Time1 SMALLINT,@Time1_H TINYINT,@Time1_M TINYINT,@Time1_S TINYINT,@Time1_L SMALLINT
DECLARE @Time2 SMALLINT,@Time2_H TINYINT,@Time2_M TINYINT,@Time2_S TINYINT,@Time2_L SMALLINT
DECLARE @GPS_Flag TINYINT
-- SET @ST1 = (SELECT StationId FROM TB1_3_2 WHERE LineId=@Id and StationNum=@j)
-- SET @ST2 = (SELECT StationId FROM TB1_3_2 WHERE LineId=@Id and StationNum=@i)
SELECT @ST1 = StationId FROM TB1_3_2 WHERE LineId=@Id and StationNum=@j
SELECT @ST2 = StationId FROM TB1_3_2 WHERE LineId=@Id and StationNum=@i
SELECT @Time1_H = Next_H,
@Time1_M = Next_M,
@Time1_S = Next_S,
@Time1_L = Used_Time,
@Time2_H = Pre_H,
@Time2_M = Pre_M,
@Time2_S = Pre_s,
@Time2_L = Es_Time,
@GPS_Flag = Gps_Flag
FROM TB2_2 WHERE Prev_Id = @j and Next_Id = @i SET @Time1 = @Time1_H*3600 + @Time1_M*60 + @Time1_S
SET @Time2 = @Time2_H*3600 + @Time2_M*60 + @Time2_S
-- PRINT @Time1
-- PRINT @Time2 IF @GPS_Flag=0
BEGIN
IF @Time1>@Time2
BEGIN
SET @n = @Time1_L
SET @Time = @Time1
END
ELSE
BEGIN
SET @n = @Time2_L
SET @Time = @Time2
END
END
ELSE
BEGIN
SET @n = @Time1_L
SET @Time = @Time1
END
IF DATEDIFF(mi,CONVERT(VARCHAR(11),GETDATE(),120)+LTRIM(@Time1_H)+':'+LTRIM(@Time1_M)+':'+LTRIM(@Time1_S),GETDATE()) > 15
SELECT @n = Time_Ave FROM Line WHERE Id=@Id
SET @m = @m + @n IF @m > @EsTime --(是字母o,不是数字0)
UPDATE TB4_2 SET Es_NextBus_Time = @m - @EsTime WHERE LineId=@Id and StationNum=@i
ELSE
UPDATE TB4_2 SET Es_NextBus_Time = @m WHERE LineId=@Id and StationNum=@i IF (SELECT Bus_Between_Sum FROM TB4_2 WHERE LineId=@Id and StationNum=@i) != 0
BEGIN
SET @m = 0
--写到这儿了
SET @EsTime = DATENAME(HH,GETDATE())*3600+DATENAME(MI,GETDATE())*60+DATENAME(SS,GETDATE())-
(Select min(Arr_H*60 + arr_m*60 + arr_s) from TB4_1 A,TB4_3 B where A.Flag=B.Flag and A.StationNum=B.Arr_Num and A.StationNum=@i)
END
ELSE
SET @m = 0
SET @i = @i + 1 PRINT @i
-- PRINT 'WHILE END'
END/*while循环*/
PRINT @EsTime
END这段存储过程是某个算法的其中一部分。
自己做了个循环,循环1000次,大概需要250秒,但是远远满足不了要求,怎么才能提高运行的速度呢??
可能这个问题说的比较笼统,各位有什么问题我再补充
set QUOTED_IDENTIFIER ON
go/*EsTime为算法中的o,默认为100
Id为路线代号 */
ALTER PROCEDURE [dbo].[Write4_2_E](@EsTime SMALLINT=100,@Id SMALLINT,@m SMALLINT,@n SMALLINT,@Time SMALLINT) --EsTime为算法中的oASBEGIN
/*
i为循环中的变量i;
j为i-1;
Down_Count为1_2_F;
ST1——前一站站点代号;
ST2——后一站站点代号;
*/
DECLARE @i SMALLINT,@j SMALLINT,@Down_Count TINYINT,@ST1 SMALLINT,@ST2 SMALLINT SET @i = 2
SET @Down_Count = (SELECT Down_Count FROM Line WHERE Id = @Id)-- PRINT 'BEGIN'
-- PRINT @Down_Count WHILE(@i <= @Down_Count)
BEGIN/*while循环*/
--声明时间
-- PRINT 'WHILE BEGIN' SET @j = @i-1 DECLARE @Time1 SMALLINT,@Time1_H TINYINT,@Time1_M TINYINT,@Time1_S TINYINT,@Time1_L SMALLINT
DECLARE @Time2 SMALLINT,@Time2_H TINYINT,@Time2_M TINYINT,@Time2_S TINYINT,@Time2_L SMALLINT
DECLARE @GPS_Flag TINYINT
-- SET @ST1 = (SELECT StationId FROM TB1_3_2 WHERE LineId=@Id and StationNum=@j)
-- SET @ST2 = (SELECT StationId FROM TB1_3_2 WHERE LineId=@Id and StationNum=@i)
SELECT @ST1 = StationId FROM TB1_3_2 WHERE LineId=@Id and StationNum=@j
SELECT @ST2 = StationId FROM TB1_3_2 WHERE LineId=@Id and StationNum=@i
SELECT @Time1_H = Next_H,
@Time1_M = Next_M,
@Time1_S = Next_S,
@Time1_L = Used_Time,
@Time2_H = Pre_H,
@Time2_M = Pre_M,
@Time2_S = Pre_s,
@Time2_L = Es_Time,
@GPS_Flag = Gps_Flag
FROM TB2_2 WHERE Prev_Id = @j and Next_Id = @i SET @Time1 = @Time1_H*3600 + @Time1_M*60 + @Time1_S
SET @Time2 = @Time2_H*3600 + @Time2_M*60 + @Time2_S
-- PRINT @Time1
-- PRINT @Time2 IF @GPS_Flag=0
BEGIN
IF @Time1>@Time2
BEGIN
SET @n = @Time1_L
SET @Time = @Time1
END
ELSE
BEGIN
SET @n = @Time2_L
SET @Time = @Time2
END
END
ELSE
BEGIN
SET @n = @Time1_L
SET @Time = @Time1
END
IF DATEDIFF(mi,CONVERT(VARCHAR(11),GETDATE(),120)+LTRIM(@Time1_H)+':'+LTRIM(@Time1_M)+':'+LTRIM(@Time1_S),GETDATE()) > 15
SELECT @n = Time_Ave FROM Line WHERE Id=@Id
SET @m = @m + @n IF @m > @EsTime --(是字母o,不是数字0)
UPDATE TB4_2 SET Es_NextBus_Time = @m - @EsTime WHERE LineId=@Id and StationNum=@i
ELSE
UPDATE TB4_2 SET Es_NextBus_Time = @m WHERE LineId=@Id and StationNum=@i IF (SELECT Bus_Between_Sum FROM TB4_2 WHERE LineId=@Id and StationNum=@i) != 0
BEGIN
SET @m = 0
--写到这儿了
SET @EsTime = DATENAME(HH,GETDATE())*3600+DATENAME(MI,GETDATE())*60+DATENAME(SS,GETDATE())-
(Select min(Arr_H*60 + arr_m*60 + arr_s) from TB4_1 A,TB4_3 B where A.Flag=B.Flag and A.StationNum=B.Arr_Num and A.StationNum=@i)
END
ELSE
SET @m = 0
SET @i = @i + 1 PRINT @i
-- PRINT 'WHILE END'
END/*while循环*/
PRINT @EsTime
END这段存储过程是某个算法的其中一部分。
自己做了个循环,循环1000次,大概需要250秒,但是远远满足不了要求,怎么才能提高运行的速度呢??
可能这个问题说的比较笼统,各位有什么问题我再补充
解决方案 »
- sql函数问题,在sql2000查询下使用,注释少里点。懂函数的能看懂,很规范的书写
- 急!!!存储过程赋值问题 如何让 exec执行的SQL语句中的数据赋值给存储过程中的output变量?
- SQL中关于year()的一个问题
- 怎么在SQL SERVER 里面设置权限管理功能,并建立起用户清单!!跪谢!
- 简单问题,希望有人帮忙,在线等待
- sql按时间进行查询统计总数问题
- 如何将列值转成行显示,并且可以设置不同的背景色?
- 请大侠们指点 关于ADO连接非法断开,重新连接的问题
- 如何恢复BAK文件?
- 急救:复制同步数据库时报 列名 'rowguidcol' 无效
- SQL2008 创建数据库失败!在数据库master中拒绝了CREATE DATABASE权限
- 如何通过sql日志,查看以前执行过的sql语句
好像大多数是时间 的计算。
你可以把这些变量 插入一个临时表中,关联操作快。
也可把循环一千次与master..spt_values表来关联