谁能帮我优化一下？最好写明为什么会运行速度如此慢？

[Calian]

select stmstno,stname,stextend2,ststudytype,语文
,(select count(*)+1 from dmstm
where stmsubject='语文'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,4)=substring(Score.stmstno,2,4)
and stmsm>Score.语文) as 语文班级
,(select count(*)+1 from dmstm
where stmsubject='语文'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,2)='06'
and stmsm>Score.语文) as 语文年级
,数学
,(select count(*)+1 from dmstm
where stmsubject='数学'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,4)=substring(Score.stmstno,2,4)
and stmsm>Score.数学) as 数学班级
,(select count(*)+1 from dmstm
where stmsubject='数学'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,2)='06'
and stmsm>Score.数学) as 数学年级
,英语
,(select count(*)+1 from dmstm
where stmsubject='英语'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,4)=substring(Score.stmstno,2,4)
and stmsm>Score.英语) as 英语班级
,(select count(*)+1 from dmstm
where stmsubject='英语'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,2)='06'
and stmsm>Score.英语) as 英语年级
,物理
,(select count(*)+1 from dmstm
where stmsubject='物理'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,4)=substring(Score.stmstno,2,4)
and stmsm>Score.物理) as 物理班级
,(select count(*)+1 from dmstm
where stmsubject='物理'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,2)='06'
and stmsm>Score.物理) as 物理年级
,化学
,(select count(*)+1 from dmstm
where stmsubject='化学'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,4)=substring(Score.stmstno,2,4)
and stmsm>Score.化学) as 化学班级
,(select count(*)+1 from dmstm
where stmsubject='化学'
and stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,2)='06'
and stmsm>Score.化学) as 化学年级
,三门总分
,八门总分 into temptable
from
(select *
,(语文+数学+英语) as 三门总分
,(语文+数学+英语+物理+化学) as 八门总分
from(
select stmstno
,stextend2
,stname
,isnull(ststudytype,0) as ststudytype
,max (case stmsubject when '语文' then stmsm else '' end) as 语文
,max (case stmsubject when '数学' then stmsm else '' end) as 数学
,max (case stmsubject when '英语' then stmsm else '' end) as 英语
,max (case stmsubject when '物理' then stmsm else '' end) as 物理
,max (case stmsubject when '化学' then stmsm else '' end) as 化学
from dmstm,dmst
where stmtype='测验2'
and stmyear='20062007'
and stmpd='第一学期'
and substring(stmstno,2,2)='06'
and stmstno=stno
group by stmstno,stname,ststudytype,stextend2)aa)Score
select
(select count(*)+1 from temptable
where 三门总分>b.三门总分) as 三门名次
,(select count(*)+1 from temptable
where 八门总分>b.八门总分) as 八门名次
,case ststudytype when '0' then '' else '*' end as studytype
,* into temptable1
from temptable b
select * from temptable1
order by stmstno

[Calian]

DMST表：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DMST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DMST]
GO

CREATE TABLE [dbo].[DMST] (
[STID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STGRADE] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[STNO] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STCLASSNAME] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STCLASSNO] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STNAME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[STOLDNAME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[STENGNAME] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[STSEX] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
[STBIRTH] [datetime] NULL ,
[STHUKOU] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[STNATION] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
[STHOMETOWN] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
[STPOLITICS] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[STJOINPOLITICS] [datetime] NULL ,
[STCOMESCHOOL] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[STINCLASSNO] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STSTATUSFLAG] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STSTUDYTYPE] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STUNITENO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[STHUKOUTYPE] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STLODGING] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STRETURN] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STADDRESS] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[STDOORNO] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[STTEL] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[STHEALTH] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STPOSTNO] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STHOMEeconomy] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STSMS] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[STEMAIL] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STPICTURE] [image] NULL ,
[STHERO] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STIDENTITYCARDNO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[STBEFOREEDUCATE] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STPOLICE] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[STSTREET] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[STJOB] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[STDEPART] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STCARDID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[STGRADETYPE] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[STXUEJI] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[STSCHNAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STEXTEND1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STEXTEND2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STEXTEND3] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STEXTEND4] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STEXTEND5] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STEXTEND6] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STINSERTID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STINSERTTIME] [datetime] NULL ,
[STUPDATEID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STUPDATETIME] [datetime] NULL ,
[STUpVer] [int] NULL ,
[STUpTime] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

[Calian]

DMSTM表：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DMSTM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DMSTM]
GO

CREATE TABLE [dbo].[DMSTM] (
[STMSTNO] [varchar] (7) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STMSUBJECT] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STMTYPE] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STMYEAR] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STMPD] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[STMDATE] [datetime] NOT NULL ,
[STMSM] [float] NULL ,
[STMSENDFLG] [int] NULL ,
[STMEXTEND1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STMEXTEND2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STMINSERTID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STMINSERTTIME] [datetime] NULL ,
[STMUPDATEID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[STMUPDATETIME] [datetime] NULL ,
[STMUpVer] [int] NULL ,
[STMUpTime] [datetime] NULL
) ON [PRIMARY]
GO

[Calian]

DMST:
学生卡号 STID
常年年级 STGRADE
学生学号 STNO
班级名 STCLASSNAME
班级编号 STCLASSNO
学生姓名 STNAME
STOLDNAME
STENGNAME
性别 STSEX
生日 STBIRTH
户口 STHUKOU
民族 STNATION
籍贯 STHOMETOWN
STPOLITICS
STJOINPOLITICS
进校年份 STCOMESCHOOL
STINCLASSNO
STSTATUSFLAG
学籍状况 STSTUDYTYPE
STUNITENO
户口类型 STHUKOUTYPE
STLODGING
STRETURN
街道 STADDRESS
门牌 STDOORNO
电话 STTEL
STHEALTH
邮政编码 STPOSTNO
STHOMEeconomy
STSMS
电子邮件 STEMAIL
照片 STPICTURE
STHERO
STIDENTITYCARDNO
STBEFOREEDUCATE
警局 STPOLICE
街道 STSTREET
其余的在这里面基本没有什么用


DMSTM:
学号 STMSTNO
科目 STMSUBJECT
考试类型 STMTYPE
学年 STMYEAR
学期 STMPD
日期 STMDATE
成绩 STMSM
STMSENDFLG
成绩 STMEXTEND1
小分录入时题目数量 STMEXTEND2
STMINSERTID
STMINSERTTIME
STMUPDATEID
STMUPDATETIME
STMUpVer
STMUpTime

非常感谢楼上各位
分一定给！！！

[xyxfly]

太乱了，楼主可以用语言描述一下需求 ^0^

ps:这种问题发到技术区吧

[dashi]

太乱了，楼主可以用语言描述一下需求 ^0^ ps:这种问题发到技术区吧

[rea1gz]

写这么长当然效率低了，呵呵

[rea1gz]

先说原因吧
子查询、嵌套查询太多，是主要原因

不如说下原表结构，看看有没有效率点的写法

[rea1gz]

既然用到temptable，建议这样分步
1、只行列转换，写原始的成绩到temptable
2、更新三门总分、八门总分
3、依次更新各门排名
4、更新总分排名

这样保证每一步都至多一个子查询，步数多了，但是效率高

思路而已，参考

[日总是我哥]

先说原因吧 子查询、嵌套查询太多，是主要原因 不如说下原表结构，看看有没有效率点的写法

[graybaby]

看的偶头晕

[pdgzzgx]

先说原因吧
子查询、嵌套查询太多，是主要原因

不如说下原表结构，看看有没有效率点的写法