优化SQL语句需要注意的4个要点

时间:2019-05-12 20:35:00下载本文作者:会员上传
简介:写写帮文库小编为你整理了多篇相关的《优化SQL语句需要注意的4个要点》,但愿对你工作学习有帮助,当然你在写写帮文库还可以找到更多《优化SQL语句需要注意的4个要点》。

第一篇:优化SQL语句需要注意的4个要点

1.尽量不要对列名进行函数处理。而是针对后面的值进行处理 例如where col1 =-5的效率比where-col1=5的效率要高

因为后面的条件对列值进行了计算。这样的条件下优化器无法使用索引 而是要针对所有值进行计算之后才能再比较

2.尽量使用和数剧列一样的值进行操作

如果col1是数值型

那么例如where col1 = 2和where col1= ‘2′

则前者效率更高

因为比较字符和数值型的时候

引擎需要把两者都转化成双精度然后进行比较

3.减少函数的使用

例如where col1 >= ‘2009-10-26′ and col1 <= ‘2009-10-27′

和where datediff(day,col1,getdate())=0

后者因为用到函数处理。所以col1上的索引又无法使用了

4.尽量不要用OR

一般对于OR的条件

优化器一般会使用全表扫描

第二篇:SQL语句性能优化

我也做了很长时间医疗软件,也写过不少sql优化,没有详细记录下来,个人感觉下面转载的更符合医院医疗软件实际业务,很认可大部分所写的原则,固转载过来,以作借鉴。软件的根本还是在于更细更精,在于从客户的实际使用考虑问题。

性能优化原则1:永远避免困境

利用缓存把字典数据取到中间服务器或是客户端替代直接sql查询,如,门诊医生站把字典下载到客户端,减少执行次数。

一次性取数据到客户端,然后再逐条处理,而不是分次取数据,处理好一条数据再取下一条再处理。例:门诊收费取hjcfmxk例子,原来是一张处方条明细都查询一次,查询后再处理,现改为一次把所有明细都取过来,然后一条条处理

尽量减少光标,看能不能用临时表

性能优化原则2:kiss原则

对于where 条件中的左边可以利用索引的字段Keep it simple stupid,左边尽量避免用函数(substring,isnull,upper,lower),参加计算+,-*/

例子1:select * from ZY_BRFYMXK where substring(zxrq,1,8)='20081212‘

select * from ZY_BRFYMXK where zxrq between '2008121200' and '2008121224' 例子2:

select * from zy_detail_charge where SUBSTRING(patient_id,1,10)=

substring('000005090600',1,10)这句耗时30秒以上

select * from zy_detail_charge where patient_id like substring('000005090600',1,10)+'%' 这句耗时2秒以内

性能优化原则3:尽可能利用到索引

例:select * from ZY_BRFYMXK a(nolock),VW_LSYZK b(nolock)where a.syxh=3 and a.yzxh=b.xh and a.fylb=0

select * from ZY_BRFYMXK a(nolock),VW_LSYZK b(nolock)where a.syxh=3 and a.yzxh=b.xh and a.fylb=0 and b.syxh=3

性能优化原则4:or,避而远之

对于索引字段尽力避免用or,普通字段可以用or,解决要么分解成多个sql,要么用业务规则避免,例:declare @rq1 ut_rq16,@syxh ut_syxh

select @rq1='20081201'

select @syxh=157

性能优化原则5:避免大批量数据取到前台

例: select * from ZY_BRSYK cyrq between ‘20080901’ and ‘20081201‘,对于大医院每天100多人,90天是9000条数据

性能优化原则6:事务,尽可能的短吧

所有计算、对临时表的更新都应但放在事务外,事务中最好只有更新和插入正式表操作.因为事务中产生的锁只有在commit tran是才会释放。

性能优化原则7:热表,留在最后吧

热表是频繁调用的表。如:sf_mzcfk,zy_brfymxk,bq_fyqqk.对于热表尽量放在事务最后:这样锁的时间短。大家都坚持这样,死锁的可能性就小。如果都是热表各个存储过程更新表的顺序应当一样这样可以避免死锁

性能优化原则8:创建临时表一定要避免在事务中作

如create #tempXX(…)

Select * into #tempXX from …

因为创建临时表会锁tempdb的系统表

例:生成#temp1放在事务内外,用sp_lock2 ‘’观察结果

if object_id('tempdb..#temp1','U')is not null

drop table #temp1

begin tran

select * into #temp1 from ZY_BRSYK where ryrq>'20080901‘

select * from #temp1

waitfor delay '00:00:10'

commit

性能优化原则9:大的报表查询避免与正常业务碰撞

如果没有查询服务器,那要在存储过程中限制不能操作加上如:

declare @rq1 ut_rq16,@rq2 ut_rq16,@now ut_rq16

select @rq1=convert(varchar(8),getdate(),112)+'08:00:00'

select @rq1=convert(varchar(8),getdate(),112)+'11:30:00'

select @now=convert(char(8),getdate(),112)+convert(char(8),getdate(),8)

if @now>@rq1 and @now<@rq2

begin

select '上午繁忙时间段不能作此查询'

return

end

性能优化原则10:存储过程避免大的if…else…

这个常出项在业务相同表不同的存储过程中,因为这样常到if …else …原来医技接口中很多这种存储过程,当时把门诊住院业务放在一个存储过程中。这样最大的问题是sql server会根据sql语句来compile存储,这个过程会生成优化计划,决定用那个索引。如果存储过程用到门诊表compile一下,到用到住院表是发现不对,又会compile一下,这样不停compile.compile很号时间要1-2秒,而且一个存储过成在compile是,所有调用这个存储过程的进程都要在排队等候,因为他会独占锁这个存储过程

例:usp_yjjk_getwzxxm_old.sql,后改为:

usp_yjjk_getwzxxm.sql, usp_yjjk_getwzxxm_mz.sql,usp_yjjk_getwzxxm_zy.sql

性能优化原则11:进攻是最好的防守

在普通编程语句对于数据校验总是用防守办法先判断,后再作相应处理。而在sql中先处理再判断性能会好很多。

--更新药品库存。

If exists(select 1 from YK_YKZKC WHERE idm=100 and kcsl>50)

begin

update YK_YKZKC set kcsl=kcsl-50 where idm=100

End

Else begin

rollback tran

Select ‘F库存不够’

return

end

--改为

update YK_YKZKC set kcsl=kcsl-50 where idm=100 and kcsl>50

If @@rowcount<=0

Begin

Rollbakc tran

Select ‘F库存不够’

end

--取未执行的医技项目,日表没有数据就到年表中查找

if exists(select a.* from SF_MZCFK a(nolock),SF_CFMXK b(nolock)

begin

select a.* into #temp1 from SF_MZCFK a(nolock),SF_CFMXK b(nolock)

end

else begin

select a.* into #temp1 from SF_NMZCFK a(nolock),SF_NCFMXK b(nolock)

end

--改为

Insert into #temp1 select a.*

from SF_MZCFK a(nolock),SF_CFMXK b(nolock)

If @@rowcount=0

Begin

Insert into #temp1 select a.*

from SF_NMZCFK a(nolock),SF_NCFMXK b(nolock)

end

性能优化原则12:trig最后的手段

Trig(触发器)的处理的处理机制是满足条件时就会在源语句后面加上trig中的代码进行执行。

它有两个致命的弊端:(1)不清楚有trig的人会对于执行结果感到迷惑。如常有在插入一张表如果主键是indentity的值常取用select @@identity。但如是有trig,tring中有表插入操作,这时的@@identity可能就不是想要的值。(2)trig会束缚选择。如:有一套单据主表和明细表,当明细表的金额更新时,要同步主表的金额,当程序是一条条更新明细时用trig的作法是每当更新一条明细记录时都算一处所有明细表的总金额,再去更新主表的金额。这样有多少条明细就要算多少次,好的作法是不要trig,直接在sql语句中明细更新完明后,一次性算出总金额每条单据的总金额,再更新主表的金额。

对于trig如果有其他手段就一定要避免用trig.性能优化原则13:用户说好才是真的好

1)有时sql语句性能难以优化,但用户对于系统响应速度还是不满意。这时可以从业务分析处理。

如:我们退费模块录入发票号原来是用fph like ‘XXX%’。用户报怨慢,后来改为先用fph=‘XXX’来查,如查不到再fph like ‘XXX%’。这样在绝大部情况下速度都非常快,同时也满足小部分情况下模糊查询的需求。

如:我们的程序要查日表和年表。如果通过日表union表视图去查会非常慢,性能也难以优化。程序改为普通情况下不查年表,用户勾上年表标志时才查年表。

(2)查询统计很多数据时间比较长,就以查询完一部分数据后可以显示这部分数据或是用提示,这样用户清楚系统在作事情也知道大概进度。这样情绪上会好很多。

(3)查询模块常有一进入时也默认一个查询,如果性能好,查询又合用户心意,这种设计非常好,如果性能不好,那就不是好的设计。用户对于进入都困难的模块是没有好感的。

(4)有户的耐心与查询出的记录成正比。用户痛恨等待很久却没有查询出记录。

对于非常慢的查询,如果有些子查询非常快可以先作这样查询以避免查询很久却没有数据出来的情况。如:按病历号查在院病人所有费有明细,可以先查一下这个病历是不是有对应病人。

实战技巧1:用exists、in代替distinct

Distinct实际上是先收集再删除这样两步都耗资源。

Exists,in会隐式过滤掉重复的记录

例查自2009年以来有金额大于100的药品的病人

select distinct a.blh,a.hzxm from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock)where a.patid=b.patid and b.syxh=c.syxh and c.zxrq>'2009' and c.zje>100--改为

select a.blh,a.hzxm from ZY_BRXXK a where exists(select 1 from ZY_BRSYK

b(nolock),ZY_BRFYMXK c(nolock)where a.patid=b.patid and b.syxh=c.syxh and

c.zxrq>'2009'and c.zje>100)

实战技巧2:缩短union

select …from A,B,C,D,E1

where(E1的条件)

and(其他表联接条件)

union

select …from A,B,C,D,E2

where(E2的条件)

and(其他表接接条件)

改为

select …from A,B,C,D,(select...from E1where(E1条件)

union

select …from E2where(E2条件))E where(其他条件)

当涉及ABCD表部分耗资源而E1,E2不耗资源时是这样,如果反过来则改后的性能不一定好。查2009年4月后入院的在院病人在2905病区发生的所有费用明细

select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw

select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw

from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),YK_YPCDMLK d where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.idm=d.idm

union all

select a.hzxm,b.cyrq,d.name,d.xmgg,c.ypsl/c.dwxs ypsl, c.ypdw

from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),YY_SFXXMK d where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.ypdm=d.id and c.idm=0

--改为

select a.hzxm,b.cyrq,d.ypmc,d.ypgg,c.ypsl/c.dwxs ypsl, c.ypdw

from ZY_BRXXK a(nolock),ZY_BRSYK b(nolock),ZY_BRFYMXK c(nolock),(select ypmc,ypgg,ypdm,idm idm from YK_YPCDMLK union select name,xmgg,id,0 from YY_SFXXMK)d

where a.patid=b.patid and b.ryrq>'200904' and b.brzt not in(3,8,9)and b.syxh=c.syxh and c.bqdm='2905' and c.idm=d.idm and c.ypdm=d.ypdm

实战技巧3:合并sql

把表和where条件类似的两个或是多个sql合并为一个sql.--查2009年以后的普通、急诊、专家挂号人数

declare @ptghs int,@jzghs int,@zjghs int

select @ptghs=0,@jzghs=0,@zjghs=0

select @ptghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=0

select @jzghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=1

select @zjghs=count(*)from GH_GHZDK where ghrq>'2009' and ghlb=2

select @ptghs,@jzghs,@zjghs

--改为

select @ptghs=0,@jzghs=0,@zjghs=0

select @ptghs=sum(case when ghlb=0 then 1 else 0 end),@jzghs=sum(case when ghlb=1 then 1 else 0 end), @zjghs=sum(case when ghlb=2 then 1 else 0 end)

from GH_GHZDK where ghrq>'2009'

select @ptghs,@jzghs,@zjghs

实战技巧4:去掉游标

把游标当作编程语言的for,do---while的方式,很多情况下都可以去掉,如果光标中间sql语句只有一条一般都是可以去掉光标改为一句sql。

--查当天出院出院日期在2009年4月1到9日间病人的zfdj,zfje置为0

declare @syxh ut_syxh

declare cur1 cursor for select syxh from ZY_BRSYK where cyrq>='20090401' and cyrq<'20090410'

open cur1

fetch cur1 into @syxh

while @@fetch_status=0

begin

fetch cur1 into @syxh

end

close cur1

deallocate cur1

--改为

update ZY_BRFYMXK set zfdj=0,zfje=0

from ZY_BRFYMXK a,ZY_BRSYK b

where a.syxh=b.syxh and b.cyrq>='20090401' and b.cyrq<'20090410'

实战技巧5:取代count

利用内部函数代替

declare @count int

select * into #tmep1 from ZY_BRFYMXK WHERE zxrq>'200901'

select @count=@@rowcount—可以得到count值

select @count

select @count=count(*)from #tmep1—可以被取代

select @count

利用exists而不count判断有没有记录

declare @count int

Select @count=count(1)from ZY_BRFYMXK WHERE zxrq>'2009‘

If @count>0 … else ….--改为

If exists(Select 1 from ZY_BRFYMXK WHERE zxrq>'2009’)… else ….

第三篇:SQL语句的优化方法

SQL语句的优化方法

1.1注释使用

在语句中多写注释,注释不影响SQL语句的执行效率。增加代码的可读性。

1.2对于事务的使用

尽量使事务处理达到最短,如果事务太长最好按功能将事务分开执行(如:可以让用户在界面上多几步操作)。事务太长很容易造成数据库阻塞,用户操作速度变慢或死机情况。

1.3对于与数据库的交互

尽量减少与数据库的交互次数。如果在前端程序写有循球访问数据库操作,最好写成将数据一次读到前端再进行处理或者写成存储过程在数据库端直接处理。

1.4对于SELECT *这样的语句,不要使用SELECT *这样的语句,而应该使用SELECT table1.column1这样的语句,明确指出要查询的列减少数据的通讯量并且这样的代码可读性好,便于维护。

1.5尽量避免使用游标

它占用大量的资源。如果需要row-by-row地执行,尽量采用非光标技术,如:在客户端循环,用临时表,Table变量,用子查询,用Case语句等等。如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。

1.6尽量使用count(1)

count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。

1.7IN和EXISTS

EXISTS要远比IN的效率高。里面关系到full table scan和range scan。几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

1.8注意表之间连接的数据类型

避免不同类型数据之间的连接。

1.9尽量少用视图

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

1.10没有必要时不要用DISTINCT和ORDER BY

这些动作可以改在客户端执行,它们增加了额外的开销。

1.11避免相关子查询

一个列的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。

1.1注意UNion和`UNion all 的区别

UNION all执行效率高。

1.1外键关联的列应该建立索引

(如子表id)主子表单据肯定要建视图,2个表的关联以2个表中的MainID为关系,所以,需要给子表的MainID单独建索引,这将很大地提高视图的速度。例如Gy_InOutSub中的InoutMainid增加索引。

第四篇:sql常用语句

//创建临时表空间

create temporary tablespace test_temp

tempfile 'E:oracleproduct10.2.0oradatatestservertest_temp01.dbf'size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

//创建数据表空间

create tablespace test_data

logging

datafile 'E:oracleproduct10.2.0oradatatestservertest_data01.dbf'size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

//创建用户并指定表空间

create user username identified by password

default tablespace test_data

temporary tablespace test_temp;

//给用户授予权限

//一般用户

grant connect,resource to username;

//系统权限

grant connect,dba,resource to username

//创建用户

create user user01 identified by u01

//建表

create table test7272(id number(10),name varchar2(20),age number(4),joindate date default sysdate,primary key(id));

//存储过程

//数据库连接池

数据库连接池负责分配、管理和释放数据库连接

//

//创建表空间

create tablespace thirdspace

datafile 'C:/Program Files/Oracle/thirdspace.dbf' size 10mautoextend on;

//创建用户

create user binbin

identified by binbin

default tablespace firstspace

temporary tablespace temp;

//赋予权限

GRANT CONNECT, SYSDBA, RESOURCE to binbin

//null与""的区别

简单点说null表示还没new出对象,就是还没开辟空间

个对象装的是空字符串。

//建视图

create view viewname

as

sql

//建索引

create index indexname on tablename(columnname)

//在表中增加一列

alter table tablename add columnname columntype

//删除一列

alter table tablename drop columnname

//删除表格内容,表格结构不变

truncate table tableneme

//新增数据

insert into tablename()values()

//直接新增多条数据

insert into tablename()

selecte a,b,c

from tableabc

//更新数据 new除了对象,但是这“”表示

update tablename set columnname=? where

//删除数据

delete from tablename

where

//union语句

sql

union

sql

//case

case

when then

else

end

第五篇:SQL语句

SQL练习

一、设有如下的关系模式,试用SQL语句完成以下操作:

学生(学号,姓名,性别,年龄,所在系)

课程(课程号,课程名,学分,学期,学时)

选课(学号,课程号,成绩)

1. 求选修了课程号为“C2”课的学生学号

2. 求选修了课程号为“C2”课的学生学号和姓名

3. 求没有选修课程号为“C2”课的学生学号

4. 求选修了课程号为“C2”,又选修了课程号为“C3”课的学生学号

5.求选修了课程号为“C2”或“C3”课的学生学号

6.求选修了全部课程的学生学号

7.求至少选修了学号为“98002”的学生所学过的所有课程的学生的学号和姓名。

8.查询学生选课表中的全部数据

9.查询计算机系学生的姓名、年龄

10.查询成绩在70—80分之间的学生的学号、课程号和成绩

11.查询计算机系年龄在18—20之间且性别为“男”的学生的姓名和年龄

12.查询成绩在80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。

13.查询哪些课程没有人选修,要求列出课程号和课程名。

14.查询数学系成绩在80分以上的学生的学号,姓名

15.查询课程号为“C02”的课程的最高分数。

16.查询计算机系学生的最大年龄和最小年龄。

17.统计每个系的学生人数。

18.统计每门课程的选课人数和考试最高分。

19.统计每个学生的选课门数和考试总成绩,并按选课门数的升序显示结果。

20.查询总成绩超过200分的学生,要求列出学号、总成绩。

21.用子查询实现如下查询:

(1)查询选修了课程号“C01”的学生的姓名和所在系。

(2)查询数学系成绩在80分以上的学生的学号和姓名。

(3)查询计算机系考试成绩最高的学生的姓名。

22.删除选课成绩小于60分的学生的选课记录。

23.将所有选修了课程“C01”的学生的成绩加10分。

24.将计算机系所有选修了课程“计算机文化基础”课程的学生的成绩加10分。

25.创建查询学生的学号、姓名、所在系、课程号、课程名、课程学分的视图。

26.创建查询每个学生的平均成绩的视图,要求列出学生学号和平均成绩。

27.创建查询每个学生的选课学分的视图,要求列出学生学号及总学分。

下载优化SQL语句需要注意的4个要点word格式文档
下载优化SQL语句需要注意的4个要点.doc
将本文档下载到自己电脑,方便修改和收藏,请勿使用迅雷等下载。
点此处下载文档

文档为doc格式


声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:645879355@qq.com 进行举报,并提供相关证据,工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。

相关范文推荐

    SQL语句

    SQL语句,用友的SQL2000,通过查询管理器写的语句 1、查询 2、修改 3、删除 4、插入表名:users 包含字段:id,sname,sage 查询 select * from users查询users表中所有数据 select i......

    常用SQL语句

    一、创建数据库 create database 数据库名 on( name='数据库名_data', size='数据库文件大小', maxsize='数据库文件最大值', filegrowth=5%,//数据库文件的增长率 filename......

    sql语句

    简单基本的sql语句 几个简单的基本的sql语句 选择:select * from table1 where范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1......

    常用sql语句

    1、查看表空间的名称及大小 select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d......

    SQL优化要点总结(共5篇)

    SQL优化要点总结 文章比较简短,但很实用,SQL优化要点总结: 1、尽量避免大表的全表扫描,建立合适的索引,务必注意索引是把双刃剑,不能滥用,用好则已,用不好伤人害已。 2、能不......

    简单SQL语句小结

    简单SQL语句小结 注释:本文假定已经建立了一个学生成绩管理数据库,全文均以学生成绩的管理为例来描述。 1.在查询结果中显示列名: a. 用as关键字:select name as '姓名' from s......

    常用SQL语句(最终五篇)

    查询:select * from table1 where 范围 select * from table where table001='JSBQF050' and table002='1307050002' and table003='0020' and table004='0030' select * fro......

    sql语句学习

    一、选择题 1、SQL语言是语言。——(SQL特点)(易) A)层次数据库 B)网络数据库 C)关系数据库D)非数据库 答案:C 2、SQL语言具有两种使用方式,分别称为交互式SQL和。 ——(SQL语言使......