处理效率问题（高手进）

[friendlyFour]

有这个一个成绩系统需要核算作业成绩。结构如下：

成绩考核规则表：
-------------------------------------
考试科目 考核类型 作业套数
大学语文 课程作业 3
大学英语 课程作业+课程论文 5
管理学 集中考试 null
会计 集中考试+课程作业 7
...
-------------------------------------

规则为：当考核类型中含有“课程作业”的考试科目需要核算作业成绩。不包含的不用核算作业成绩。
-------------------------------------------------------
学生作业表：
考试科目 作业成绩 作业名称 学生用户名
大学语文 90 大学语文第一次作业 friendly
大学语文 90 大学语文第二次作业 friendly
大学语文 100 大学语文第三次作业 friendly
大学语文 60 大学语文第四次作业 friendly
大学语文 0 大学语文第五次作业 friendly
大学英语 90 大学英语第一次作业 xxxxxx
大学英语 80 大学英语第二次作业 xxxxxx
大学英语 null 大学英语第三次作业 xxxxxx
...
-------------------------------------------------------

说明如下：学生friendly的大学语文第五次作业，作业成绩为0是实际作了作业但得了0分。而学生xxxxxx的大学英语第三次作业，作业成绩为null是没有做这次的作业（在实际业务中是没有这个数据的，这里只是表明有这样的情况。即：应作的作业没有做。）
规则为：按成绩合算规则中设置的作业套数，从学生已完成的作业中按成绩由高至低选取相对应套数的记录数。
【例】某考试科目，成绩合算规则中作业套数设置为3，该门课程共发布了7套作业，学生已经提交作业的套数为5，成绩分别为100、98、75、99、70，那么计算后的作业成绩就是（100+98+99）/3=99

现在问题就来了，需要核算的学生+考试科目+考核类型为“课程作业的”的数据量为3w左右，但学生的作业数每个考试计划为10w到20w条，并由于学生的考试科目的作业套数是不定数，不能直接top number后倒排成绩数据。现在查出要核算成绩的学生，用的是一个函数来处理，在函数中根据考试科目的作业套数来取最高那几套作业的成绩，处理10000个学生需要1个小时左右（这还不是全部的数据）。

请高手给出解决的方案。

[internetroot]

我的小程序终于派上用场了，先把数据建起来

INSERT INTO a VALUES ('大学语文','课程作业','3')
INSERT INTO a VALUES ('大学英语','课程作业+课程论文','5')
INSERT INTO a VALUES ('管理学','集中考试','0')
INSERT INTO a VALUES ('会计','集中考试+课程作业','7')

INSERT INTO b VALUES ('大学语文','90','大学语文第一次作业','friendly')
INSERT INTO b VALUES ('大学语文','90','大学语文第二次作业','friendly')
INSERT INTO b VALUES ('大学语文','100','大学语文第三次作业','friendly')
INSERT INTO b VALUES ('大学语文','60','大学语文第四次作业','friendly')
INSERT INTO b VALUES ('大学语文','0','大学语文第五次作业','friendly')
INSERT INTO b VALUES ('大学英语','90','大学英语第一次作业','xxxxxx')
INSERT INTO b VALUES ('大学英语','80','大学英语第二次作业','xxxxxx')
INSERT INTO b VALUES ('大学英语','0','大学英语第三次作业','xxxxxx')

[friendlyFour]

to:liangCK
学生没有提交相应的作业,则作业成绩合算后,将该学生该门考试科目的作业成绩记为0份，并标记为“缺考”

[friendlyFour]

to:dawugui
帮忙看看有什么办法吗？
我现在正在写其他的算法来加快速度。如可行发上来大家给看看。

[zjcxc]

用临时表或者辅助列来实现: 以"学生+考试科目+考核类型"分组, 按成绩由高到低排序, 生成一个连续的序号

然后最终算成绩的时候, 只需要前面算出的这个序号 <= 套数, 汇总后/套数 就可以算出成绩了

[-狙击手-]

钻钻

[friendlyFour]

--studentScore.dbo.HomeWorkScoreTemp为学生的所有作业
insert into studentScore.dbo.HomeWorkScoreCountTemp(UserID,CourseID,cj)
select UserID,CourseID,cj from studentScore.dbo.HomeWorkScoreTemp a where a.cj > 0

--处理成绩的中间表
CREATE TABLE [dbo].[HomeWorkScoreCountTemp] (
[UserID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CourseID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[cj] [float] NOT NULL ,
[iCount] [int] IDENTITY (1, 1) NOT NULL ,
[ScoreDiffStr] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

update studentScore.dbo.HomeWorkScoreCountTemp
set ScoreDiffStr = case when (cj >= 100) then 'W'
when (cj < 100 and cj >= 90) then 'V'
when (cj < 90 and cj >= 80) then 'U'
when (cj < 80 and cj >= 70) then 'T'
when (cj < 70 and cj >= 60) then 'S'
when (cj < 60 and cj >= 50) then 'R'
when (cj < 50 and cj >= 40) then 'Q'
when (cj < 40 and cj >= 30) then 'P'
when (cj < 30 and cj >= 20) then 'O'
when (cj < 20 and cj >= 10) then 'N'
when (cj < 10) then 'M' end + cast(cj as varchar(10)) + convert(varchar(50),10000000 + iCount)

--用studentScore.dbo.HomeWorkScoreCountTemp.[ScoreDiffStr]做实际的比较
select top 10000 a.UserID,a.CourseID,a.cj,a.ScoreDiffStr
from studentScore.dbo.HomeWorkScoreCountTemp a
where (select Count(*) from studentScore.dbo.HomeWorkScoreCountTemp b where a.UserID = b.UserID and a.CourseID = b.CourseID and a.ScoreDiffStr > ScoreDiffStr)
< (select HomeworkCount from studentScore.dbo.tb_e_ExamScoreRule where ExamMethodID = 3 and a.CourseID = CourseID)

我现在就是这样做的，不知道是否正确？

[zjcxc]

不知道你的分级是干嘛?

我的意思是这样:

USE tempdb
GO

SET NOCOUNT ON
-- 示例数据
IF OBJECT_ID('a') IS NOT NULL
	DROP TABLE a
CREATE TABLE a(
	考试科目 nvarchar(10),
	考试类型 nvarchar(10),
	作业套数 int)
INSERT INTO a VALUES ('大学语文','课程作业','3')
INSERT INTO a VALUES ('大学英语','课程作业+课程论文','5')
INSERT INTO a VALUES ('管理学','集中考试','0')
INSERT INTO a VALUES ('会计','集中考试+课程作业','7')

IF OBJECT_ID('b') IS NOT NULL
	DROP TABLE b
CREATE TABLE b(
	考试科目 nvarchar(10),
	作业成绩 int,
	作业名称 nvarchar(10),
	学生用户名 nvarchar(10))
INSERT INTO b VALUES ('大学语文','90','大学语文第一次作业','friendly')
INSERT INTO b VALUES ('大学语文','90','大学语文第二次作业','friendly')
INSERT INTO b VALUES ('大学语文','100','大学语文第三次作业','friendly')
INSERT INTO b VALUES ('大学语文','60','大学语文第四次作业','friendly')
INSERT INTO b VALUES ('大学语文','0','大学语文第五次作业','friendly')
INSERT INTO b VALUES ('大学英语','90','大学英语第一次作业','xxxxxx')
INSERT INTO b VALUES ('大学英语','80','大学英语第二次作业','xxxxxx')
GO

-- 计算(使用临时表的方式, 一般大数据量的话, 建议用辅助列)
-- a. 临时表
SELECT
	ID = IDENTITY(int, 1, 1),
	a.考试科目, a.考试类型, b.学生用户名,
	a.作业套数, b.作业成绩,
	sortid = 0
INTO #
FROM a, b
WHERE A.考试科目 = B.考试科目
ORDER BY a.考试科目, a.考试类型, b.学生用户名

ALTER TABLE # ADD
	PRIMARY KEY(
		id)

CREATE INDEX IX_ALL
	ON #(
		考试科目, 考试类型, 学生用户名, 作业成绩 DESC)

-- b. 生成序号
DECLARE
	@sortid int,
	@考试科目 nvarchar(10),
	@考试类型 nvarchar(10),
	@学生用户名 nvarchar(10)
UPDATE A SET
	@sortid = CASE
		WHEN @考试科目 = 考试科目 AND @考试类型 = 考试类型 AND @学生用户名 = 学生用户名
			THEN @sortid + 1
		ELSE 1 END,
	@考试科目 = 考试科目,
	@考试类型 = 考试类型,
	@学生用户名 = 学生用户名,
	sortid = @sortid
FROM # A WITH(INDEX(IX_ALL))

-- 计算成绩
SELECT 
	考试科目, 考试类型, 学生用户名,
	作业成绩 = SUM(作业成绩) / MAX(作业套数)
FROM #
WHERE sortid <= 作业套数
GROUP BY 考试科目, 考试类型, 学生用户名

DROP TABLE #
GO

-- 删除示例
DROP TABLE a, b

[zjcxc]

结果:

大学英语 课程作业+课程论文 xxxxxx 34
大学语文 课程作业 friendly 93

[zjcxc]

第1位的成绩是 34 , 因为只交了两次作业, 但规定要交 3 次, 所以 (90 + 80) / 3
不是这个规则的话, 你自己改

[zjcxc]

SELECT COUNT(*) 生成序号的方式效率太低
所以我才说要临时表或者辅助列, 这样借用索引就可以使用 UPDATE 直接生成序号

[zjcxc]

2005 就更好了, 可以直接 TOP(a表.套数), 也可以使用ROW_NUMBER() 直接排名, 所以 2005 不需要临时表或者辅助列就可以处理

[一者仁心]

不明白 你想要去几套的参数不是已经给顶了么 为什么还要说不能用top number的方法
用一个动态查询不可以么

[friendlyFour]

知道2005有 TOP(a表.套数)，但现在我们用的是2000，我现在也改了我刚才的那个算法。

insert into studentScore.dbo.HomeWorkScoreCountTemp(UserID,CourseID,cj)
select UserID,CourseID,cj from studentScore.dbo.HomeWorkScoreTemp a where a.cj > 0

update studentScore.dbo.HomeWorkScoreCountTemp
set ScoreDiffStr = cast(cast(cj as varchar(10))+cast(left(ABS(CHECKSUM(NEWID())),8) as varchar(10)) as bigint)

select a.UserID,a.CourseID,avg(a.cj)
from studentScore.dbo.HomeWorkScoreCountTemp a
where (select Count(*) from studentScore.dbo.HomeWorkScoreCountTemp b where a.UserID = b.UserID and a.CourseID = b.CourseID and a.ScoreDiffStr < ScoreDiffStr)
< (select HomeworkCount from studentScore.dbo.tb_e_ExamScoreRule where ExamMethodID = 3 and a.CourseID = CourseID)
group by a.UserID,a.CourseID
order by a.UserID,a.courseID

[zjcxc]

2000 可以考虑我的 update 方法

[w2jc]

处理10000个学生需要1个小时左右（这还不是全部的数据）。
--------------------------------
不知道具体的操作，假设用了80分钟，那么 80 * 60 / 10000 = 0.48
平均每个学生花不到0.5秒，看应用的需求和硬件的配置了，似乎也不是很慢。

复杂的操作是需要时间的，数据库上可以做些优化，比如重建索引。

另，在学生成绩上有null值不太好，对空值的处理比其他数据慢得多。
是否可以用 -1 代替，这样会快很多。但你的程序需要相应修改。

[dawugui]

我看懂了一点就是要求比较多.

[fengjijia]

效率太高了，老大就是老大，向邹建学习ing...

[friendlyFour]

ALTER Proc dbo.Sp_ExamHomeworkCount
(
@BatchID varchar(50),
@LastOperator varchar(50)
)
as
begin
SET NOCOUNT ON

/*变量声明*/
Declare @错误号 int,
@错误信息 varchar(8000),
@影响行数 int,
@临时ID int,
@事务已启动 bit,
@当前时间 datetime

Set @当前时间=getdate()
Set @事务已启动=0
--end
/*临时数据产生以及相关准备中*/
TRUNCATE TABLE studentScore.dbo.NotSubmitHomeworkTemp

TRUNCATE TABLE [studentscore].[dbo].[HomeWorkScoreCountTemp]

TRUNCATE TABLE [studentscore].[dbo].[HomeWorkScoreTemp]

TRUNCATE TABLE [studentscore].[dbo].[ExamStuBookingAndExamScoreRuleTemp]

TRUNCATE TABLE [studentscore].[dbo].[TotalHomeWorkScoreTemp]
--清除作业核算所用到临时物理表中的数据并创建的索引。
-----*******************************begin****************************************************************************

IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[HomeWorkScoreCountTemp]') AND name = N'index_HomeWorkScoreCountTemp')
DROP INDEX [dbo].[HomeWorkScoreCountTemp].[index_HomeWorkScoreCountTemp]

CREATE NONCLUSTERED INDEX index_HomeWorkScoreCountTemp ON [studentscore].[dbo].[HomeWorkScoreCountTemp]
(
[UserID] ASC,
[CourseID] ASC
) ON [PRIMARY]



IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[HomeWorkScoreTemp]') AND name = N'index_HomeWorkScoreTemp')
DROP INDEX [dbo].[HomeWorkScoreTemp].[index_HomeWorkScoreTemp]

CREATE NONCLUSTERED INDEX index_HomeWorkScoreTemp ON [studentscore].[dbo].[HomeWorkScoreTemp]
(
[UserID] ASC,
[CourseID] ASC
) ON [PRIMARY]


IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[ExamStuBookingAndExamScoreRuleTemp]') AND name = N'index_ExamStuBookingAndExamScoreRuleTemp')
DROP INDEX [dbo].[ExamStuBookingAndExamScoreRuleTemp].[index_ExamStuBookingAndExamScoreRuleTemp]

CREATE NONCLUSTERED INDEX index_ExamStuBookingAndExamScoreRuleTemp ON [studentscore].[dbo].[ExamStuBookingAndExamScoreRuleTemp]
(
[UserID] ASC,
[CourseID] ASC
) ON [PRIMARY]


IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[TotalHomeWorkScoreTemp]') AND name = N'index_TotalHomeWorkScoreTemp')
DROP INDEX [dbo].[TotalHomeWorkScoreTemp].[index_TotalHomeWorkScoreTemp]

CREATE NONCLUSTERED INDEX index_TotalHomeWorkScoreTemp ON [studentscore].[dbo].[TotalHomeWorkScoreTemp]
(
[UserID] ASC,
[CourseID] ASC
) ON [PRIMARY]


--*******************************end****************************************************************************

declare @HomeworkEndTime varchar(50)

select @HomeworkEndTime=HomeworkEndTime from studentExam.dbo.tb_e_ExamBatch where BatchID = @BatchID

--已预约所选考试计划的在籍学生的考试科目（考核方式包括“作业”）
insert into studentScore.dbo.ExamStuBookingAndExamScoreRuleTemp(CourseID,UserID)
select a.CourseID,b.UserID
from studentScore.dbo.Vw_ExamScoreRule_List a
inner join studentExam.dbo.tb_e_ExamstuBooking b on a.CourseAttrID = b.CourseAttrID and a.BatchID = b.BatchID
where a.BatchID = @BatchID and a.ExamMethodID = 3

--取得作业信息
insert into studentScore.dbo.HomeWorkScoreTemp(cj,UserID,CourseID)
Select max(convert(float,a.cj)) cj,
a.UserID,right(a.CourseID,6) CourseID
From student.dbo.Clerk_kscj a
inner join studentScore.dbo.ExamStuBookingAndExamScoreRuleTemp b on a.UserID = b.UserID and right(a.CourseID,6) = b.CourseID
Where a.Clerk_ks_status=1 and a.Status=1
And a.Clerk_Ks_btime<=@HomeworkEndTime
And a.cj is Not null
group by a.tk_cl_id,a.UserID,right(a.CourseID,6)

insert into studentScore.dbo.NotSubmitHomeworkTemp(BatchID,UserID,CourseID,LastOperator,HomeWorkScore,HomeWorkScoreFlagID)
select distinct @BatchID as BatchID,a.UserID,a.CourseID,@LastOperator as LastOperator,0 as cj,3 as HomeWorkScoreFlagID
from studentScore.dbo.ExamStuBookingAndExamScoreRuleTemp a
left join studentScore.dbo.HomeWorkScoreTemp b on a.UserID = b.UserID and a.CourseID = b.CourseID
where b.UserID is null and b.CourseID is null


--按成绩合算规则中设置的作业套数，从学生已完成的作业中按成绩由高至低选取，并核算出作业成绩后更新总成绩表中的分项作业成绩。
------------------------------------**Begin**------------------------------------------------------------------
insert into studentScore.dbo.HomeWorkScoreCountTemp(UserID,CourseID,cj)
select UserID,CourseID,cj from studentScore.dbo.HomeWorkScoreTemp a where a.cj > 0

update studentScore.dbo.HomeWorkScoreCountTemp
set ScoreDiffStr = cast(cast(cj as varchar(10))+cast(left(ABS(CHECKSUM(NEWID())),6) as varchar(10))+cast(left(ABS(CHECKSUM(NEWID())),6) as varchar(10)) as bigint)

insert into studentScore.dbo.TotalHomeWorkScoreTemp(UserID,CourseID,cj)
select a.UserID,a.CourseID,sum(a.cj)/b.HomeworkCount as cj
from studentScore.dbo.HomeWorkScoreCountTemp a
inner join studentScore.dbo.tb_e_ExamScoreRule b on b.ExamMethodID = 3 and a.CourseID = b.CourseID
where (
select Count(*) from studentScore.dbo.HomeWorkScoreCountTemp b
where a.UserID = b.UserID and a.CourseID = b.CourseID and a.ScoreDiffStr < ScoreDiffStr
) < b.HomeworkCount
group by a.UserID,a.courseID,b.HomeworkCount



Set @事务已启动=1
/*设置事务的隔绝级别：其他的进程只读*/
Set Transaction Isolation level repeatable Read

/*开始事务*/
BEGIN Tran

--写入只有作业而无卷面考试的课程成绩,并若在作业提交截止时间之前,学生没有提交相应的作业,则作业成绩合算后,将该学生该门考试科目的作业成绩记为0份，并标记为“缺考”
insert into tb_e_TotalScore(BatchID,UserID,CourseID,LastOperator,HomeWorkScore,HomeWorkScoreFlagID)
select aa.BatchID,aa.UserID,aa.CourseID,aa.LastOperator,aa.HomeWorkScore,aa.HomeWorkScoreFlagID from studentScore.dbo.NotSubmitHomeworkTemp aa
left join studentScore.dbo.tb_e_TotalScore bb on aa.UserID = bb.UserID and aa.CourseID = bb.CourseID and bb.BatchID = @BatchID
where bb.UserID is null and bb.CourseID is null

/*判断模块*/
if @@error>0
BEGIN
Select @错误号=50002,@错误信息='写入只有作业而无卷面考试的课程成绩时出错'
Goto ErrCl
end


--更新成绩
update tb_e_TotalScore SET HomeWorkScore = convert(decimal(4,1),b.cj),HomeWorkScoreFlagID = 0,
LastOperator = @LastOperator,LastOperationTime = getdate()
from studentScore.dbo.tb_e_TotalScore a
inner join studentScore.dbo.TotalHomeWorkScoreTemp b on a.UserID = b.UserID and a.CourseID = b.CourseID
where a.BatchID = @BatchID and a.IsAllowTotalScoreSum = 1

/*判断模块*/
if @@error>0
BEGIN
Select @错误号=50001,@错误信息='合算作业成绩时出错'
Goto ErrCl
end
------------------------------------**End**---------------------------------------------------------------------

--若在作业提交截止时间之前,学生没有提交相应的作业,则作业成绩合算后,将该学生该门考试科目的作业成绩记为0份，并标记为“缺考”
update tb_e_TotalScore SET HomeWorkScore = 0 ,HomeWorkScoreFlagID = 3 ,LastOperator = @LastOperator, LastOperationTime = getdate()
from tb_e_TotalScore a
left join TotalHomeWorkScoreTemp b on a.UserID = b.UserID and a.CourseID = b.CourseID
where a.BatchID = @BatchID and b.UserID is null and b.CourseID is null and a.IsAllowTotalScoreSum = 1
/*判断模块*/
if @@error>0
BEGIN
Select @错误号=50005,@错误信息='合算作业成绩时出错'
Goto ErrCl
end

update studentExam.dbo.tb_e_ExamBatch set IsHomeworkAccount = 1,IsHomeworkAccountTime = getdate()
where BatchID = @BatchID

/*判断模块*/
if @@error>0
BEGIN
Select @错误号=50003,@错误信息='更新考试计划时出错'
Goto ErrCl
end

/*提交事务*/
COMMIT TRANSACTION
TRUNCATE TABLE studentScore.dbo.tb_b_Job
Return
ErrCL:
TRUNCATE TABLE studentScore.dbo.tb_b_Job
/*如果事务已经启动，则回滚事务*/
if @事务已启动=1 rollback transaction
/*触发SQL自定义错误*/
Raiserror @错误号 @错误信息
/*返回*/
Return
end

[friendlyFour]

以上是我的最终解决Proc ，由于字数有限去掉了一些东西，但核心就是以上的内容了。感谢老大的支持。
明天结贴！！

[honghuali]

恩
真强！