1.加索引,中间表(RDMDS)数据没有索引,查询非常慢,加索引之后,性能提升很明显;
2.更新IPMT执行秘书,IPMT主任时,删除SQL中的函数;
3.删除游标,(C-Marketing接口人,研发项目管理处主管(PMO), 研发产品线总监, PL CCB执行秘书, PL CCB主任);
4.修改从RDMDS角色同步到WEBDP错误角色(PDT核心组群组,研发核心组群组等)
5.substring(A.NotesUser,1,1)+substring(A.NotesUser, charindex(' ',A.NotesUser)+1,5)代替dbo.ConvertNotesFormatToPinyinAccountFirst(A.RNDPDT_ID)
6.sqlserver分页语句
--top 分页查询
SELECT TOP 10 * FROM dbo.TaskDelivery WHERE TaskDeliveryID NOT IN (SELECT TOP 10 TaskDeliveryID FROM dbo.TaskDelivery)
--rownumber 分页查询
SELECT * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY TaskDeliveryID) AS rownumber FROM dbo.TaskDelivery) AS b WHERE b.rownumber BETWEEN (1-1*10) AND (1*10);
--offset 页数 row fetch next 条数 row only SQL2012以上的版本才支持
SELECT * FROM dbo.TaskDelivery ORDER BY TaskDeliveryID OFFSET 0 ROW FETCH NEXT 10 ROW ONLY
top not in方式:查询靠前的数据速度较快
ROW_NUMBER() OVER()方式:查询靠后的数据速度比上一种较快
offset fetch next方式:速度稳定,优于前2种,但sql版本限制2012及以上才可使用
查看锁表
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'
查重
SELECT E2ETaskID FROM dbo.E2ETask WHERE E2ETaskID IN (SELECT E2ETaskID FROM dbo.E2ETask GROUP BY E2ETaskID HAVING COUNT(1)>1)
SELECT TOP 3 WITH TIES * FROM dbo.IPDPhase ORDER BY CreationDate 加上WITH TIES在*前面,列出所有与第10%个记录值相同的记录
一般常见的代码中的性能问题:
1、 循环中数据获取,多次打开数据库连接
2、 数据读写无序,最优的写法是先把所有的数据准备好,一起写入
3、 有些更适合在数据库中实现,比如集合处理、集合更新等,不要再代码中实现
4、 采用直接的处理方法,减少无益的复用
一般数据库中可能的性能问题:
1、 索引未定时重组;
2、 数据库设计的原则要遵守,但是因时而动,不能严格遵守,比如适当的冗余;
3、 不可缺少的主键以及有效的索引;
4、 避免索引失效的写法等数据库查询更优的写法;
5、 最后装饰原则,不要层层封装视图,性能会严重受影响;
6、 预估数据量变化,恰当的数据库设计,适当的拆表和合表;
7、 减少I/0,只查询需要数据。
DB设计
从用户要求的性能需求出发,分析系统中操作的类别,区别开读数据库操作和写数据库操作,根据读写功能的分布以及读写及时性的判断决定是否读写分离。
从数据的增长量和数据库设计结构,决定是否需要数据归档,对数据进行分表分库设计。
一些数据库设计细节:
1、不要使用游标。
使用游标不仅占用内存,而且还用不可思议的方式锁定表,它们可以使DBA所能做的一切性能优化等于没做。游标里每执行一次fetch就等于执行一次select。
2、创建适当的索引
每当为一个表添加一个索引,select会更快,可insert和delete却大大变慢,因为创建了维护索引需要许多额外的工作。
(1)采用函数处理的字段不能利用索引
(2)条件内包括了多个本表的字段运算时不能进行索引
3、使用事务
对于一些耗时的操作,使用事务可以达到很好的优化效果。
4、小心死锁
按照一定的次序来访问你的表。如果你先锁住表A,再锁住表B,那么在所有的存储过程中都要按照这个顺序来锁定它们。 如果某个存储过程先锁定表B,再锁定表A,这可能会导致一个死锁。
5、不要打开大的数据集
6、不要使用服务器端游标
与服务器端游标比起来,客户端游标可以减少服务器和网络的系统开销,并且还减少锁定时间。
7、不要忽略同时修改同一记录的问题
有时候,两个用户会同时修改同一记录,这样,后一个修改者修改了前一个修改者的操作,某些更新就会丢失。处理这种情况,创建一个timestamp字段,在写入前检查它,如果允许,就合并修改,如果存在冲突,提示用户。
8、尽量不要使用text数据类型
除非使用text处理一个很大的数据,否则不要使用它。因为它不易于查询,速度慢,用的不好还会浪费大量的空间。一般varchar可以更好的处理数据。
9、避免在索引列上使用计算
where子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描。
10、不同类型的索引效能是不一样的,应尽可能先使用效能高的
数字类型的索引查找效率高于字符串类型,定长字符串char、nchar的索引效率高于变长字符串varchar、nvarchar的索引
SQL语句
避免数据库中可能的性能问题:
1、 索引未定时重组;
2、 数据库设计的原则要遵守,但是因时而动,不能严格遵守,比如适当的冗余;
3、 不可缺少的主键以及有效的索引;
4、 避免索引失效的写法等数据库查询更优的写法;
5、 最后装饰原则,不要层层封装视图,性能会严重受影响;
6、 预估数据量变化,恰当的数据库设计,适当的拆表和合表;
7、 减少I/0,只查询需要数据。
一些SQL语句的细节:
1、不要使用select *
在select中指定所需要的列,将带来的好处:
(1)减少内存耗费和网络的带宽
(2)更安全
(3)给查询优化器机会从索引读取所有需要的列
2、使用参数查询
主要是防止SQL注入,提高安全性。
3、使用exists或not exists代替in或not in
4、is null或is not null操作
判断字段是否为空一般是不会应用索引的,因为索引不索引空值。不能用null作索引,任何包含null值的列都将不会被包含在索引中。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器都不允许使用索引。
5、大于小于操作
大于或小于一般情况不用调整,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化。如一个表有100万记录,那么执行>2与>=3的效果就有很大区别了。
(低效)select * from [emp] where [emplid]>2;
(高效)select * from [emp] where [emplid]>=3;
6、where后面的条件顺序影响
where子句后面的条件顺序对大数据量表的查询会产生直接的影响,根据满足条件的数据,小范围的在前,范围较大的在后。
8、用union替换or(适用于索引列)
通常情况下,用union替换where子句中的or将会起到较好的效果。对索引列使用or将造成全表扫描。注意:这个规则只针对多个索引列有效。如果有column没有被索引,查询效率可能会因为你没有选择or而降低。
9、优化group by
提高group by语句的效率,可以通过将不需要的记录在group by之前过滤掉。
10、使用存储过程
可以考虑使用存储过程封装那些复杂的SQL语句或业务逻辑,这样有几个好处:
(1)存储过程的执行计划可以被缓存在内存中较长的时间,减少了重新编译的时间。
(2)存储过程减少了客户端和服务器的繁复交互。
(3)如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序,避免需要重新安装部署程序。
11、用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时,服务器自动取消查询,在查询之前就扼杀掉。SET LOCKTIME设置锁的时间。
12、使用select top或set rowcount来限制操作的行。
13、如果使用了in或or等时发现查询没有走索引,使用显式申明指定索引: SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN ('男','女')。
14、如果要插入大的二进制值到Image列,使用存储过程,千万不要用内嵌insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍),服务器受到字符后又将他转换成二进制值。存储过程就没有这些动作: 方法:Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数,这样处理速度明显改善。
15、参数传入时传入准确的数据类型,不要等运行时让DBMS进行转化
2.更新IPMT执行秘书,IPMT主任时,删除SQL中的函数;
3.删除游标,(C-Marketing接口人,研发项目管理处主管(PMO), 研发产品线总监, PL CCB执行秘书, PL CCB主任);
4.修改从RDMDS角色同步到WEBDP错误角色(PDT核心组群组,研发核心组群组等)
5.substring(A.NotesUser,1,1)+substring(A.NotesUser, charindex(' ',A.NotesUser)+1,5)代替dbo.ConvertNotesFormatToPinyinAccountFirst(A.RNDPDT_ID)
6.sqlserver分页语句
--top 分页查询
SELECT TOP 10 * FROM dbo.TaskDelivery WHERE TaskDeliveryID NOT IN (SELECT TOP 10 TaskDeliveryID FROM dbo.TaskDelivery)
--rownumber 分页查询
SELECT * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY TaskDeliveryID) AS rownumber FROM dbo.TaskDelivery) AS b WHERE b.rownumber BETWEEN (1-1*10) AND (1*10);
--offset 页数 row fetch next 条数 row only SQL2012以上的版本才支持
SELECT * FROM dbo.TaskDelivery ORDER BY TaskDeliveryID OFFSET 0 ROW FETCH NEXT 10 ROW ONLY
top not in方式:查询靠前的数据速度较快
ROW_NUMBER() OVER()方式:查询靠后的数据速度比上一种较快
offset fetch next方式:速度稳定,优于前2种,但sql版本限制2012及以上才可使用
查看锁表
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'
查重
SELECT E2ETaskID FROM dbo.E2ETask WHERE E2ETaskID IN (SELECT E2ETaskID FROM dbo.E2ETask GROUP BY E2ETaskID HAVING COUNT(1)>1)
SELECT TOP 3 WITH TIES * FROM dbo.IPDPhase ORDER BY CreationDate 加上WITH TIES在*前面,列出所有与第10%个记录值相同的记录
一般常见的代码中的性能问题:
1、 循环中数据获取,多次打开数据库连接
2、 数据读写无序,最优的写法是先把所有的数据准备好,一起写入
3、 有些更适合在数据库中实现,比如集合处理、集合更新等,不要再代码中实现
4、 采用直接的处理方法,减少无益的复用
一般数据库中可能的性能问题:
1、 索引未定时重组;
2、 数据库设计的原则要遵守,但是因时而动,不能严格遵守,比如适当的冗余;
3、 不可缺少的主键以及有效的索引;
4、 避免索引失效的写法等数据库查询更优的写法;
5、 最后装饰原则,不要层层封装视图,性能会严重受影响;
6、 预估数据量变化,恰当的数据库设计,适当的拆表和合表;
7、 减少I/0,只查询需要数据。
DB设计
从用户要求的性能需求出发,分析系统中操作的类别,区别开读数据库操作和写数据库操作,根据读写功能的分布以及读写及时性的判断决定是否读写分离。
从数据的增长量和数据库设计结构,决定是否需要数据归档,对数据进行分表分库设计。
一些数据库设计细节:
1、不要使用游标。
使用游标不仅占用内存,而且还用不可思议的方式锁定表,它们可以使DBA所能做的一切性能优化等于没做。游标里每执行一次fetch就等于执行一次select。
2、创建适当的索引
每当为一个表添加一个索引,select会更快,可insert和delete却大大变慢,因为创建了维护索引需要许多额外的工作。
(1)采用函数处理的字段不能利用索引
(2)条件内包括了多个本表的字段运算时不能进行索引
3、使用事务
对于一些耗时的操作,使用事务可以达到很好的优化效果。
4、小心死锁
按照一定的次序来访问你的表。如果你先锁住表A,再锁住表B,那么在所有的存储过程中都要按照这个顺序来锁定它们。 如果某个存储过程先锁定表B,再锁定表A,这可能会导致一个死锁。
5、不要打开大的数据集
6、不要使用服务器端游标
与服务器端游标比起来,客户端游标可以减少服务器和网络的系统开销,并且还减少锁定时间。
7、不要忽略同时修改同一记录的问题
有时候,两个用户会同时修改同一记录,这样,后一个修改者修改了前一个修改者的操作,某些更新就会丢失。处理这种情况,创建一个timestamp字段,在写入前检查它,如果允许,就合并修改,如果存在冲突,提示用户。
8、尽量不要使用text数据类型
除非使用text处理一个很大的数据,否则不要使用它。因为它不易于查询,速度慢,用的不好还会浪费大量的空间。一般varchar可以更好的处理数据。
9、避免在索引列上使用计算
where子句中,如果索引列是函数的一部分,优化器将不使用索引而使用全表扫描。
10、不同类型的索引效能是不一样的,应尽可能先使用效能高的
数字类型的索引查找效率高于字符串类型,定长字符串char、nchar的索引效率高于变长字符串varchar、nvarchar的索引
SQL语句
避免数据库中可能的性能问题:
1、 索引未定时重组;
2、 数据库设计的原则要遵守,但是因时而动,不能严格遵守,比如适当的冗余;
3、 不可缺少的主键以及有效的索引;
4、 避免索引失效的写法等数据库查询更优的写法;
5、 最后装饰原则,不要层层封装视图,性能会严重受影响;
6、 预估数据量变化,恰当的数据库设计,适当的拆表和合表;
7、 减少I/0,只查询需要数据。
一些SQL语句的细节:
1、不要使用select *
在select中指定所需要的列,将带来的好处:
(1)减少内存耗费和网络的带宽
(2)更安全
(3)给查询优化器机会从索引读取所有需要的列
2、使用参数查询
主要是防止SQL注入,提高安全性。
3、使用exists或not exists代替in或not in
4、is null或is not null操作
判断字段是否为空一般是不会应用索引的,因为索引不索引空值。不能用null作索引,任何包含null值的列都将不会被包含在索引中。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器都不允许使用索引。
5、大于小于操作
大于或小于一般情况不用调整,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化。如一个表有100万记录,那么执行>2与>=3的效果就有很大区别了。
(低效)select * from [emp] where [emplid]>2;
(高效)select * from [emp] where [emplid]>=3;
6、where后面的条件顺序影响
where子句后面的条件顺序对大数据量表的查询会产生直接的影响,根据满足条件的数据,小范围的在前,范围较大的在后。
8、用union替换or(适用于索引列)
通常情况下,用union替换where子句中的or将会起到较好的效果。对索引列使用or将造成全表扫描。注意:这个规则只针对多个索引列有效。如果有column没有被索引,查询效率可能会因为你没有选择or而降低。
9、优化group by
提高group by语句的效率,可以通过将不需要的记录在group by之前过滤掉。
10、使用存储过程
可以考虑使用存储过程封装那些复杂的SQL语句或业务逻辑,这样有几个好处:
(1)存储过程的执行计划可以被缓存在内存中较长的时间,减少了重新编译的时间。
(2)存储过程减少了客户端和服务器的繁复交互。
(3)如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序,避免需要重新安装部署程序。
11、用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时,服务器自动取消查询,在查询之前就扼杀掉。SET LOCKTIME设置锁的时间。
12、使用select top或set rowcount来限制操作的行。
13、如果使用了in或or等时发现查询没有走索引,使用显式申明指定索引: SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN ('男','女')。
14、如果要插入大的二进制值到Image列,使用存储过程,千万不要用内嵌insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍),服务器受到字符后又将他转换成二进制值。存储过程就没有这些动作: 方法:Create procedure p_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数,这样处理速度明显改善。
15、参数传入时传入准确的数据类型,不要等运行时让DBMS进行转化