救命啊,关于存储过程的优化!SQL SERVER2005!

vanjayhsu 2007-09-19 03:27:03
下面是我的存储过程,想取得每一个id每一天(从07.2.7到07.7.17)的总数以及在各种情况下出现的总数。id一共有460个左右,但执行起来慢的可怜了。从表T中去查,表T有大概1700万条数据。
USE [TEMP]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[up_statistic_amount_detail]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE Stat_amount_detail(ID INT NOT NULL,
TDate DATETIME,
SAmountEachDay INT NOT NULL,
CAmountEachDay INT NOT NULL,
QAmountEachDay INT NOT NULL,
TAmountEachDay INT NOT NULL,
PAmountEachDay INT NOT NULL);

-- Insert statements for procedure here
DECLARE @DATE DATETIME;
DECLARE @MAXDATE DATETIME;
/*RECORD ONCE ROUND COST TIME*/
DECLARE @PROCESSTIME DATETIME;
DECLARE @FINALDATE DATETIME;
DECLARE @TIMEEXP INT;
SET @MAXDATE='2007-7-17 23:59:59';
DECLARE @SAmount INT;
DECLARE @CAmount INT;
DECLARE @QAmount INT;
DECLARE @TAmount INT;
DECLARE @PAmount INT;
DECLARE @ID INT;

DECLARE ID_CURSOR CURSOR FOR
SELECT DISTINCT(ID) FROM T ORDER BY ID ASC;
OPEN ID_CURSOR;
FETCH NEXT FROM ID_CURSOR INTO @ID;
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @DATE='2007-2-7 0:00:00';
WHILE(@DATE<=@MAXDATE)
BEGIN
CREATE TABLE TL(
ID varchar(32) NOT NULL,
TID varchar(20) NOT NULL,
TDATE datetime NOT NULL
)
CREATE CLUSTERED INDEX temp_index_id ON TL(ID);
CREATE NONCLUSTERED INDEX temp_index_tdate ON TL(TDATE);
CREATE NONCLUSTERED INDEX temp_index_tid ON TL(TID);

SET @FINALDATE=DATEADD(DAY,1,@DATE);
SET @PROCESSTIME=GETDATE();
INSERT INTO TL SELECT ID,TID,TDATE FROM T WHERE ID=@ID AND TDATE BETWEEN @DATE AND @FINALDATE;
SELECT @SAmount=COUNT(ID) FROM TL;
/*SELECT @CAmount=COUNT(ID) FROM TL WHERE TID='20001';*/
SELECT @QueryAmount=COUNT(ID) FROM TL WHERE TID='20002';
SELECT @PwdAmount=COUNT(ID) FROM TL WHERE TID='20004';
SELECT @TransferAmount=COUNT(ID) FROM TL WHERE TID='20005';
SET @CAmount=@SAmount-@QAmount-@PAmount-@TAmount;
INSERT INTO Stat_amount_detail VALUES(@ID,@TDATE,@SAmount,@CAmount,@QAmount,@TAmount,@PAmount);
SET @DATE=DATEADD(DAY,1,@DATE);
DROP TABLE TL;
SELECT @TIMEEXP=DATEDIFF(MS,@PROCESSTIME,GETDATE());
PRINT('one round spend time:');
PRINT(@TIMEEXP);
END
FETCH NEXT FROM ID_CURSOR INTO @ID;
END
END
表T中有非聚集索引ID和非聚集索引TDATE,我测试了一下,大概跑完一个id需要12分钟。大家帮忙看看,什么原因,有没有什么提高效率的办法,谢谢!在线等!!!
...全文
125 点赞 收藏 12
写回复
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
vanjayhsu 2007-09-21
哦。。好的,谢谢啊!!!以后多联系啊。。。
回复
brother2605 2007-09-21
嗯。。。这个不错啊,跑出来一台大概也需要一分半钟,那460台???请问下,insert和update哪个快?

我是想建个新表,然后insert进去,里面本来没有数据的啊。。可以解释下这些语句么?学习了,谢谢啊。。。马上给分!
---------------------------------------
--取得所有的ID
SELECT DISTINCT ID INTO t1 FROM TL
--取得所选时间段的所有日期,包括没有记录的日期,如果不显示没数据的日期,则可省略
SELECT TOP (DATEDIFF(dd,@MINDATE,@MAXDATE)+1)ID=IDENTITY(INT,0,1),@MINDATE AS TDate INTO t2
FROM master.dbo.sysobjects
UPDATE t2 SET TDate=CONVERT(VARCHAR(10),DATEADD(dd,ID,@MINDATE),120)
--list 出所有的行:ID*日期
INSERT INTO Stat_amount_detail(ID,TDate)
SELECT A.ID,B.TDate
FROM t1 A CROSS JOIN t2 B
--按照 ID,TDATE,TID 将原来的记录进行初步汇总,这样可以提升下面那段UPDATE的速度
SELECT ID,TDATE,TID,COUNT(1)AS NUM
INTO t3
FROM TL
WHERE TDATE>=@MINDATE AND TDATE<=@MAXDATE
GROUP BY ID,TDATE,TID
--------------------------------------------------
你的这个需求没法直接用INSERT插入
回复
vanjayhsu 2007-09-21
嗯。。。这个不错啊,跑出来一台大概也需要一分半钟,那460台???请问下,insert和update哪个快?

我是想建个新表,然后insert进去,里面本来没有数据的啊。。可以解释下这些语句么?学习了,谢谢啊。。。马上给分!
SELECT DISTINCT ID INTO t1 FROM TL
SELECT TOP (DATEDIFF(dd,@MINDATE,@MAXDATE)+1)ID=IDENTITY(INT,0,1),@MINDATE AS TDate INTO t2
FROM master.dbo.sysobjects
UPDATE t2 SET TDate=CONVERT(VARCHAR(10),DATEADD(dd,ID,@MINDATE),120)

INSERT INTO Stat_amount_detail(ID,TDate)
SELECT A.ID,B.TDate
FROM t1 A CROSS JOIN t2 B

SELECT ID,TDATE,TID,COUNT(1)AS NUM
INTO t3
FROM TL
WHERE TDATE>=@MINDATE AND TDATE<=@MAXDATE
GROUP BY ID,TDATE,TID
回复
andy84 2007-09-21
mark 学习
回复
brother2605 2007-09-21
--再试试这个,再不快我就没法了
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[up_statistic_amount_detail]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE Stat_amount_detail(ID INT NOT NULL,
TDate DATETIME,
SAmountEachDay INT NOT NULL,
CAmountEachDay INT NOT NULL,
QAmountEachDay INT NOT NULL,
TAmountEachDay INT NOT NULL,
PAmountEachDay INT NOT NULL);
-- Insert statements for procedure here
DECLARE @MINDATE DATETIME;
DECLARE @MAXDATE DATETIME;
SET @MINDATE='2007-2-7 0:00:00';
SET @MAXDATE='2007-7-17 23:59:59';
SELECT DISTINCT ID INTO t1 FROM TL
SELECT TOP (DATEDIFF(dd,@MINDATE,@MAXDATE)+1)ID=IDENTITY(INT,0,1),@MINDATE AS TDate INTO t2
FROM master.dbo.sysobjects
UPDATE t2 SET TDate=CONVERT(VARCHAR(10),DATEADD(dd,ID,@MINDATE),120)

INSERT INTO Stat_amount_detail(ID,TDate)
SELECT A.ID,B.TDate
FROM t1 A CROSS JOIN t2 B

SELECT ID,TDATE,TID,COUNT(1)AS NUM
INTO t3
FROM TL
WHERE TDATE>=@MINDATE AND TDATE<=@MAXDATE
GROUP BY ID,TDATE,TID

UPDATE A
SET SAmountEachDay=(SELECT SUM(NUM)FROM t3 WHERE ID=A.ID AND TDATE=A.TDATE),
QAmountEachDay=(SELECT SUM(NUM)FROM t3 WHERE ID=A.ID AND TDATE=A.TDATE AND TID='20002'),
TAmountEachDay=(SELECT SUM(NUM)FROM t3 WHERE ID=A.ID AND TDATE=A.TDATE AND TID='20002'),
PAmountEachDay=(SELECT SUM(NUM)FROM t3 WHERE ID=A.ID AND TDATE=A.TDATE AND TID='20002')
FROM Stat_amount_detail A

UPDATE Stat_amount_detail
SET CAmountEachDay=SAmountEachDay-QAmountEachDay-TAmountEachDay-PAmountEachDay

--SELECT*FROM Stat_amount_detail

DROP TABLE t1
DROP TABLE t2
DROP TABLE t3
--DROP TABLE Stat_amount_detail
END
回复
brother2605 2007-09-21
高手再进来看看,还有没有更快的办法啊,楼上的尽管效率提高了,但跑出来一个ID大概需要12分钟,那460台需要。。。

时间太长了啊。。。有没有其它好的办法?谢谢了!!!急啊
--------------------------------------------------------------
晕,还是需要12分钟,那岂不是说没有提升,没道理啊,汗~~
回复
vanjayhsu 2007-09-21
高手再进来看看,还有没有更快的办法啊,楼上的尽管效率提高了,但跑出来一个ID大概需要12分钟,那460台需要。。。

时间太长了啊。。。有没有其它好的办法?谢谢了!!!急啊
回复
brother2605 2007-09-19
上面所用的TL表就是你那个1700W+ 的表,汗~~~~
我这没这么多数据,你测试一下看看多久能执行完。
不过应该至少能提高50~100倍的速度吧~_~
回复
brother2605 2007-09-19
--试试这个看能提升多少效率,PS:尽量不要用游标
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[up_statistic_amount_detail]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE Stat_amount_detail(ID INT NOT NULL,
TDate DATETIME,
SAmountEachDay INT NOT NULL,
CAmountEachDay INT NOT NULL,
QAmountEachDay INT NOT NULL,
TAmountEachDay INT NOT NULL,
PAmountEachDay INT NOT NULL);
-- Insert statements for procedure here
DECLARE @DATE DATETIME;
DECLARE @MAXDATE DATETIME;
SET @DATE='2007-2-7 0:00:00';
SET @MAXDATE='2007-7-17 23:59:59';
SELECT DISTINCT ID INTO t1 FROM TL
SELECT TOP (DATEDIFF(dd,@DATE,@MAXDATE)+1)ID=IDENTITY(INT,0,1),@DATE AS TDate INTO t2
FROM sysobjects
UPDATE t2 SET TDate=CONVERT(VARCHAR(10),DATEADD(dd,ID,@DATE),120)

INSERT INTO Stat_amount_detail(ID,TDate)
SELECT A.ID,B.TDate
FROM t1 A CROSS JOIN t2 B

UPDATE A
SET SAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0),
QAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0 AND TID='20002'),
TAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0 AND TID='20002'),
PAmountEachDay=(SELECT COUNT(1)FROM TL WHERE ID=A.ID AND DATEDIFF(dd,A.TDATE,TDATE)=0 AND TID='20002')
FROM Stat_amount_detail A
UPDATE Stat_amount_detail
SET CAmountEachDay=SAmountEachDay-QAmountEachDay-TAmountEachDay-PAmountEachDay

SELECT*FROM Stat_amount_detail

DROP TABLE t1
DROP TABLE t2
DROP TABLE Stat_amount_detail
END
回复
bcc1o 2007-09-19
表T有大概1700万条数据
。。。。日啊
回复
brother2605 2007-09-19
这样写循环不慢才是怪事。
回复
plsandslp 2007-09-19
统计ID不用写这么复杂吧,直接用条件过滤然后分组应该能统计不行吗?
回复
发动态
发帖子
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
社区公告
暂无公告