SQL Server查询速度慢原因及优化方法(2)
作者:voboy 发布时间:2008-12-03 15:19:00
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 提示将使指定了该提示的表在游标内是只读的。
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), 在前台调用这个存储过程传入二进制参数,这样处理速度明显改善。
猜你喜欢
- 1、在Asp页面首部加入Response.Buffer = True Response.ExpiresAbso
- 今天有人问起,晚上试着写出来,供参考; 以下代码兼容主流浏览器IE6、IE7、Firefox、Opera。从最简单的开始…………一
- 最简单的方法:取整后判断是否和原值相等!javascript的取整函数是:parseIntif(parseInt(value)==value
- 经常会遇到这样一个情况:浏览器弹出对话框,提示脚本运行时间过长,询问“停止”还是“继续”。那究竟各个浏览器是如何判断在什么时候才弹出此对话框
- 用采集程序的优点有:无须维护网站,因为采集程序中的数据来自其他网站,它将随着该网站的更新而更新;可以节省服务器资源,一般采集程序就几个文件,
- Windows中升级MySQL应采取的步骤:1. 进行升级前你应先备份当前的MySQL安装。2. 下载最新Windows版MySQL。3.
- 您可以使用 ObjectContext 对象提交或放弃一项由 Microsoft Transaction Server (MTS) 管理的事
- 很实用的过滤重复数据的asp代码,函数如下:<%'**************************************
- 有时候我们可能不知道一个用户的密码,但是又需要以这个用户做一些操作,又不能去修改掉这个用户的密码,这个时候,就可以利用一些小窍门,来完成操作
- ASP * 页制作中连接Oracle数据库的例子,下面这段代码能够显示,当前用户所能够看到的所有的用户和表,有兴趣的, 可以把每个表的内容上
- 在某些情况下,比如自动补全(auto complete)的输入框中,需要使用keyup事件来监听键盘的输入以迅速作出回应。关键在于keyup
- JS获取网页中HTML元素的几种方法分析:getElementById getElementsByName getElementsByTag
- sql2000安全很重要将有安全问题的SQL过程删除.比较全面.一切为了安全!删除了调用shell,注册表,COM组件的破坏权限use&nb
- 目录:分析和设计组件编码实现和算法用 Ant 构建组件测试 JavaScript 组件我们走到哪儿了?前两期思考了太多东西,你是否已有倦意?
- 准备软件:1. J2SDK(1.5.0): jdk-1_5_0-linux-i586-rpm.bin2. Apache(2.0.53): h
- 生成Fiboncci Fn数有Θ(1),Θ(n)甚至指数级的算法,不过有Θ(log n)的吗?告诉你,有。首先,关于Fibonacci数,有
- 迭代器模式迭代器模式(Iterator Pattern)是一种常用的设计模式,用于遍历集合中的元素,不暴露集合的内部结构。迭代器模式将集合和
- 人一旦习惯了某些东西就很难去改,以及各种各样的原因,新的浏览器越来越多,而老的总淘汰不了。增长总是快于消亡导致了浏览器兼容是成了谈不完的话题
- 比如要访问b站在a站设置一个cookies,则可以这样做: 1.在b.com下建立一个文件cookies.htm 内容为: 代码如下:内容摘
- 本文介绍了保护MySQL数据库中重要数据受外部攻击的六个注意事项,以减少面临的风险。与自动的数据库备份不同,对系统管理员来说,保护数据免受未