.Net+SQL Server企业应用性能优化笔记3——SQL查询语句

第一篇：.Net+SQL Server企业应用性能优化笔记3——SQL查询语句

在上一篇文章中我们使用了几种方法来确定瓶颈，找到瓶颈，下面再回顾一下：

LoadRunner压力测试+Windows计数器，这种方法主要是找出大概的性能问题是在哪台服务器，主要是哪个资源紧张。

 ANTS Profiler+SQL Server Profiler，这两个工具的完美搭配可以准确的定位性能是出在哪个函数，哪个SQL语句上。

如果性能问题是出在程序上，那么就要根据业务对程序中的函数进行调整，可能是函数中的写法有问题，算法有问题，这种调整如果不能解决问题的话，那么 就要从架构上进行考虑，我们是不是应该使用这种技术，有没有替代的方案来实现同样的业务功能？举个简单的例子，假设经过跟踪发现，一个负责生成图表的函数 存在性能问题，尤其是在压力测试情况下性能问题尤为严重。原来的图表生成是完全基于GDI+在Web服务器上根据数据进行复杂的绘图，然后将绘出的图片保 存在磁盘上，然后在HTML中添加Img标签来引用图片的地址。现在使用GDI+会消耗大量内存和CPU，而算法上也没有太大的问题，那么这种情况下我们 就需要考虑修改架构，不使用GDI+ 绘图的方式，或者是使用异步绘图的方式。既然绘图会消耗大量的服务器资源，那么一种解决办法就是将绘图的操作从服务器转移到客户端。使用 SilverLight技术，在用户打开网页是只是下载了一个SilverLight文件，该文件负责调用Web服务器的Web服务，将绘图所需的数据获 取下来，然后在客户端绘图展现出来。这样服务器只提供WebService的数据访问接口，不需要做绘图操作。

.net上的优化我暂时不表，今天主要讲数据库的优化。使用ANTS Profiler+SQL Server Profiler我们可以精确定位某个业务操作对应的数据库脚本或者存储过程。ANTS Profiler告诉我们一个方法在调用的时候花了10秒的时间，那么我们就可以使用VS打开源代码，找到该放入，然后找到对应调用的存储过程，这里也许 一个方法里面调用了多个数据层方法，调用了多个存储过程。将调用的这些存储过程记下了，然后在SQL Server Provider的跟踪文件里面去找调用该存储过程花费的Duration。

据的时间

一般企业应用或小型应用中数据库服务器和Web服务器要不是就在同一个机房，同一个局域网，或者干脆是同一台机器，这种情况下网络传输速度是很快 的，所以我们不考虑网络传送上面的时间。那么就得出：的存储过程的Duration)

代码中的时间得到了，SQL Server中的时间（也就是Duration字段）得到了，那么就可以判断出打开该页面各个服务器所花费的时间，从而找到我们要优化的方向，是存储过程 还是C#代码。如果是存储过程，那么通过查询SQL Server Profiler中内容可以找到具体是哪一个存储过程消耗的时间最长。

“射人先射马，擒贼先擒王。”多个存储过程被调用，如果性能出在数据库服务器上，那么进行性能优化时首先要调优的是最大Duration最大的存储 过程，另外还有就是Reads很大的存储过程。如果Duration很大但是Reads和Writes都不算特别大，那么有可能是以下原因：

1.这个存储过程相关的资源正在被其他事务占用，也就是说该存储过程被阻塞所以才花了那么多时间。这种情况只需要把该存储过程提出，多执行几 次，看是不是仍然Duration很大但Reads不大。

2.存储过程本身很复杂，里面的T-SQL语句就是五六百行，编译出的执行计划也是一堆，里面进行了大量的逻辑判断、大量函数的调用，这种情 况下进行调优就比较痛苦了。实际上这次我调优的这个项目就是如此，抓取出来的存储过程尽是复杂的逻辑，少则两三百行代码，多则五六百行，里面还有大量的用 户定义函数的调用。对于这种存储过程，我接下来会专门写篇博客介绍下我们这个项目是如何调优的。

3.程序读取的数据不多，但是需要对数据进行大量的运算。哈希联接、聚合函数、DISTINCT、UNION等都是比较耗CPU的。如果是这 种情况那就看能不能建立索引或者改写法进行调优。

前面说的是Duration大而Reads小的情况，当然更常见的情况是Duration和Reads都很大。那么我们就将主要精力集中在如何减小 Reads上。造成Reads很多的原因大概有以下几种：

1.没有建立相应的索引。对表t1进行查询，条件是where c2=abc返回c1,c2,c3三个字段，那么这种情况下如果没有对c2建立非聚集索引（c1是主键，建立了聚集索引），那么这个查询将会进行“聚集索 引扫描”，本来可能只查出几条记录的，结果要把表的所有记录都扫描一篇，自然Reads就高了。解决办法就是建立相应的索引，比如这里只需要对c2字段建 立非聚集索引，然后将c3字段作为包行列就行了。如果只是最c2字段建立非聚集索引，那么前面说到的查找在进行了“非聚集索引查找”后还会进行“键查找” 来找到c3列的值，所以要建立的正确的索引才行。

2.不符合SARG原则。查询如果不符合SARG原则，那么即使建立了索引也没法使用。SARG就是查询参数的意思，具体怎么写才符合 SARG，大家可以百度，已经有很多相关文章了，我就不累述。

3.涉及的业务数据量大。也就是说即使建立了正确的索引，查询也符合SARG使用到了该索引，但是由于涉及的数据量太大了，所以Reads仍 然很大。这种情况就不能再从索引和查询入手，而只能从数据库的设计入手。是否能够增加适当的冗余字段，对数据库进行反范式化，或者如果数据的实时性要

求不 高的话则可以建立中间汇总表，使用SQL作业来维护这个中间汇总表，查询的时候只查询该中间汇总表即可。或者是否可以建立索引视图或者计算列，然后在计算 列中建立索引的方式进行一个预运算，减小实际查询时涉及的数据量。

4.使用了不当的视图。如果对视图的定义很复杂，涉及的表很多，在查询的时候使用了该视图，但是实际上只用到了视图中的一张或两张表，对视图 的查询会造成系统根据视图定义查询其他与该查询不相关的表。所以在使用视图的时候一定要知道视图的定义，不用贪图一时的方便而随便使用视图。

5.不正确的使用了用户定义函数。一个存储过程中几百行代码，出于编写方便，大量的调用了一个用户定义表值函数，而该函数是进行了复杂的查询 和运算才返回结果的。如果数次或者数十次的调用该用户定义表值函数，那么就会进行很多这种复杂的查询和运算，自然Reads也就很大了。解决办法是尽量减 少对这种复制函数的调用，比如一次调用后就将解决保存在表变量或临时表中，接下来再使用的话就使用该表变量或临时表即可。

如果Duration并不大，但是Reads却很大的查询仍然需要需要进行优化。虽然表现出来消耗的时间并不大，但是由于Reads很多，那么说明 要进行大量的IO，在高并发的情况下大量的IO处理不过来会加重磁盘的负担，造成CPU占用率上升，性能降低，这时其Duration就会变大。关于 Duration不大但是Reads很大的情况仍然是前面说到的几点情况，建立相关索引、修改查询语句等便可解决。

第二篇：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查询语句学习笔记

问题描述：

为管理岗位业务培训信息，建立3个表:

S(SID,SNAME,SDEPT,SEX,AGE)SID,SNAME,SDEPT,SEX,AGE 分别代表学号、学员姓名、所属部门、学员性别、学员年龄

C(CID,CNAME)CID,CNAME 分别代表课程编号、课程名称

SC(SID,CID,GRADE)SID,CID,GRADE 分别代表学号、所选修的课程编号、学习成绩

表结构与数据见Data4SqlTest1.sql

要求实现如下5个处理：

1.使用标准SQL嵌套语句查询选修课程名称为“英语”的学员学号和姓名

2.使用标准SQL Join语句查询选修课程名称为“英语”的学员学号和姓名

3.使用标准SQL语句查询不选修“数学”课程的学员姓名和所属单位

4.使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

5.查询选修了课程的学员人数

6.查询选修课程超过3门的学员学号和所属单位

1.使用标准SQL嵌套语句查询选修课程名称为“英语”的学员学号和姓名 Java代码

1.SELECT SNAME,SDEPT FROM S

2.WHERE SID IN（3.SELECT SID FROM C,SC

4.WHERE C.CID=SC.CID

5.AND CNAME='英语')

2.使用标准SQL Join语句查询选修课程名称为“英语”的学员学号和姓名 Java代码

1.SELECT SNAME,SDEPT FROM S

2.JOIN SC ON S.SID=SC.SID JOIN C ON C.CID=SC.CID

3.WHERE CNAME='英语'

3.使用标准SQL语句查询不选修“数学”课程的学员姓名和所属单位 Java代码

1.select SNAME,SDEPT

2.from S

3.where SID not in（4.select SC.SID

5.from SC

6.right join(select S.SID,C.CID from S join C)as T on SC.SID=T.SID

and SC.CID=T.CID join C on T.CID=C.CID

7.where C.CNAME='数学' and SC.SID is not null

8.)

或者是：

Java代码

1.SELECT SNAME,SDEPT FROM S

2.WHERE SID NOT IN（3.SELECT SID FROM SC

4.WHERE CID IN(SELECT CID FROM C WHERE CNAME='数学'))

4.使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位 Java代码

1.select SNAME,SDEPT

2.from S

3.where SID in

4.(select S.SID

5.from S

6.inner join SC on S.SID=SC.SID

7.group by S.SID

8.having count(CID)=(select count(1)from C)

9.)

5.查询选修了课程的学员人数

Java代码

1.SELECT 学员人数=COUNT(DISTINCT(SID))FROM SC

6.查询选修课程超过3门的学员学号和所属单位

Java代码

1.SELECT SNAME,SDEPT FROM S

2.WHERE SID IN（3.SELECT SID FROM SC

4.GROUP BY SID

5.HAVING COUNT(DISTINCT(CID))>3)