sql优化面试题（合集12篇）

“Eternalreturn”通过精心收集，向本站投稿了12篇sql优化面试题，下面是小编为大家整理后的sql优化面试题，如果喜欢可以分享给身边的朋友喔!

sql优化面试题

篇1：sql优化面试题

问题：

1、查询“001”课程比“002”课程成绩高的所有学生的学号;

select a.S# from (select s#,score from SC where C#='001') a,(select s#,score

from SC where C#='002') b

where a.score>b.score and a.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩;

select S#,avg(score)

from sc

group by S# having avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩;

select Student.S#,Student.Sname,count(SC.C#),sum(score)

from Student left Outer join SC on Student.S#=SC.S#

group by Student.S#,Sname

4、查询姓“李”的老师的个数;

select count(distinct(Tname))

from Teacher

where Tname like '李%';

5、查询没学过“叶平”老师课的同学的学号、姓名;

select Student.S#,Student.Sname

from Student

where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

select S#,Sname

from Student

where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname='叶平'));

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2

from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2

9、查询所有课程成绩小于60分的同学的学号、姓名;

select S#,Sname

from Student

where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

10、查询没有学全所有课的同学的学号、姓名;

select Student.S#,Student.Sname

from Student,SC

where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;

select distinct SC.S#,Sname

from Student,SC

where Student.S#=SC.S# and C# in (select C# from SC where S#='001');

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

SC set score=(select avg(SC_2.score)

from SC SC_2

where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;

select S# from SC where C# in (select C# from SC where S#='1002')

group by S# having count(*)=(select count(*) from SC where S#='1002');

15、删除学习“叶平”老师课的SC表记录;

Delect SC

from course ,Teacher

where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、

号课的平均成绩;

Insert SC select S#,'002',(Select avg(score)

from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

SELECT S# as 学生ID

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理

,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语

,COUNT(*) AS 有效课程数, AVG(t.score) AS平均成绩

FROM SC AS t

GROUP BY S#

ORDER BY avg(t.score)

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分

FROM SC L ,SC AS R

WHERE L.C# = R.C# and

L.score = (SELECT MAX(IL.score)

FROM SC AS IL,Student AS IM

WHERE L.C# = IL.C# and IM.S#=IL.S#

GROUP BY IL.C#)

AND

R.Score = (SELECT MIN(IR.score)

FROM SC AS IR

WHERE R.C# = IR.C#

GROUP BY IR.C#

);

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS平均成绩

,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数

FROM SC T,Course

where t.C#=course.C#

GROUP BY t.C#

ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

20、查询如下课程平均成绩和及格率的百分数(用“1行”显示): 企业管理(001)，马克思(002)，OO&UML (003)，数据库(004)

SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分

,100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数

,SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分

,100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数

,SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分

,100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数

,SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分

,100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数

FROM SC

21、查询不同老师所教不同课程平均分从高到低显示

SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS平均成绩

FROM SC AS T,Course AS C ,Teacher AS Z

where T.C#=C.C# and C.T#=Z.T#

GROUP BY C.C#

ORDER BY AVG(Score) DESC

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理(001)，马克思(002)，UML (003)，数据库(004)

[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

SELECT DISTINCT top 3

SC.S# As 学生学号,

Student.Sname AS 学生姓名 ,

T1.score AS 企业管理,

T2.score AS 马克思,

T3.score AS UML,

T4.score AS 数据库,

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分

FROM Student,SC LEFT JOIN SC AS T1

ON SC.S# = T1.S# AND T1.C# = '001'

LEFT JOIN SC AS T2

ON SC.S# = T2.S# AND T2.C# = '002'

LEFT JOIN SC AS T3

ON SC.S# = T3.S# AND T3.C# = '003'

LEFT JOIN SC AS T4

ON SC.S# = T4.S# AND T4.C# = '004'

WHERE student.S#=SC.S# and

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)

NOT IN

(SELECT

DISTINCT

TOP 15 WITH TIES

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)

FROM sc

LEFT JOIN sc AS T1

ON sc.S# = T1.S# AND T1.C# = 'k1'

LEFT JOIN sc AS T2

ON sc.S# = T2.S# AND T2.C# = 'k2'

LEFT JOIN sc AS T3

ON sc.S# = T3.S# AND T3.C# = 'k3'

LEFT JOIN sc AS T4

ON sc.S# = T4.S# AND T4.C# = 'k4'

ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT SC.C# as 课程ID, Cname as 课程名称

,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]

,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]

,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]

,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]

FROM SC,Course

where SC.C#=Course.C#

GROUP BY SC.C#,Cname;

24、查询学生平均成绩及其名次

SELECT 1+(SELECT COUNT( distinct平均成绩)

FROM (SELECT S#,AVG(score) AS平均成绩

FROM SC

GROUP BY S#

) AS T1

WHERE平均成绩 > T2.平均成绩) as 名次,

S# as 学生学号,平均成绩

FROM (SELECT S#,AVG(score)平均成绩

FROM SC

GROUP BY S#

) AS T2

ORDER BY平均成绩 desc;

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

FROM SC t1

WHERE score IN (SELECT TOP 3 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC

)

ORDER BY t1.C#;

26、查询每门课程被选修的学生数

select c#,count(S#) from sc group by C#;

27、查询出只选修了一门课程的全部学生的学号和姓名

select SC.S#,Student.Sname,count(C#) AS 选课数

from SC ,Student

where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;

28、查询男生、女生人数

Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';

Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女';

29、查询姓“张”的学生名单

SELECT Sname FROM Student WHERE Sname like '张%';

30、查询同名同性学生名单，并统计同名人数

select Sname,count(*) from Student group by Sname having count(*)>1;;

31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age

from student

where CONVERT(11),DATEPART(year,Sage))='1981';

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;

33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select Sname,SC.S# ,avg(score)

from Student,SC

where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;

34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数

Select Sname,isnull(score,0)

from Student,SC,Course

where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname='数据库'and score <60;

35、查询所有学生的选课情况;

SELECT SC.S#,SC.C#,Sname,Cname

FROM SC,Student,Course

where SC.S#=Student.S# and SC.C#=Course.C# ;

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

SELECT distinct student.S#,student.Sname,SC.C#,SC.score

FROM student,Sc

WHERE SC.score>=70 AND SC.S#=student.S#;

37、查询不及格的课程，并按课程号从大到小排列

select c# from sc where scor e <60 order by C# ;

38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;

select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';

39、求选了课程的学生人数

select count(*) from sc;

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select Student.Sname,score

from Student,SC,Course C,Teacher

where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );

41、查询各个课程及相应的选修人数

select count(*) from sc group by C#;

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# B.C# ;

43、查询每门功成绩最好的前两名

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

FROM SC t1

WHERE score IN (SELECT TOP 2 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC

)

ORDER BY t1.C#;

44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列

select C# as 课程号,count(*) as 人数

from sc

group by C#

order by count(*) desc,c#

45、检索至少选修两门课程的学生学号

select S#

from sc

group by s#

having count(*) > = 2

46、查询全部学生都选修的课程的课程号和课程名

select C#,Cname

from Course

where C# in (select c# from sc group by c#)

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');

48、查询两门以上不及格课程的同学的学号及其平均成绩

select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;

49、检索“004”课程分数小于60，按分数降序排列的同学学号

select S# from SC where C#='004'and score <60 order by score desc;

50、删除“002”同学的“001”课程的成绩

from Sc where S#='001'and C#='001';

[sql优化面试题]

篇2：优化SQL一条

昨天大半夜接到一条SQL，反应说很慢，我非常愤怒，经过询问，三个月才需要跑这个SQL一次，你tm非要在马上法定节假日了跑它吗？

SQL如下（巨长无比）

select * from table(dbms_xplan.display_cursor(lower('0ah5a8dbk28fh'))); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 0ah5a8dbk28fh, child number 0 ------------------------------------- INSERT INTO END_TRANS_ACCOUNT( BRANCH_NO ,COST_CENTER ,CNTR_NO ,IPSN_NO ,POL_CODE ,ACCOUNT_NO ,I_INFO_GROUP_FLAG ,SG_NO ,CURRENCY_CODE ,VALID_DATE ,CNTR_STAT ,INVALID_DATE ,ENDORSE_STAT ,REDUCE_START_DATE ,GROUP_FLAG ,SET_STAT ,FREEZE_STAT ,DEAD_DATE ,DEAD_CODE ,MED_DATE ,ADJ_STOP_CAUSE ,ADJ_STOP_DATE ,DUTY_STOP_DATE ,IPSN_AGE ,IPSN_SEX ,IPSN_NUM ,I_INFO_PAY_ITRVL ,I_INFO_PAY_DUR ,I_INFO_PREM ,I_INFO_INSUR_DUR ,FACE_AMNT ,EXPIRY_AMNT ,SUM_ASS_AMNT ,FEE_ITRVL ,REV_GRNT ,REV_GRNT_RATE ,RIDER_INFO ,RIDER1_CNTR_NO ,RIDER2_CNTR_NO ,RIDER3_CNTR_NO ,RIDER1_SA ,RIDER2_SA ,ACCOUNT_V_B ,ACCOUNT_V_E ,BONUS_RATIO ,CLAIM_FLAG ,PREM_PAID_NUM ,LAST_PREM_DATE ,LAST_PREM ,YEAR_PREM_SG ,YEAR_PREM_RG ,FTP_PREM ,SUM_PREM ,OCC_AMNT ,PALBD_AMNT ,FEE_INCOME ,FEE_INCOME_TOTAL ,BONUS_PERSISTENCY ,BONUS_PERSISTENCY_TOTAL ,BONUS_CREDITED ,INV_GRNT_RATE ,BONUS_REV_ITRVL ,BONUS_SUM ,BONUS_AMNT ,PAID_AMNT ,PAID_ANN_AMNT ,PAID_DEATH_AMNT ,PAID_DIS_AMNT ,PAID_MED_AMNT ,PAID_MED_REIMB ,PAID_EXP_AMNT ,PAID_GRANT_AMNT , Plan hash value: 2746060288 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name| Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 2581K(100)| | | 1 | LOAD TABLE CONVENTIONAL| | | | || | | 2 | UNION-ALL | | | | || | | 3 | NESTED LOOPS OUTER | | 8 | 1264 | | 24 (17)| 00:00:01 | |* 4 | HASH JOIN OUTER | | 8 | 912 | | 24 (17)| 00:00:01 | |* 5 |HASH JOIN OUTER | | 8 | 840 | | 20 (15)| 00:00:01 | |* 6 | HASH JOIN OUTER | | 8 | 744 | | 17 (18)| 00:00:01 | |* 7 | HASH JOIN OUTER | | 8 | 648 | | 13 (16)| 00:00:01 | |* 8 | HASH JOIN OUTER | | 8 | 552 | | 10 (20)| 00:00:01 | | 9 | MERGE JOIN OUTER | | 8 | 456 | | 6 (17)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID | PRE_INSUR_APPL | 8 | 360 | | 2 (0)| 00:00:01 | | 11 |INDEX FULL SCAN | PRIMARY_KEY | 8 | | | 1 (0)| 00:00:01 | |* 12 | SORT JOIN | | 8 | 96 | | 4 (25)| 00:00:01 | | 13 |TABLE ACCESS FULL | TMP_FACE_AMNT_APPLID | 8 | 96 | | 3 (0)| 00:00:01 | | 14 | TABLE ACCESS FULL | TMP_ACCOUNT_V_E_APPLID| 8 | 96 | | 3 (0)| 00:00:01 | | 15 | TABLE ACCESS FULL | TMP_YEAR_PREM_RG_APPLID | 8 | 96 | | 3 (0)| 00:00:01 | | 16 | TABLE ACCESS FULL| TMP_YEAR_PREM_SG_APPLID | 8 | 96 | | 3 (0)| 00:00:01 | | 17 | TABLE ACCESS FULL | TMP_SUM_PRE_APPLID | 8 | 96 | | 3 (0)| 00:00:01 | | 18 |TABLE ACCESS FULL | TMP_INSUR_DUR_APPLID | 8 | 72 | | 3 (0)| 00:00:01 | | 19 | TABLE ACCESS BY INDEX ROWID | TMP_COST_CENTER_CNTRNO| 1 | 44 | | 0 (0)| | |* 20 |INDEX UNIQUE SCAN | KEY_COST_CENTER_CNTRNO| 1 | | | 0 (0)| | |* 21 | HASH JOIN RIGHT OUTER| | 4326K| 1390M| | 613K (2)| 02:02:48 | | 22 | TABLE ACCESS FULL | TMP_COST_CENTER_CNTRNO| 1877 | 82588 | | 5 (0)| 00:00:01 | |* 23 | HASH JOIN RIGHT OUTER | | 4326K| 1209M| | 613K (2)| 02:02:48 | | 24 |TABLE ACCESS FULL | TMP_PAID_MED_AMNT_CNTRNO | 1872 | 50544 | | 5 (0)| 00:00:01 | |* 25 |HASH JOIN RIGHT OUTER | | 4326K| 1097M| | 613K (2)| 02:02:47 | | 26 | TABLE ACCESS FULL | TMP_INSUR_DUR_CNTRNO | 1862 | 48412 | | 5 (0)| 00:00:01 | |* 27 | HASH JOIN RIGHT OUTER | | 4326K| 990M| | 613K (2)| 02:02:46 | | 28 | TABLE ACCESS FULL| TMP_MEDDATE_CLAIMFLAG_ACCID | 1 | 35 | | 2 (0)| 00:00:01 | |* 29 | HASH JOIN RIGHT OUTER | | 4326K| 845M| 165M| 613K (2)| 02:02:46 | | 30 | TABLE ACCESS FULL | TMP_ACCOUNT_V_B_ACCID | 8653K| 66M| | 3616 (5)| 00:00:44 | |* 31 | HASH JOIN RIGHT OUTER | | 4326K| 812M| | 558K (2)| 01:51:48 | | 32 | TABLE ACCESS FULL | TMP_ACCOUNT_V_E_ACCID | 14 | 112 | | 3 (0)| 00:00:01 | |* 33 | HASH JOIN RIGHT OUTER| | 4326K| 779M| 165M| 558K (2)| 01:51:47 | | 34 | TABLE ACCESS FULL | TMP_FEE_INCOME_ACCID | 8653K| 66M| | 3728 (5)| 00:00:45 | |* 35 | HASH JOIN RIGHT OUTER | | 4326K| 746M| 139M| 507K (2)| 01:41:27 | | 36 |TABLE ACCESS FULL | TMP_FUND_AVRG1_ACCID | 7337K| 55M| | 3199 (5)| 00:00:39 | |* 37 |HASH JOIN RIGHT OUTER | | 4326K| 713M| 165M| 458K (2)| 01:31:48 | | 38 | TABLE ACCESS FULL | TMP_FUND_AVRGS_ACCID | 8653K| 66M| | 3756 (5)| 00:00:46 | |* 39 | HASH JOIN RIGHT OUTER | | 4326K| 680M| 165M| 410K (2)| 01:22:07 | | 40 | TABLE ACCESS FULL| TMP_PAID_AMNT_ACCID | 8653K| 66M| | 3728 (5)| 00:00:45 | |* 41 | HASH JOIN RIGHT OUTER | | 4326K| 647M| 165M| 363K (2)| 01:12:46 | | 42 | TABLE ACCESS FULL | TMP_SUM_PRE_ACCID | 8653K| 66M| | 3728 (5)| 00:00:45 | |* 43 | HASH JOIN RIGHT OUTER | | 4326K| 614M| 165M| 318K (2)| 01:03:45 | | 44 | TABLE ACCESS FULL | TMP_YEAR_PREM_RG_ACCID| 8653K| 66M| | 3728 (5)| 00:00:45 | |* 45 | HASH JOIN RIGHT OUTER| | 4326K| 581M| 165M| 275K (2)| 00:55:03 | | 46 | TABLE ACCESS FULL | TMP_YEAR_PREM_SG_ACCID| 8653K| 66M| | 3644 (5)| 00:00:44 | |* 47 | HASH JOIN RIGHT OUTER | | 4326K| 548M| 165M| 233K (2)| 00:46:42 | | 48 |TABLE ACCESS FULL | TMP_ACC_DIS_AMNT_ACCID| 8653K| 66M| | 3616 (5)| 00:00:44 | |* 49 |HASH JOIN RIGHT OUTER | | 4326K| 515M| 165M| 193K (2)| 00:38:41 | | 50 | TABLE ACCESS FULL | TMP_FUND_OUTGO_ACCID | 8654K| 66M| | 3589 (5)| 00:00:44 | |* 51 | HASH JOIN RIGHT OUTER | | 4326K| 482M| 165M| 154K (2)| 00:30:59 | | 52 | TABLE ACCESS FULL| TMP_FUND_INCOME_ACCID | 8654K| 66M| | 3728 (5)| 00:00:45 | |* 53 | HASH JOIN RIGHT OUTER | | 4326K| 449M| 165M| 117K (2)| 00:23:36 | | 54 | TABLE ACCESS FULL | TMP_FEE_INCOME_TOTAL_ACC_ID | 8654K| 66M| | 3728 (5)| 00:00:45 | |* 55 | HASH JOIN RIGHT OUTER | | 4326K| 416M| 132M| 82683 (2)| 00:16:33 | | 56 | TABLE ACCESS FULL | TMP_FUND_B_ACCID| 7338K| 48M| | 2808 (6)| 00:00:34 | |* 57 | TABLE ACCESS FULL | PRE_MED_FUND_ACC| 4326K| 387M| | 51358 (2)| 00:10:17 | | 58 | NESTED LOOPS OUTER | | 1 | 344 | | 1416K (1)| 04:43:24 | | 59 | NESTED LOOPS OUTER | | 1 | 336 | | 1416K (1)| 04:43:24 | | 60 |NESTED LOOPS OUTER | | 1 | 328 | | 1416K (1)| 04:43:24 | | 61 | NESTED LOOPS OUTER| | 1 | 320 | | 1416K (1)| 04:43:24 | | 62 | NESTED LOOPS OUTER | | 1 | 312 | | 1416K (1)| 04:43:24 | |* 63 | HASH JOIN RIGHT SEMI | | 1 | 304 | 2134M| 1416K (1)| 04:43:24 | | 64 | INDEX FAST FULL SCAN | LH_01 | 101M| 970M| | 152K (2)| 00:30:36 | |* 65 | HASH JOIN RIGHT OUTER| | 8653K| 2426M| 165M| 1030K (1)| 03:26:11 | | 66 | TABLE ACCESS FULL | TMP_FUND_OUTGO_ACCID | 8654K| 66M| | 3589 (5)| 00:00:44 | |* 67 | HASH JOIN RIGHT OUTER | | 8653K| 2360M| 165M| 896K (1)| 02:59:22 | | 68 |TABLE ACCESS FULL | TMP_SUM_PRE_ACCID | 8653K| 66M| | 3728 (5)| 00:00:45 | |* 69 |HASH JOIN RIGHT OUTER | | 8653K| 2294M| 165M| 765K (1)| 02:33:10 | | 70 | TABLE ACCESS FULL | TMP_PAID_AMNT_ACCID | 8653K| 66M| | 3728 (5)| 00:00:45 | |* 71 | HASH JOIN RIGHT OUTER | | 8653K| 2228M| 165M| 638K (1)| 02:07:37 | | 72 | TABLE ACCESS FULL| TMP_FUND_AVRGS_ACCID | 8653K| 66M| | 3756 (5)| 00:00:46 | |* 73 | HASH JOIN RIGHT OUTER | | 8653K| 2162M| 165M| 513K (1)| 01:42:44 | | 74 | TABLE ACCESS FULL | TMP_FEE_INCOME_ACCID | 8653K| 66M| | 3728 (5)| 00:00:45 | |* 75 | HASH JOIN RIGHT OUTER | | 8653K| 2096M| 165M| 392K (1)| 01:18:30 | | 76 | TABLE ACCESS FULL | TMP_ACCOUNT_V_B_ACCID | 8653K| 66M| | 3616 (5)| 00:00:44 | |* 77 | HASH JOIN RIGHT OUTER| | 8653K| 2030M| 132M| 274K (2)| 00:54:56 | | 78 | TABLE ACCESS FULL | TMP_FUND_B_ACCID| 7338K| 48M| | 2808 (6)| 00:00:34 | |* 79 | HASH JOIN RIGHT OUTER | | 8653K| 1972M| 139M| 162K (2)| 00:32:27 | | 80 |TABLE ACCESS FULL | TMP_FUND_AVRG1_ACCID | 7337K| 55M| | 3199 (5)| 00:00:39 | |* 81 |HASH JOIN RIGHT OUTER | | 8653K| 1906M| | 52225 (4)| 00:10:27 | | 82 | TABLE ACCESS FULL | TMP_PAID_MED_AMNT_ACCID | 30936 | 332K| | 19 (6)| 00:00:01 | |* 83 | HASH JOIN RIGHT OUTER | | 8653K| 1815M| | 52107 (4)| 00:10:26 | | 84 | TABLE ACCESS FULL| TMP_COST_CENTER_CNTRNO| 1877 | 82588 | | 5 (0)| 00:00:01 | |* 85 | HASH JOIN RIGHT OUTER | | 8653K| 1452M| | 5 (3)| 00:10:25 | | 86 | TABLE ACCESS FULL | TMP_INSUR_DUR_CNTRNO | 1862 | 48412 | | 5 (0)| 00:00:01 | |* 87 | HASH JOIN RIGHT OUTER | | 8653K| 1237M| | 51901 (3)| 00:10:23 | | 88 | TABLE ACCESS FULL | TMP_ACCOUNT_V_E_ACCID | 14 | 112 | | 3 (0)| 00:00:01 | |* 89 | HASH JOIN RIGHT OUTER| | 8653K| 1171M| | 51800 (3)| 00:10:22 | | 90 | TABLE ACCESS FULL | TMP_MEDDATE_CLAIMFLAG_ACCID | 1 | 35 | | 2 (0)| 00:00:01 | |* 91 | TABLE ACCESS FULL | PRE_MED_FUND_ACC| 8653K| 883M| | 51700 (3)| 00:10:21 | | 92 | TABLE ACCESS BY INDEX ROWID | TMP_YEAR_PREM_RG_ACCID| 1 | 8 | | 1 (0)| 00:00:01 | |* 93 | INDEX UNIQUE SCAN | KEY_YEAR_PREM_RG_ACCID| 1 | | | 1 (0)| 00:00:01 | | 94 | TABLE ACCESS BY INDEX ROWID | TMP_YEAR_PREM_SG_ACCID| 1 | 8 | | 1 (0)| 00:00:01 | |* 95 | INDEX UNIQUE SCAN | KEY_YEAR_PREM_SG_ACCID| 1 | | | 1 (0)| 00:00:01 | | 96 | TABLE ACCESS BY INDEX ROWID | TMP_ACC_DIS_AMNT_ACCID| 1 | 8 | | 1 (0)| 00:00:01 | |* 97 | INDEX UNIQUE SCAN| KEY_ACC_DIS_AMNT_ACCID| 1 | | | 1 (0)| 00:00:01 | | 98 |TABLE ACCESS BY INDEX ROWID | TMP_FUND_INCOME_ACCID | 1 | 8 | | 1 (0)| 00:00:01 | |* 99 | INDEX UNIQUE SCAN | KEY_FUND_INCOME_ACCID | 1 | | | 1 (0)| 00:00:01 | | 100 | TABLE ACCESS BY INDEX ROWID | TMP_FEE_INCOME_TOTAL_ACC_ID | 1 | 8 | | 1 (0)| 00:00:01 | |*101 |INDEX UNIQUE SCAN | KEY_FEE_INCOME_TOTAL_ACC_ID | 1 | | | 1 (0)| 00:00:01 | |*102 | HASH JOIN RIGHT OUTER| | 8653K| 4085M| | 202K (3)| 00:40:35 | | 103 | TABLE ACCESS FULL | TMP_COST_CENTER_CNTRNO| 1877 | 82588 | | 5 (0)| 00:00:01 | |*104 | HASH JOIN RIGHT OUTER | | 8653K| 3722M| | 202K (3)| 00:40:34 | | 105 |TABLE ACCESS FULL | TMP_PAID_MED_AMNT_CNTRNO | 1872 | 50544 | | 5 (0)| 00:00:01 | |*106 |HASH JOIN RIGHT OUTER | | 8653K| 3499M| | 202K (3)| 00:40:33 | | 107 | TABLE ACCESS FULL | TMP_INSUR_DUR_CNTRNO | 1862 | 48412 | | 5 (0)| 00:00:01 | |*108 | HASH JOIN RIGHT OUTER | | 8653K| 3284M| | 202K (3)| 00:40:31 | | 109 | TABLE ACCESS FULL| TMP_ACCOUNT_V_B_CNTRNO| 1 | 29 | | 3 (0)| 00:00:01 | |*110 | HASH JOIN RIGHT OUTER | | 8653K| 3045M| | 202K (3)| 00:40:30 | | 111 | TABLE ACCESS FULL | TMP_ACCOUNT_V_E_CNTRNO| 1 | 29 | | 3 (0)| 00:00:01 | |*112 | HASH JOIN RIGHT OUTER | | 8653K| 2805M| | 202K (2)| 00:40:29 | | 113 | TABLE ACCESS FULL | TMP_FEE_INCOME_CNTRNO | 1 | 25 | | 3 (0)| 00:00:01 | |*114 | HASH JOIN RIGHT OUTER| | 8653K| 2599M| | 202K (2)| 00:40:28 | | 115 | TABLE ACCESS FULL | TMP_FUND_AVRGS_CNTRNO | 1 | 29 | | 3 (0)| 00:00:01 | |*116 | HASH JOIN RIGHT OUTER | | 8653K| 2360M| | 202K (2)| 00:40:26 | | 117 |TABLE ACCESS FULL | TMP_IPSN_NO | 1 | 32 | | 3 (0)| 00:00:01 | |*118 |HASH JOIN RIGHT OUTER | | 8653K| 2096M| | 202K (2)| 00:40:25 | | 119 | TABLE ACCESS FULL | TMP_PAID_AMNT_CNTRNO | 1 | 25 | | 3 (0)| 00:00:01 | |*120 | HASH JOIN RIGHT OUTER | | 8653K| 1889M| | 201K (2)| 00:40:24 | | 121 | TABLE ACCESS FULL| TMP_PAID_EXP_AMNT_CNTRNO | 1 | 29 | | 3 (0)| 00:00:01 | |*122 | HASH JOIN RIGHT OUTER | | 8653K| 1650M| | 201K (2)| 00:40:23 | | 123 | TABLE ACCESS FULL | TMP_SUM_PRE_CNTRNO | 1 | 29 | | 3 (0)| 00:00:01 | |*124 | HASH JOIN RIGHT OUTER | | 8653K| 1411M| | 201K (2)| 00:40:22 | | 125 | TABLE ACCESS FULL | TMP_YEAR_PREM_RG_CNTRNO | 1 | 25 | | 3 (0)| 00:00:01 | |*126 | HASH JOIN RIGHT OUTER| | 8653K| 1204M| | 201K (2)| 00:40:20 | | 127 | TABLE ACCESS FULL | TMP_YEAR_PREM_SG_CNTRNO | 1 | 25 | | 3 (0)| 00:00:01 | |*128 | HASH JOIN RIGHT OUTER | | 8653K| 998M| | 201K (2)| 00:40:19 | | 129 |TABLE ACCESS FULL | TMP_ACC_DIS_AMNT_CNTRNO | 1 | 25 | | 3 (0)| 00:00:01 | |*130 |HASH JOIN RIGHT OUTER | | 8653K| 792M| 165M| 201K (2)| 00:40:18 | | 131 | TABLE ACCESS FULL | TMP_FUND_OUTGO_ACCID | 8654K| 66M| | 3589 (5)| 00:00:44 | |*132 | HASH JOIN RIGHT OUTER | | 8653K| 726M| 165M| 148K (2)| 00:29:41 | | 133 | TABLE ACCESS FULL| TMP_FUND_INCOME_ACCID | 8654K| 66M| | 3728 (5)| 00:00:45 | |*134 | HASH JOIN RIGHT OUTER | | 8653K| 660M| 165M| 98472 (2)| 00:19:42 | | 135 | TABLE ACCESS FULL | TMP_FEE_INCOME_TOTAL_ACC_ID | 8654K| 66M| | 3728 (5)| 00:00:45 | |*136 | TABLE ACCESS FULL | PRE_MED_FUND_ACC| 8653K| 594M| | 51822 (3)| 00:10:22 | | 137 | NESTED LOOPS OUTER | | 1 | 152 | | 347K (2)| 01:09:29 | | 138 | NESTED LOOPS OUTER | | 1 | 108 | | 347K (2)| 01:09:29 | |*139 |HASH JOIN SEMI | | 1 | 82 | 693M| 347K (2)| 01:09:29 | |*140 | TABLE ACCESS FULL | PRE_MED_FUND_ACC| 8653K| 594M| | 51699 (3)| 00:10:21 | | 141 | INDEX FAST FULL SCAN | LH_01 | 101M| 970M| | 152K (2)| 00:30:36 | | 142 |TABLE ACCESS BY INDEX ROWID | TMP_INSUR_DUR_CNTRNO | 1 | 26 | | 1 (0)| 00:00:01 | |*143 | INDEX UNIQUE SCAN | KEY_TMP_INSUR_DUR_CNTRNO | 1 | | | 0 (0)| | | 144 | TABLE ACCESS BY INDEX ROWID | TMP_COST_CENTER_CNTRNO| 1 | 44 | | 1 (0)| 00:00:01 | |*145 |INDEX UNIQUE SCAN | KEY_COST_CENTER_CNTRNO| 1 | | | 0 (0)| | --------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(“T1”.“APPL_ID”=“TMPAP30”.“APPL_ID”) 5 - access(“T1”.“APPL_ID”=“TMPAP53”.“APPL_ID”) 6 - access(“T1”.“APPL_ID”=“TMPAP50”.“APPL_ID”) 7 - access(“T1”.“APPL_ID”=“TMPAP51”.“APPL_ID”) 8 - access(“T1”.“APPL_ID”=“TMPAP44”.“APPL_ID”) 12 - access(“T1”.“APPL_ID”=“TMPAP31”.“APPL_ID”) filter(“T1”.“APPL_ID”=“TMPAP31”.“APPL_ID”) 20 - access(“T1”.“CG_NO”=“TMP”.“CNTR_NO”) 21 - access(“T”.“CNTR_NO”=“TMP1”.“CNTR_NO”) 23 - access(“T”.“CNTR_NO”=“TMP69”.“CNTR_NO”) 25 - access(“T”.“CNTR_NO”=“TMPNO30”.“CNTR_NO”) 27 - access(“T”.“ACC_ID”=“TMPID2046”.“ACC_ID”) 29 - access(“T”.“ACC_ID”=“TMPID43”.“ACC_ID”) 31 - access(“T”.“ACC_ID”=“TMPID44”.“ACC_ID”) 33 - access(“T”.“ACC_ID”=“TMPID56”.“ACC_ID”) 35 - access(“T”.“ACC_ID”=“TMPID82”.“ACC_ID”) 37 - access(“T”.“ACC_ID”=“TMPID81”.“ACC_ID”) 39 - access(“T”.“ACC_ID”=“TMPID65”.“ACC_ID”) 41 - access(“T”.“ACC_ID”=“TMPID53”.“ACC_ID”) 43 - access(“T”.“ACC_ID”=“TMPID51”.“ACC_ID”) 45 - access(“T”.“ACC_ID”=“TMPID50”.“ACC_ID”) 47 - access(“T”.“ACC_ID”=“TMPID58”.“ACC_ID”) 49 - access(“T”.“ACC_ID”=“TMPID78”.“ACC_ID”) 51 - access(“T”.“ACC_ID”=“TMPID79”.“ACC_ID”) 53 - access(“T”.“ACC_ID”=“TMPID57”.“ACC_ID”) 55 - access(“T”.“ACC_ID”=“TMPID77”.“ACC_ID”) 57 - filter(“T”.“FLAG”='1') 63 - access(“T”.“CG_ID”=“B”.“CG_ID” AND “B”.“IPSN_NO”=TO_NUMBER(“T”.“IPSN_NO”)) 65 - access(“T”.“ACC_ID”=“TMPID78”.“ACC_ID”) 67 - access(“T”.“ACC_ID”=“TMPID53”.“ACC_ID”) 69 - access(“T”.“ACC_ID”=“TMPID65”.“ACC_ID”) 71 - access(“T”.“ACC_ID”=“TMPID81”.“ACC_ID”) 73 - access(“T”.“ACC_ID”=“TMPID56”.“ACC_ID”) 75 - access(“T”.“ACC_ID”=“TMPID43”.“ACC_ID”) 77 - access(“T”.“ACC_ID”=“TMPID77”.“ACC_ID”) 79 - access(“T”.“ACC_ID”=“TMPID82”.“ACC_ID”) 81 - access(“T”.“ACC_ID”=“TMPID69”.“ACC_ID”) 83 - access(“T”.“CNTR_NO”=“TMP25”.“CNTR_NO”) 85 - access(“T”.“CNTR_NO”=“TMPNO30”.“CNTR_NO”) 87 - access(“T”.“ACC_ID”=“TMPID44”.“ACC_ID”) 89 - access(“T”.“ACC_ID”=“TMPID2046”.“ACC_ID”) 91 - filter((“T”.“FLAG”='2' OR “T”.“FLAG”='5')) 93 - access(“T”.“ACC_ID”=“TMPID51”.“ACC_ID”) 95 - access(“T”.“ACC_ID”=“TMPID50”.“ACC_ID”) 97 - access(“T”.“ACC_ID”=“TMPID58”.“ACC_ID”) 99 - access(“T”.“ACC_ID”=“TMPID79”.“ACC_ID”) 101 - access(“T”.“ACC_ID”=“TMPID57”.“ACC_ID”) 102 - access(“T”.“CNTR_NO”=“TMP46”.“CNTR_NO”) 104 - access(“T”.“CNTR_NO”=“TMPNO69”.“CNTR_NO”) 106 - access(“T”.“CNTR_NO”=“TMPNO30”.“CNTR_NO”) 108 - access(“T”.“CNTR_NO”=“TMPNO43”.“CNTR_NO”) 110 - access(“T”.“CNTR_NO”=“TMPNO44”.“CNTR_NO”) 112 - access(“T”.“CNTR_NO”=“TMPNO56”.“CNTR_NO”) 114 - access(“T”.“CNTR_NO”=“TMPNO81”.“CNTR_NO”) 116 - access(“T”.“CNTR_NO”=“TMPNO4”.“CNTR_NO”) 118 - access(“T”.“CNTR_NO”=“TMPNO65”.“CNTR_NO”) 120 - access(“T”.“CNTR_NO”=“TMPNO71”.“CNTR_NO”) 122 - access(“T”.“CNTR_NO”=“TMPNO53”.“CNTR_NO”) 124 - access(“T”.“CNTR_NO”=“TMPNO51”.“CNTR_NO”) 126 - access(“T”.“CNTR_NO”=“TMPNO50”.“CNTR_NO”) 128 - access(“T”.“CNTR_NO”=“TMPNO58”.“CNTR_NO”) 130 - access(“T”.“ACC_ID”=“TMPID78”.“ACC_ID”) 132 - access(“T”.“ACC_ID”=“TMPID79”.“ACC_ID”) 134 - access(“T”.“ACC_ID”=“TMPID57”.“ACC_ID”) 136 - filter((“T”.“FLAG”='4' OR “T”.“FLAG”='6')) 139 - access(“T”.“CG_ID”=“B”.“CG_ID” AND “B”.“IPSN_NO”=TO_NUMBER(“T”.“IPSN_NO”)) 140 - filter(“T”.“FLAG”='2') 143 - access(“T”.“CNTR_NO”=“TMPNO30”.“CNTR_NO”) 145 - access(“T”.“CNTR_NO”=“TMP3”.“CNTR_NO”) 245 rows selected.

是一个insert select，

优化SQL一条

，

然后其中的select是一堆union all 组合起来的。通过粗略一看，看的我头晕眼花。

给对方打电话，询问情况，得知开发说以前跑的比现在快

我让对方跑select * from table(dbms_xplan.display_awr('0ah5a8dbk28fh'),null,null,'advanced'); 并将内容发给我

其中存在三个执行计划， cost 分别有三个，当前跑的这个是其中cost最大的那个

第一、我不在现场

第二、现在没时间，也没办法详细优化

所以我选择的方案，就是通过coe_xfr_sql_profile.sql 来将执行计划绑定为cost最小的那个!

后来对方领导决定先不kill，因为我和对方说，这里是DML操作，回滚时间会比较长。

这里反应出了问题，首先开发连select的速度都没测，就直接insert，真是。。而且，再弱也应该知道开并行吧？这里也没有开并行

等周二详细优化的时候，思路如下：

1、先检查统计信息，并检查这个SQL产生三个执行计划的主要原因

2、将union all 拆开，分别优化每个SQL（如果能用with as 尝试运用）

3、优化好查询速度之后开并行跑。这里注意，看并行DML 要打开session级别的并行DML

未完待续……

篇3：[]SQL一些优化技巧

1、游标处理数据的方式是一次一条记录，而不是使用基于集合的方法，是使用游标还是使用WHILE循环，要具体分析需求，如果是每条记录都要做个性化处理，则使用游标。如果不需要，则基于集合操作即可。

2、合并联接：可以在一个表上同时执行插入、更新和删除操作。合并连接执行更少的数据库读取，并且运行速度更快。

MERGE INTO 目标表

USING 源表

ON 目标表与源表的匹配条件

WHEN MATCHED THEN

UPDATE SET 目标表字段=源表.字段

WHEN NOT MATCHED BY TARGET THEN

INSERT(字段1,字段2,...) VALUES(源表.字段1,源表.字段2,...)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

3、分组集：允许同时聚合多个字段分组，将所有不同的结果一起返回。

SELECT A,B,C,SUM(NUM)

FROM TABLENAME

GROUP BY GROUPING SETS((A),(B),(C),)

注：()会统计出总数，如果不需要可以去掉，

每个分组列都包含在一个括号里，如果(A,B)，则表示以AB进行分组

4、非重复值聚合：以COUNT(DISTINCT 字段)的形式使用DISTINCT关键字计算唯一值数。

SQL SERVER 计算非重复值聚合，它必须基于输入数据流创建和读取中间结果表。对于同一查询中的多个非重复值聚合来说，SQL SERVER会重复这一过程，销毁并重新创建数据流，为每个总计生成新的中间结果。

在能使用子查询的情况下不要使用非重复值聚合。

5、查询表的记录数

Count(*) ：对主键进行索引扫描或在没有主键的情况下执行全表扫描

针对于不常插入和删除的表或对记录数要求不是很精确的情况下，可以用Select Sum(row_count) From sys.dm_db_partition_stats Where object_id=object_id('表名') And index_id<=1语句进行查询

6、临时表与表变量

a、临时表在tempdb中创建，表变量在内存中创建

b、临时表具有会话作用域，表变量只有过程/批处理作用域

c、临时表可以使用DDL，表变量不可以使用DDL

d、表变量上的统计信息不会被计算

e、表变量不参与事务或锁定，不受回滚影响

篇4：浅谈如何优化SQL Server服务器

1.数据和日志文件分开存放在不同磁盘上

数据文件和日志文件的操作会产生大量的I/O，在可能的条件下，日志文件应该存放在一个与数据和索引所在的数据文件不同的硬盘上以分散I/O，同时还有利于数据库的灾难恢复。

2.tempdb数据库单独存放在不同磁盘上

tempdb数据库是其他所有数据库都有可能使用的临时数据库。当使用select into、在没建立索引的列上执行Orderby时就会在tempdb数据库中产生临时表来存储中间数据。由于建立和填充临时表会严重降低系统性能，所以在尽可能的情况下应该为要排序的列建立索引。同时，tempdb数据库是为所有的用户和应用程序共享，所以如果一个用户占据了tempdb数据库的所有空间，则其他数据库将不能再使用。在可能的情况下，tempdb数据库应该单独放置在一个速度更快的硬盘或者RAID阵列上。分离tempdb数据库的I/O操作以加快性能。tempdb数据库应该有适当的容量，以满足用户的需要。应该允许tempdb数据库的空间自动增长。如果设置为不允许自动增长，当查询操作建立了超过tempdb数据库容量的临时表时，操作将无法完成。

适当设置tempdb数据库的增长幅度，过小的增长幅度会产生更多的外部碎片，会占用更多的资源。

3.避免热点数据的发生

在SQLServer7.0之前，对于没有聚集索引的表(堆集表)，新插入的数据行总是放置在磁盘中表的物理结尾处。如果并发的用户很多，同时在对表执行插入或者更新数据的操作，这将使得十分繁忙的表的末尾有可能产生数据热点，

并发的I/O操作集中对少数页面进行操作，将导致数据库性能的下降。

在SQLServer中，新的数据行的物理存储空间的分配是通过PFS页面来进行的。PFS页面的管理算法将插入操作进行分散来尽量避免产生数据热点。

在设计应用系统和数据库时，要避免在自然增长的列上建立主键，这样有可能导致热点数据的发生。

4.数据类型要少

在设计表时，尽可能少用数据类型。这样一个数据页面上可以保存最多的信息。数据页面就少，检索数据页面的I/O操作就少，所以效率会高。

5.监控和整理空间碎片

文件空间的自动增长提高了自动管理性，但可能导致空间碎片。物理空间与数据的逻辑空间不再连续。定期的监控和空间碎片整理有利于提高I/O性能。

6.使用主数据文件和次要数据文件

每个数据库的一个主数据文件属于主文件组。对于1GB左右规模的数据库，一个数据文件就够了，如果有次要数据文件，主数据文件中有管理次要数据文件的指针。

采用多个数据文件时，主数据文件用于存储系统对象和表，次要数据文件用于存储用户数据和索引。在可能的情况下，主数据文件和次要数据文件可以单独存放在不同的磁盘上以分散I/O。

如果采用多个数据文件，推荐主数据文件存储系统数据，次要数据文件存放用户数据和索引，这样会有助于提高I/O性能。

篇5：对SQL优化的理解

SQL优化：

注：由于做PLSQL的开发，经常遇到SQL方面的优化，个人认为，好的代码习惯是能够避免或者消除某些BUG，好的SQL的跟差点SQL性能相差甚远的。随着ORACLE的内部优化器的作用，很多SQL优化过程都被内置解析器所代替。但是好的SQL代码还是很有欣赏价值的。

部分源于网上，部分源于个人实践，仅供参考。

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：

ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

（2） WHERE子句中的连接顺序：

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

（3） SELECT子句中避免使用 ‘ * ‘：

ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

（4）减少访问数据库的次数：

ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等；

（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

（7）整合简单,无关联的数据库访问：如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

（8）删除重复记录：最高效的删除重复记录方法 ( 因为使用了ROWID)例子： DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

（9）用TRUNCATE替代DELETE：

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)

（10）尽量多使用COMMIT：

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理上述3种资源中的内部花费

（11）用Where子句替换HAVING子句：

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里

（12）减少对表的查询：

在含有子查询的SQL语句中,要特别注意减少对表的查询.例子： SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

（13）通过内部函数提高SQL效率：

复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的

（14）使用表的别名(Alias)：

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

（15）用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 例子：（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB') (低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

（16）识别'低效执行'的SQL语句：

虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法： SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS REBUILD

(18）用EXISTS替换DISTINCT：

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子： (低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

（19） sql语句用大写的；

因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行（20）避免在索引列上使用NOT 通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.

（ 21）避免在索引列上使用计算．

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．举例: 低效： SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效: SELECT … FROM DEPT WHERE SAL > 25000/12;

（22）用<=替代<; 高效:

SELECT * FROM EMP WHERE DEPTNO <=4 低效: SELECT * FROM EMP WHERE DEPTNO >3 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

（23）用UNION替换OR (适用于索引列)

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

24）用IN来替换OR

这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的．低效: SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 高效 SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30); （25）避免在索引列上使用IS NULL和IS NOT NULL 避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果至少有一个列不为空，则记录存在于索引中．举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引. 低效: (索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效: (索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

（26）总是使用索引的第一个列：

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引（27）用UNION-ALL 替换UNION ( 如果有可能的话)：当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量低效： SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' 高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'

（28）用WHERE替代ORDER BY：

ORDER BY 子句只在两种严格的条件下使用索引. ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序. ORDER BY中所有的列必须定义为非空. WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列. 例如: 表DEPT包含以下列: DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL 低效: (索引不被使用) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE 高效: (使用索引) SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

（29） a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高. b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!

（30）避免使用耗费资源的操作:

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强

（31）避免改变索引列的类型.:

当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换. 假设 EMPNO是一个数值类型的索引列. SELECT … FROM EMP WHERE EMPNO = ‘123' 实际上,经过ORACLE类型转换, 语句转化为: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123')

幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变. 现在,假设EMP_TYPE是一个字符类型的索引列. SELECT … FROM EMP WHERE EMP_TYPE = 123 这个语句被ORACLE转换为: SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型

（32）优化GROUP BY:

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.

低效: SELECT JOB , AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'

高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB

作者 JohnsonLiang1988

篇6：如何优化SQL Server数据库查询

查询速度慢的原因 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应，

如何优化SQL Server数据库查询

。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 9、返回了不必要的行和列 10、查询语句不好，没有优化

优化查询的方法 1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL不在支持。数据量（尺寸）越大，提高I/O越重要. 2、纵向、横向分割表，减少表的尺寸(sp_spaceuse) 3、升级硬件 4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如性别字段 5、提高网速; 6、扩大服务器的内存,Windows 2000和SQLserver2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server? 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍（虚拟内存大小设置的一半）。 7、增加服务器 CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作Update,Insert， Delete还不能并行处理。 8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。 9、DB Server 和APPLication Server 分离；OLTP和OLAP分离 10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照SQL帮助文件'分区视图'） a、在实现分区视图之前，必须先水平分区表 b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。 11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能，在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的： 1、查询语句的词法、语法检查 2、将语句提交给DBMS的查询优化器 3、优化器做代数优化和存取路径的优化 4、由预编译模块生成查询规划 5、然后在合适的时间提交给系统处理执行 6、最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。 12、Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。 13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。 14、SQL的注释申明对执行没有任何影响 15、尽可能不使用光标，它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。游标可以按照它所支持的提取选项进行分类：只进必须按照从第一行到最后一行的顺序提取行。FETCH NEXT 是唯一允许的提取操作,也是默认方式。可滚动性可以在游标中任何地方随机提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个并发选项 READ_ONLY：不允许通过游标定位更新(Update)，且在组成结果集的行中没有锁。 OPTIMISTIC WITH valueS:乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的，服务器就执行修改。选择这个并发选项OPTIMISTIC WITH ROW VERSIONING:此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在 SQL Server 中，这个性能由 timestamp 数据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值：@@DBTS。每次以任何方式更改带有 timestamp 列的行时，SQL Server 先在时间戳列中存储当前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某个表具有 timestamp 列，则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值和上次提取时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较 timestamp 列即可。如果应用程序对没有 timestamp 列的表要求基于行版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。 SCROLL LOCKS 这个选项实现悲观并发控制。在悲观并发控制中，在把数据库的行读入游标结果集时，应用程序将试图锁定数据库行。在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁将一直保持到事务被提交或回滚；当提取下一行时

篇7：.NET高级工程师面试题之SQL篇

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Student](

[stuID] [int] IDENTITY(1,1) NOT NULL,

[stuName] [varchar](50) NOT NULL,

[deptID] [int] NOT NULL,

PRIMARY KEY CLUSTERED

(

[stuID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

SET IDENTITY_INSERT [dbo].[Student] ON

INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (1, N'计算机张三', 1)

INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (2, N'计算机李四', 1)

INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (3, N'计算机王五', 1)

INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (4, N'生物amy', 2)

INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (5, N'生物kity', 2)

INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (6, N'生物lucky', 2)

INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (7, N'数学_yiming', 3)

INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (8, N'数学_haoxue', 3)

INSERT [dbo].[Student] ([stuID], [stuName], [deptID]) VALUES (9, N'数学_wuyong', 3)

SET IDENTITY_INSERT [dbo].[Student] OFF

/****** Object: Default [DF__Departmen__depNa__5441852A] Script Date: 05/11/ 23:16:23 ******/

ALTER TABLE [dbo].[Department] ADD DEFAULT ('') FOR [depName]

/****** Object: Default [DF__Score__category__5EBF139D] Script Date: 05/11/2015 23:16:23 ******/

ALTER TABLE [dbo].[Score] ADD DEFAULT ('') FOR [category]

/****** Object: Default [DF__Score__score__5FB337D6] Script Date: 05/11/2015 23:16:23 ******/

ALTER TABLE [dbo].[Score] ADD DEFAULT ((0)) FOR [score]

/****** Object: Default [DF__Student__stuName__59063A47] Script Date: 05/11/2015 23:16:23 ******/

ALTER TABLE [dbo].[Student] ADD DEFAULT ('') FOR [stuName]

/****** Object: ForeignKey [FK__Student__deptID__59FA5E80] Script Date: 05/11/2015 23:16:23 ******/

ALTER TABLE [dbo].[Student] WITH CHECK ADD FOREIGN KEY([deptID])

REFERENCES [dbo].[Department] ([depID])

准备环境

3 结果

面试的时候，没有写出来，当时脑袋昏沉沉的。也确实好久没有写复杂的sql语句了。今天花了2到3个小时，终于试出来了。不知道有没有更好的写法?

-- 每个系里的最高分的学生信息

SELECT Department.depID, Department.depName, Student.stuID, stuName, Dscore.scores

FROM Department

LEFT JOIN Student

on department.depID = student.deptID

LEFT JOIN (SELECT Score.stuId, SUM(Score) AS scores

FROM Score

GROUP by stuID

) AS Dscore

on Student.stuID = dScore.stuID

where exists (

select *

from

(

SELECT deptID, MAX(scores) AS topScores

FROM Student

LEFT JOIN

(

SELECT stuID,SUM(score) AS scores

FROM Score

GROUP BY stuID) AS newScore

ON Student.stuID = newScore.stuID

group by deptID) AS depScore

where Department.depID = depScore.deptID and Dscore.scores=depScore.topScores

)

order by Department.depID,Student.stuID;

篇8：浅析SQL Server数据库的性能优化

· 减少竞争和等待的次数，尤其是磁盘读写等待次数

· 利用更快的部件

· 减少利用资源所需的时间

绝大多数性能的获得来自于优秀的数据库设计、精确的查询分析和适当的索引。最好性能的获得能够通过确立优秀的数据库设计，在开发时学会使用SQL Server查询优化器来实现。

为了取得更好的数据库性能，我们就需要对数据库进行优化，减少系统资源的竞争，如对数据cache，过程cache，系统资源和CPU的竞争。

在SQL Server中，有如下优化层次：

·应用层——大部分性能的获得来自于对你的SQL应用中查询的优化，这必须是以好的数据库设计为基础的。

·数据库层——应用共享在数据库层中的资源，这些资源包括硬盘，事务日志和数据cache。

·服务器层——在服务器层有许多共享的资源，包括数据高速缓存，过程高速缓存，锁，CPU等。

·设备层——指的是存储数据的磁盘及其控制器，在这一层，你应尤其关注磁盘的I/O。

·网络层——指连接用户和SQL Server的网络。

·硬件层——指可利用的CPU。

·操作系统层——理想地，SQL Server是一台机器的唯一主要应用，它必须和操作系统以及其他sybase软件，如Backup Server或SQL Server Monitor共享处理器、内存以及其他资源。

在大多数情况下面，我们是对应用层进行优化，，因为对应用性能的优化是大家最乐于接受的功能，其结果能被观测及检验，查询的性能是SQL应用的整个性能的一个关键。

应用层上的问题包括以下内容：

·决策支持VS.和在线事务处理(OLTP)需要不同的性能策略

·事务设计能够减少并发，因为长的事务保持占用锁，也就减少了其他用户对相关数据的存取

·关联一致性对数据修改需要join操作

·支持Select操作的索引增加了修改数据的时间

·为了安全而设立的审计限制了性能

在应用层优化的选项包括：

·远程处理或复制处理能够把决策支持从OLTP机器中分离出来

·利用存储过程来减少编译时间和网络的利用

·利用最少量的锁去满足你的应用需要

数据库层的问题包括：

·建立备份和恢复方案

·在设备上分布存储数据

·审计操作影响性能;仅审计你所需的

·日常的维护活动将导致性能的降低和导致用户不能操作数据库表

在数据库层上优化选择包括：

·利用事务日志的阀值来自动转储事务日志防止其超出使用空间

·在数据段中用阀值来监视空间的使用

·利用分区来加速数据的装入

·对象的定位以避免硬盘的竞争

·把重要表和索引放入cache中，保证随时取得

服务器层的问题有：

·应用的类型——服务器是支持OLTP还是DSS，或者两者都支持

·所支持的用户数影响优化决策——随着用户数的增加，对资源的竞争会发生改变

·网络负荷

·当用户数和事务数达到一定的数量时复制服务器或其他分布式处理是一个解决的方法

服务器层的优化的选项包括:

·优化内存——一个关键的配置参数和其他方面的参数

·决策是客户端处理还是服务器端处理——有些处理能在客户端进行吗

·配置cache的大小和I/O的大小

·增加多个CPU

·为空闲时间排定批处理任务和生成报表

·工作负荷发生改变，重新配置特定参数

·决定是否可能把DSS移到另一个SQL服务器中设备层

设备层的问题包括：

·主设备、包含用户数据库的设备，用户数据设备，或数据库日志是否要镜像

·怎样在设备之间分布系统数据库、用户数据库和数据库日志

·为获得对堆表插入操作的高性能，是否有必要进行分区

设备层上优化的选项包括：

·用多个中等大小的设备及多个控制器可能比用少量的大设备有更好的I/O性能

·分布数据库，表和索引以在不同的设备上进行I/O装载

网络层

实际上，SQL Server的所有用户都是通过网络存取他们的数据，

网络层上的主要问题有:

·网络的流量

·网络的瓶颈

·网络的速度

网络层上优化的选项包括：

·配置包的大小，以使其与应用的需要相匹配

·配置子网

·分隔出繁忙的网络运用

·创建一个高容量的网络

·配置多个网络引擎

·更好地设计应用，限制所需的网络传输

硬件层

在硬件层上的问题包括

·CPU的效率

·磁盘的存取:控制器和磁盘

·磁盘备份

·内存的使用

在硬件层上优化的选项包括：

·增加CPU以适应工作负荷

·配置调度程序以提高CPU利用率

·遵循多处理器应用设计指导以减少竞争

·配置多个数据cache操作系统层

操作系统层的主要问题有：

·文件系统——是否被SQL Server独占使用

·内存管理——精确估算操作系统和其他程序的内存占用

·CPU的利用——整个系统共有多少处理器可用?有多少分配给SQL Server

在操作系统层优化的选项包括：

·网络接口

·在文件和原始分区之间选择

·增加内存

·把客户操作和批处理移到其他机器上

·SQL Server利用多个CPU

篇9：浅析SQL Server数据库的性能优化数据库

在一个大型的数据库中，性能成为人们关注的焦点之一，如何让数据库高效有效的运行成为广大数据库管理人员和开发人员必须要考虑的问题，性能是一个应用或多个应用在相同的环境下运行时对效率的衡量。性能常用响应时间和工作效率来表示。响应时间是指完成

・减少竞争和等待的次数，尤其是磁盘读写等待次数

・利用更快的部件

・减少利用资源所需的时间

为了取得更好的数据库性能，我们就需要对数据库进行优化，减少系统资源的竞争，如对数据cache，过程cache，系统资源和CPU的竞争。

在SQL Server中，有如下优化层次：

・应用层――大部分性能的获得来自于对你的SQL应用中查询的优化，这必须是以好的数据库设计为基础的。

・数据库层――应用共享在数据库层中的资源，这些资源包括硬盘，事务日志和数据cache。

・服务器层――在服务器层有许多共享的资源，包括数据高速缓存，过程高速缓存，锁，CPU等。

・设备层――指的是存储数据的磁盘及其控制器，在这一层，你应尤其关注磁盘的I/O。

・网络层――指连接用户和SQL Server的网络。

・硬件层――指可利用的CPU。

・操作系统层――理想地，SQL Server是一台机器的唯一主要应用，它必须和操作系统以及其他sybase软件，如Backup Server或SQL Server Monitor共享处理器、内存以及其他资源。

应用层上的问题包括以下内容：

・决策支持VS.和在线事务处理(OLTP)需要不同的性能策略

・事务设计能够减少并发，因为长的事务保持占用锁，也就减少了其他用户对相关数据的存取

・关联一致性对数据修改需要join操作

・支持Select操作的索引增加了修改数据的时间

・为了安全而设立的审计限制了性能

在应用层优化的选项包括：

・远程处理或复制处理能够把决策支持从OLTP机器中分离出来

・利用存储过程来减少编译时间和网络的利用

・利用最少量的锁去满足你的应用需要

数据库层的问题包括：

・建立备份和恢复方案

・在设备上分布存储数据

・审计操作影响性能;仅审计你所需的

・日常的维护活动将导致性能的降低和导致用户不能操作数据库表

在数据库层上优化选择包括：

・利用事务日志的阀值来自动转储事务日志防止其超出使用空间

・在数据段中用阀值来监视空间的使用

・利用分区来加速数据的装入

・对象的定位以避免硬盘的竞争

・把重要表和索引放入cache中，保证随时取得

服务器层的问题有：

・应用的类型――服务器是支持OLTP还是DSS，或者两者都支持

・所支持的用户数影响优化决策――随着用户数的增加，对资源的竞争会发生改变

・网络负荷

・当用户数和事务数达到一定的数量时复制服务器或其他分布式处理是一个解决的方法

服务器层的优化的选项包括:

・优化内存――一个关键的配置参数和其他方面的参数

・决策是客户端处理还是服务器端处理――有些处理能在客户端进行吗

・配置cache的大小和I/O的大小

・增加多个CPU

・为空闲时间排定批处理任务和生成报表

・工作负荷发生改变，重新配置特定参数

・决定是否可能把DSS移到另一个SQL服务器中设备层

设备层的问题包括：

・主设备、包含用户数据库的设备，用户数据设备，或数据库日志是否要镜像

・怎样在设备之间分布系统数据库、用户数据库和数据库日志

・为获得对堆表插入操作的高性能，是否有必要进行分区

设备层上优化的选项包括：

・用多个中等大小的设备及多个控制器可能比用少量的大设备有更好的I/O性能

・分布数据库，表和索引以在不同的设备上进行I/O装载

网络层

实际上，SQL Server的所有用户都是通过网络存取他们的数据，

网络层上的主要问题有:

・网络的流量

・网络的瓶颈

・网络的速度

网络层上优化的选项包括：

・配置包的大小，以使其与应用的需要相匹配

・配置子网

・分隔出繁忙的网络运用

・创建一个高容量的网络

・配置多个网络引擎

・更好地设计应用，限制所需的网络传输

硬件层

在硬件层上的问题包括

・CPU的效率

・磁盘的存取:控制器和磁盘

・磁盘备份

・内存的使用

在硬件层上优化的选项包括：

・增加CPU以适应工作负荷

・配置调度程序以提高CPU利用率

・遵循多处理器应用设计指导以减少竞争

・配置多个数据cache操作系统层

操作系统层的主要问题有：

・文件系统――是否被SQL Server独占使用

・内存管理――精确估算操作系统和其他程序的内存占用

・CPU的利用――整个系统共有多少处理器可用?有多少分配给SQL Server

在操作系统层优化的选项包括：

・网络接口

・在文件和原始分区之间选择

・增加内存

・把客户操作和批处理移到其他机器上

・SQL Server利用多个CPU

原文转自：www.ltesting.net

篇10：SQL Server数据库优化方案数据库教程

server|数据|数据库|优化

查询速度慢的原因很多，常见如下几种：

1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)

2、I/O吞吐量小，形成了瓶颈效应，

3、没有创建计算列导致查询不优化。

4、内存不足

5、网络速度慢

6、查询出的数据量过大(可以采用多次查询，其他的方法降低数据量)

7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)

8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。

9、返回了不必要的行和列

10、查询语句不好，没有优化

可以通过如下方法来优化查询 :

1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL不在支持。数据量(尺寸)越大，提高I/O越重要.

2、纵向、横向分割表，减少表的尺寸(sp_spaceuse)

3、升级硬件

4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小，使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段

5、提高网速;

6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server? 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍(虚拟内存大小设置的一半)。

7、增加服务器 CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作Update,Insert， Delete还不能并行处理。

8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。

9、DB Server 和APPLication Server 分离;OLTP和OLAP分离

10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。(参照SQL帮助文件'分区视图')

a、在实现分区视图之前，必须先水平分区表

b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。

11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的：

1、查询语句的词法、语法检查

2、将语句提交给DBMS的查询优化器

3、优化器做代数优化和存取路径的优化

4、由预编译模块生成查询规划

5、然后在合适的时间提交给系统处理执行

6、最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。

12、Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。

13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。

14、SQL的注释申明对执行没有任何影响

15、尽可能不使用光标，它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。游标可以按照它所支持的提取选项进行分类：只进必须按照从第一行到最后一行的顺序提取行。FETCH NEXT 是唯一允许的提取操作,也是默认方式。可滚动性可以在游标中任何地方随机提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个并发选项 READ_ONLY：不允许通过游标定位更新(Update)，且在组成结果集的行中没有锁。 OPTIMISTIC WITH valueS:乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的，服务器就执行修改。选择这个并发选项OPTIMISTIC WITH ROW VERSIONING:此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在 SQL Server 中，这个性能由 timestamp 数据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值：@@DBTS。每次以任何方式更改带有 timestamp 列的行时，SQL Server 先在时间戳列中存储当前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某个表具有 timestamp 列，则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值和上次提取时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较 timestamp 列即可。如果应用程序对没有 timestamp 列的表要求基于行版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。 SCROLL LOCKS 这个选项实现悲观并发控制。在悲观并发控制中，在把数据库的行读入游标结果集时，应用程序将试图锁定数据库行。在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁将一直保持到事务被提交或回滚;当提取下一行时，将除去游标锁。如果在事务外打开游标，则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发控制时，游标都应在事务内打开。更新锁将阻止任何其它任务获取更新锁或排它锁，从而阻止其它任务更新该行。然而，更新锁并不阻止共享锁，所以它不会阻止其它任务读取行，除非第二个任务也在要求带更新锁的读取。滚动锁根据在游标定义的 Select 语句中指定的锁提示，这些游标并发选项可以生成滚动锁。滚动锁在提取时在每行上获取，并保持到下次提取或者游标关闭，以先发生者为准。下次提取时，服务器为新提取中的行获取滚动锁，并释放上次提取中行的滚动锁。滚动锁独立于事务锁，并可以保持到一个提交或回滚操作之后。如果提交时关闭游标的选项为关，则 COMMIT 语句并不关闭任何打开的游标，而且滚动锁被保留到提交之后，以维护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标 Select 语句中的锁提示。锁提示只读乐观数值乐观行版本控制锁定无提示未锁定未锁定未锁定更新 NOLOCK 未锁定未锁定未锁定未锁定 HOLDLOCK 共享共享共享更新 UPDLOCK 错误更新更新更新 TABLOCKX 错误未锁定未锁定更新其它未锁定未锁定未锁定更新 *指定 NOLOCK 提示将使指定了该提示的表在游标内是只读的。

16、用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优化索引

17、注意UNion和UNion all 的区别。UNION all好

18、注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。重复的记录在查询里是没有问题的

19、查询时不要返回不需要的行、列

20、用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查询之前就扼杀掉。 SET LOCKTIME设置锁的时间

21、用select top 100 / 10 Percent 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行

22、在SQL2000以前，一般不要用如下的字句: “IS NULL”, “”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE '%500'”，因为他们不走索引全是表扫描。也不要在Where字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法：Where SUBSTRING(firstname,1,1) = 'm'改为Where firstname like 'm%'(索引扫描)，一定要将函数和列名分开。并且索引不能建得太多和太大。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，现在2000的优化器能够处理了。相同的是IS NULL，“NOT”, “NOT EXISTS”, “NOT IN”能优化她，而“”等还是不能优化，用不到索引。

23、使用Query Analyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL。一般的20%的代码占据了80%的资源，我们优化的重点是这些慢的地方。

24、如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引： Select * FROM PersonMember (INDEX = IX_Title) Where processid IN ('男'，'女')

25、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段。例如医院的住院费计算。

26、MIN 和 MAX()能使用到合适的索引。

27、数据库有一个原则是代码离数据越近越好，所以优先选择Default,依次为Rules,Triggers, Constraint(约束如外健主健CheckUNIQUE……,数据类型的最大长度等等都是约束),Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。

28、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌Insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍)，服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 方法：Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

29、Between在某些时候比IN 速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。 select * from chineseresume where title in ('男','女') Select * from chineseresume where between '男' and '女' 是一样的。由于in会在比较多次，所以有时会慢些。

30、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。

31、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。

32、用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。

33、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。

34、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION 和UNION ALL一样的道理。

select top 20 ad.companyname,comid,position,ad.referenceid,worklocation, convert(varchar(10),ad.postDate,120) as postDate1,workyear,degreedescription FROM jobcn_query.dbo.COMPANYAD_query ad where referenceID in('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345',

'JCNAD00333138','JCNAD00303570','JCNAD00303569',

'JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933',

'JCNAD00254567','JCNAD00254585','JCNAD00254608',

'JCNAD00254607','JCNAD00258524','JCNAD00332133','JCNAD00268618',

'JCNAD00279196','JCNAD00268613') order by postdate desc

35、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数，

36、当用Select INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit 在另一个连接中Select * from sysobjects可以看到 Select INTO 会锁住系统表，Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它!!!这样的话如果是经常要用的临时表请使用实表，或者临时表变量。

37、一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快

38、一次更新多条记录比分多次更新每次一条快,就是说批处理好

39、少用临时表，尽量用结果集和Table类性的变量来代替它,Table 类型的变量比临时表好

40、在SQL2000下，计算字段是可以索引的，需要满足的条件如下：

a、计算字段的表达是确定的

b、不能用在TEXT,Ntext，Image数据类型

c、必须配制如下选项 ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….

41、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程(临时表)被放在Tempdb中。以前由于SQL SERVER对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程

42、不要在一句话里再三的使用相同的函数，浪费资源,将结果放在变量里再调用更快

43、Select COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的!!!

44、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率;否则使用配制线程数量<最大连接数启用sql server的线程池来解决如果还是数量=“最大连接数+5，严重的损害服务器的性能。”>

45、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你(不经意的)某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现

46、通过SQL Server Performance Monitor监视相应硬件的负载 Memory: Page Faults / sec计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。

Process:

1、% DPC Time 指在范例间隔期间处理器用在缓延程序调用(DPC)接收和提供服务的百分比。(DPC 正在运行的为比标准间隔优先权低的间隔)。由于 DPC 是以特权模式执行的，DPC 时间的百分比为特权时间百分比的一部分。这些时间单独计算并且不属于间隔计算总数的一部分。这个总数显示了作为实例时间百分比的平均忙时。

2、%Processor Time计数器如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。

3、% Privileged Time 指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内存。另一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务)。特权时间的 % 包括为间断和 DPC 提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。

4、% User Time表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。 Physical Disk: Curretn Disk Queue Length计数器该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。 SQLServer:Cache Hit Ratio计数器该值越高越好。如果持续低于80%，应考虑增加内存。注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。

47、分析select emp_name form. employee where salary > 3000 在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。

48、查询的关联同写的顺序

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B ,B = '号码'）

select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B ,B = '号码'， A = '号码'）

select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '号码'， A = '号码'）

49、

(1)IF 没有输入负责人代码 THEN code1=0 code2=9999 ELSE code1=code2=负责人代码 END IF 执行SQL语句为: Select 负责人名 FROM P2000 Where 负责人代码>=:code1 AND负责人代码 <=:code2

(2)IF 没有输入负责人代码 THEN Select 负责人名 FROM P2000 ELSE code= 负责人代码 Select 负责人代码 FROM P2000 Where 负责人代码=:code END IF 第一种方法只用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代码时,第二种方法显然比第一种方法执行效率高,因为它没有限制条件; 在输入了负责人代码时,第二种方法仍然比第一种方法效率高,不仅是少了一个限制条件,还因相等运算是最快的查询运算。我们写程序不要怕麻烦

50、关于JOBCN现在查询分页的新方法(如下)，用性能优化器分析性能的瓶颈，如果在I/O或者网络的速度上，如下的方法优化切实有效，如果在CPU或者内存上，用现在的方法更好。请区分如下的方法，说明索引越小越好。

begin

DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))

insert into @local_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by ReferenceID

select * from @local_variable where Fid > 40 and fid <= 60

end 和

begin

DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))

insert into @local_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select * from @local_variable where Fid > 40 and fid <= 60

end 的不同

begin

create table #temp (FID int identity(1,1),ReferenceID varchar(20))

insert into #temp (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select * from #temp where Fid > 40 and fid <= 60 drop table #temp

end

另附：存储过程编写经验和优化措施 From：网页教学网

一、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP(存储过程)的优化的项目开发人员，对数据库有浓厚兴趣的人。

二、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。如果项目的SP较多，书写又没有一定的规范，将会影响以后的系统维护困难和大SP逻辑的难以理解，另外如果数据库的数据量大或者项目对SP的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。

三、内容：

1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。

2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。

3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点：

a)SQL的使用规范：

i. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。

ii. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。

iii. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写;如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

iv. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。

v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

vi. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。

vii. 尽量使用“>=”，不要使用“>”。

viii. 注意一些or子句和union子句之间的替换

ix. 注意表之间连接的数据类型，避免不同类型数据之间的连接。

x. 注意存储过程中参数和数据类型的关系。

xi. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面(400k)，那么系统将会进行锁升级，页级锁会升级成表级锁。

b)索引的使用规范：

i. 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。

ii. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引

iii. 避免对大表查询时进行table scan，必要时考虑新建索引。

iv. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

v. 要注意索引的维护，周期性重建索引，重新编译存储过程。

c)tempdb的使用规范：

i. 尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。

ii. 避免频繁创建和删除临时表，减少系统表资源的消耗。

iii. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度;如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。

iv. 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。

v. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。

vi. 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。

d)合理的算法使用：

根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。

篇11：SQL Server数据库性能优化数据库教程

server|数据|数据库|性能|优化

设计1个应用系统似乎并不难，但是要想使系统达到最优化的性能并不是一件容易的事，

SQL Server数据库性能优化数据库教程

。在开发工具、数据库设计、应用程序的结构、查询设计、接口选择等方面有多种选择，这取决于特定的应用需求以及开发队伍的技能。本文以SQL Server为例，从后台数据库的角度讨论应用程序性能优化技巧，并且给出了一些有益的建议。

1 数据库设计

要在良好的SQL Server方案中实现最优的性能，最关键的是要有1个很好的数据库设计方案。在实际工作中，许多SQL Server方案往往是由于数据库设计得不好导致性能很差。所以，要实现良好的数据库设计就必须考虑这些问题。

1.1 逻辑库规范化问题

一般来说，逻辑数据库设计会满足规范化的前3级标准:

1.第1规范:没有重复的组或多值的列。

2.第2规范:每个非关键字段必须依赖于主关键字，不能依赖于1个组合式主关键字的某些组成部分。

3.第3规范:1个非关键字段不能依赖于另1个非关键字段。

遵守这些规则的设计会产生较少的列和更多的表，因而也就减少了数据冗余，也减少了用于存储数据的页。但表关系也许需要通过复杂的合并来处理，这样会降低系统的性能。某种程度上的非规范化可以改善系统的性能，非规范化过程可以根据性能方面不同的考虑用多种不同的方法进行，但以下方法经实践验证往往能提高性能。

1.如果规范化设计产生了许多4路或更多路合并关系，就可以考虑在数据库实体(表)中加入重复属性(列)。

2.常用的计算字段(如总计、最大值等)可以考虑存储到数据库实体中。

比如某一个项目的计划管理系统中有计划表，其字段为:项目编号、年初计划、二次计划、调整计划、补列计划…，而计划总数(年初计划+二次计划+调整计划+补列计划)是用户经常需要在查询和报表中用到的，在表的记录量很大时，有必要把计划总数作为1个独立的字段加入到表中。这里可以采用触发器以在客户端保持数据的一致性。

3.重新定义实体以减少外部属性数据或行数据的开支。相应的非规范化类型是:

(1)把1个实体(表)分割成2个表(把所有的属性分成2组)。这样就把频繁被访问的数据同较少被访问的数据分开了。这种方法要求在每个表中复制首要关键字。这样产生的设计有利于并行处理，并将产生列数较少的表。

(2)把1个实体(表)分割成2个表(把所有的行分成2组)。这种方法适用于那些将包含大量数据的实体(表)。在应用中常要保留历史记录，但是历史记录很少用到。因此可以把频繁被访问的数据同较少被访问的历史数据分开。而且如果数据行是作为子集被逻辑工作组(部门、销售分区、地理区域等)访问的，那么这种方法也是很有好处的。

1.2 生成物理数据库

要想正确选择基本物理实现策略，必须懂得数据库访问格式和硬件资源的操作特点，主要是内存和磁盘子系统I/O。这是一个范围广泛的话题，但以下的准则可能会有所帮助。

1.与每个表列相关的数据类型应该反映数据所需的最小存储空间，特别是对于被索引的列更是如此。比如能使用smallint类型就不要用integer类型，这样索引字段可以被更快地读取，而且可以在1个数据页上放置更多的数据行，因而也就减少了I/O操作。

2.把1个表放在某个物理设备上，再通过SQL Server段把它的不分簇索引放在1个不同的物理设备上，这样能提高性能。尤其是系统采用了多个智能型磁盘控制器和数据分离技术的情况下，这样做的好处更加明显。

3.用SQL Server段把一个频繁使用的大表分割开，并放在2个单独的智能型磁盘控制器的数据库设备上，这样也可以提高性能。因为有多个磁头在查找，所以数据分离也能提高性能。

4.用SQL Server段把文本或图像列的数据存放在1个单独的物理设备上可以提高性能。1个专用的智能型的控制器能进一步提高性能。

2 与SQL Server相关的硬件系统

与SQL Server有关的硬件设计包括系统处理器、内存、磁盘子系统和网络，这4个部分基本上构成了硬件平台，Windows NT和SQL Server运行于其上。

2.1 系统处理器(CPU)

根据自己的具体需要确定CPU结构的过程就是估计在硬件平台上占用CPU的工作量的过程。从以往的经验看，CPU配置最少应是1个80586/100处理器。如果只有2～3个用户，这就足够了，但如果打算支持更多的用户和关键应用，推荐采用Pentium Pro或PⅡ级CPU。

2.2 内存(RAM)

为SQL Server方案确定合适的内存设置对于实现良好的性能是至关重要的。SQL Server用内存做过程缓存、数据和索引项缓存、静态服务器开支和设置开支。SQL Server最多能利用2GB虚拟内存，这也是最大的设置值。还有一点必须考虑的是Windows NT和它的所有相关的服务也要占用内存。

Windows NT为每个WIN32应用程序提供了4GB的虚拟地址空间。这个虚拟地址空间由Windows NT虚拟内存管理器(VMM)映射到物理内存上，在某些硬件平台上可以达到4GB。SQL Server应用程序只知道虚拟地址，所以不能直接访问物理内存，这个访问是由VMM控制的。Windows NT允许产生超出可用的物理内存的虚拟地址空间，这样当给SQL Server分配的虚拟内存多于可用的物理内存时，会降低SQL Server的性能。

这些地址空间是专门为SQL Server系统设置的，所以如果在同一硬件平台上还有其它软件(如文件和打印共享，应用程序服务等)在运行，那么应该考虑到它们也占用一部分内存。一般来说硬件平台至少要配置32MB的内存，其中，Windows NT至少要占用16MB。1个简单的法则是，给每一个并发的用户增加100KB的内存。例如，如果有100个并发的用户，则至少需要32MB+100用户*100KB=42MB内存，实际的使用数量还需要根据运行的实际情况调整。可以说，提高内存是提高系统性能的最经济的途径。

2.3 磁盘子系统

设计1个好的磁盘I/O系统是实现良好的SQL Server方案的一个很重要的方面。这里讨论的磁盘子系统至少有1个磁盘控制设备和1个或多个硬盘单元，还有对磁盘设置和文件系统的考虑。智能型SCSI-2磁盘控制器或磁盘组控制器是不错的选择，其特点如下:

(1)控制器高速缓存。

(2)总线主板上有处理器，可以减少对系统CPU的中断。

(3)异步读写支持。

(4)32位RAID支持。

(5)快速SCSI―2驱动。

(6)超前读高速缓存(至少1个磁道)。

3 检索策略

在精心选择了硬件平台，又实现了1个良好的数据库方案，并且具备了用户需求和应用方面的知识后，现在应该设计查询和索引了。有2个方面对于在SQL Server上取得良好的查询和索引性能是十分重要的，第1是根据SQL Server优化器方面的知识生成查询和索引;第2是利用SQL Server的性能特点，加强数据访问操作，

3.1 SQL Server优化器

Microsoft SQL Server数据库内核用1个基于费用的查询优化器自动优化向SQL提交的数据查询操作。数据操作查询是指支持SQL关键字WHERE或HAVING的查询，如SELECT、DELETE和UPDATE。基于费用的查询优化器根据统计信息产生子句的费用估算。

了解优化器数据处理过程的简单方法是检测SHOWPLAN命令的输出结果。如果用基于字符的工具(例如isql)，可以通过键入SHOW SHOWPLAN ON来得到SHOWPLAN命令的输出。如果使用图形化查询，比如SQL Enterprise Manager中的查询工具或isql/w，可以设定配置选项来提供这一信息。

SQL Server的优化通过3个阶段完成:查询分析、索引选择、合并选择。

1.查询分析

在查询分析阶段，SQL Server优化器查看每一个由正规查询树代表的子句，并判断它是否能被优化。SQL Server一般会尽量优化那些限制扫描的子句。例如，搜索和/或合并子句。但是不是所有合法的SQL语法都可以分成可优化的子句，如含有SQL不等关系符“”的子句。因为“”是1个排斥性的操作符，而不是1个包括性的操作符，所在扫描整个表之前无法确定子句的选择范围会有多大。当1个关系型查询中含有不可优化的子句时，执行计划用表扫描来访问查询的这个部分，对于查询树中可优化的SQL Server子句，则由优化器执行索引选择。

2.索引选择

对于每个可优化的子句，优化器都查看数据库系统表，以确定是否有相关的索引能用于访问数据。只有当索引中的列的1个前缀与查询子句中的列完全匹配时，这个索引才被认为是有用的。因为索引是根据列的顺序构造的，所以要求匹配是精确的匹配。对于分簇索引，原来的数据也是根据索引列顺序排序的。想用索引的次要列访问数据，就像想在电话本中查找所有姓为某个姓氏的条目一样，排序基本上没有什么用，因为你还是得查看每一行以确定它是否符合条件。如果1个子句有可用的索引，那么优化器就会为它确定选择性。

所以在设计过程中，要根据查询设计准则仔细检查所有的查询，以查询的优化特点为基础设计索引。

(1)比较窄的索引具有比较高的效率。对于比较窄的索引来说，每页上能存放较多的索引行，而且索引的级别也较少。所以，缓存中能放置更多的索引页，这样也减少了I/O操作。

(2)SQL Server优化器能分析大量的索引和合并可能性。所以与较少的宽索引相比，较多的窄索引能向优化器提供更多的选择。但是不要保留不必要的索引，因为它们将增加存储和维护的开支。对于复合索引、组合索引或多列索引，SQL Server优化器只保留最重要的列的分布统计信息，这样，索引的第1列应该有很大的选择性。

(3)表上的索引过多会影响UPDATE、INSERT和DELETE的性能，因为所有的索引都必须做相应的调整。另外，所有的分页操作都被记录在日志中，这也会增加I/O操作。

(4)对1个经常被更新的列建立索引，会严重影响性能。

(5)由于存储开支和I/O操作方面的原因，较小的自组索引比较大的索引性能更好一些。但它的缺点是要维护自组的列。

(6)尽量分析出每一个重要查询的使用频度，这样可以找出使用最多的索引，然后可以先对这些索引进行适当的优化。

(7)查询中的WHERE子句中的任何列都很可能是个索引列，因为优化器重点处理这个子句。

(8)对小于1个范围的小型表进行索引是不划算的，因为对于小表来说表扫描往往更快而且费用低。

(9)与“ORDER BY”或“GROUP BY”一起使用的列一般适于做分族索引。如果“ORDER BY”命令中用到的列上有分簇索引，那么就不会再生成1个工作表了，因为行已经排序了。“GROUP BY”命令则一定产生1个工作表。

(10)分簇索引不应该构造在经常变化的列上，因为这会引起整行的移动。在实现大型交易处理系统时，尤其要注意这一点，因为这些系统中数据往往是频繁变化的。

3.合并选择

当索引选择结束，并且所有的子句都有了一个基于它们的访问计划的处理费用时，优化器开始执行合并选择。合并选择被用来找出一个用于合并子句访问计划的有效顺序。为了做到这一点，优化器比较子句的不同排序，然后选出从物理磁盘I/O的角度看处理费用最低的合并计划。因为子句组合的数量会随着查询的复杂度极快地增长，SQL Server查询优化器使用树剪枝技术来尽量减少这些比较所带来的开支。当这个合并选择阶段结束时，SQL Server查询优化器已经生成了1个基于费用的查询执行计划，这个计划充分利用了可用的索引，并以最小的系统开支和良好的执行性能访问原来的数据。

3.2 高效的查询选择

从以上查询优化的3个阶段不难看出，设计出物理I/O和逻辑I/O最少的方案并掌握好处理器时间和I/O时间的平衡，是高效查询设计的主要目标。也就是说，希望设计出这样的查询:充分利用索引、磁盘读写最少、最高效地利用了内存和CPU资源。

以下建议是从SQL Server优化器的优化策略中总结出来的，对于设计高效的查询是很有帮助的。

1.如果有独特的索引，那么带有“=”操作符的WHERE子句性能最好，其次是封闭的区间(范围)，再其次是开放的区间。

2.从数据库访问的角度看，含有不连续连接词(OR和IN)的WHERE子句一般来说性能不会太好。所以，优化器可能会采用R策略，这种策略会生成1个工作表，其中含有每个可能匹配的执行的标识符，优化器把这些行标志符(页号和行号)看做是指向1个表中匹配的行的“动态索引”。优化器只需扫描工作表，取出每一个行标志符，再从数据表中取得相应的行，所以R策略的代价是生成工作表。

3.包含NOT、、或! =的WHERE子句对于优化器的索引选择来说没有什么用处。因为这样的子句是排斥性的，而不是包括性的，所以在扫描整个原来数据表之前无法确定子句的选择性。

4.限制数据转换和串操作，优化器一般不会根据WHERE子句中的表达式和数据转换式生成索引选择。例如:

paycheck * 12>36000 or substring(lastname,1,1)=“L”

如果该表建立了针对paycheck和lastname的索引，就不能利用索引进行优化，可以改写上面的条件表达式为:

paycheck<36000/12 or lastname like “L%”

5.WHERE子句中的本地变量被认为是不被优化器知道和考虑的，例外的情况是定义为储备过程输入参数的变量。

6.如果没有包含合并子句的索引，那么优化器构造1个工作表以存放合并中最小的表中的行。然后再在这个表上构造1个分簇索引以完成一个高效的合并。这种作法的代价是工作表的生成和随后的分族索引的生成，这个过程叫REFORMATTING。所以应该注意RAM中或磁盘上的数据库tempdb的大小(除了SELECT INTO语句)。另外，如果这些类型的操作是很常见的，那么把tempdb放在RAM中对于提高性能是很有好处的。

4 性能优化的其他考虑

上面列出了影响SQL Server的一些主要因素，实际上远不止这些。操作系统的影响也很大，在Windows NT下，文件系统的选择、网络协议、开启的服务、SQL Server的优先级等选项也不同程度上影响了SQL Server的性能。

影响性能的因素是如此的多，而应用又各不相同，找出1个通用的优化方案是不现实的，在系统开发和维护的过程中必须针对运行的情况，不断加以调整。事实上，绝大部分的优化和调整工作是在与客户端独立的服务器上进行的，因此也是现实可行的。