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

[vanjayhsu]

下面是我的存储过程，想取得每一个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分钟。大家帮忙看看，什么原因，有没有什么提高效率的办法，谢谢！在线等！！！

[plsandslp]

统计ID不用写这么复杂吧,直接用条件过滤然后分组应该能统计不行吗?

[brother2605]

这样写循环不慢才是怪事。

[bcc1o]

表T有大概1700万条数据
。。。。日啊

[brother2605]

--试试这个看能提升多少效率，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

[brother2605]

上面所用的TL表就是你那个1700W+ 的表，汗~~~~
我这没这么多数据，你测试一下看看多久能执行完。
不过应该至少能提高50~100倍的速度吧~_~

[vanjayhsu]

高手再进来看看，还有没有更快的办法啊，楼上的尽管效率提高了，但跑出来一个ID大概需要12分钟，那460台需要。。。

时间太长了啊。。。有没有其它好的办法?谢谢了！！！急啊

[brother2605]

高手再进来看看，还有没有更快的办法啊，楼上的尽管效率提高了，但跑出来一个ID大概需要12分钟，那460台需要。。。

时间太长了啊。。。有没有其它好的办法?谢谢了！！！急啊
--------------------------------------------------------------
晕，还是需要12分钟，那岂不是说没有提升，没道理啊，汗~~

[brother2605]

--再试试这个,再不快我就没法了
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

[andy84]

mark 学习

[vanjayhsu]

嗯。。。这个不错啊，跑出来一台大概也需要一分半钟，那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

[brother2605]

嗯。。。这个不错啊，跑出来一台大概也需要一分半钟，那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]

哦。。好的，谢谢啊！！！以后多联系啊。。。