处理效率问题（高手进）

Question

  有这个一个成绩系统需要核算作业成绩。结构如下：成绩考核规则表：
-------------------------------------
考试科目         考核类型        作业套数      
大学语文         课程作业            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个小时左右（这还不是全部的数据）。请高手给出解决的方案。
   

Answer 1

我的小程序终于派上用场了，先把数据建起来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')

Answer 2

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

Answer 3

处理10000个学生需要1个小时左右（这还不是全部的数据）。 
--------------------------------
不知道具体的操作，假设用了80分钟，那么 80 * 60 / 10000 = 0.48
平均每个学生花不到0.5秒，看应用的需求和硬件的配置了，似乎也不是很慢。复杂的操作是需要时间的，数据库上可以做些优化，比如重建索引。另，在学生成绩上有null值不太好，对空值的处理比其他数据慢得多。
是否可以用 -1 代替，这样会快很多。但你的程序需要相应修改。

Answer 4

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

Answer 5

--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]
GOupdate 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)我现在就是这样做的，不知道是否正确？

Answer 6

不知道你的分级是干嘛?我的意思是这样:USE tempdb
GOSET 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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

知道2005有 TOP(a表.套数)，但现在我们用的是2000，我现在也改了我刚才的那个算法。insert into studentScore.dbo.HomeWorkScoreCountTemp(UserID,CourseID,cj)
select UserID,CourseID,cj from studentScore.dbo.HomeWorkScoreTemp a where a.cj > 0update 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

Answer 12

2000 可以考虑我的 update 方法

Answer 13

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

Answer 14

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

Answer 15

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