实用技巧:优化SQL Server数据库查询方法
作者:eNet 来源:eNet 发布时间:2009-02-04 13:46:00
SQL Server数据库查询速度慢的原因有很多,常见的有以下几种:
1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷)
2、I/O吞吐量小,形成了瓶颈效应。
3、没有创建计算列导致查询不优化。
4、内存不足
5、网络速度慢
6、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)
7、锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷)
8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
9、返回了不必要的行和列
10、查询语句不好,没有优化
●可以通过以下方法来优化查询:
1、把数据、日志、索引放到不同的I/O设备上,增加读取速度,以前可以将Tempdb应放在RAID0上,SQL2000不在支持。数据量(尺寸)越大,提高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 站点的处理需要。有关更多信息,参见设计联合数据库服务器。
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:乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形,即在打开游标及更新行的间隔中,只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时,没有锁控制其中的行,这将有助于最大化其处理能力。如果用户试图修改某一行,则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变,则服务器就会知道其他人已更新了此行,并会返回一个错误。如果值是一样的,服务器就执行修改。
猜你喜欢
- 如果我们的web应用有大量的异步请求,而这些异步请求是在web服务器认证的情况下,那当我们请求发生在服务器认证失效下,服务器自动302到登录
- 我看见朋友可以把数据库的记录输出到页面表格上去,觉得很有用。这是怎么做的啊?见下:dbtable.asp<html><he
- phpMyAdmin可以管理整个MySQL服务器(需要超级用户),也可以管理单个数据库。为了实现后一种,你将需要合理设置MySQL用户,他只
- 前两天,编辑建议我去当当和卓越申请个用户,在网站上放上我的书的链接,这样还可以拿到一些反点儿,于是我兴冲冲地跑到几个网站上去看,却只在卓越(
- <html><head><title>不刷新页面查询的方法</title><meta
- 凡搞WEB开发的人都离不开HTTP(超文本传输协议),而要了解HTTP,除了HTML本身以外,还有一部分不可忽视的就是HTTP消息头。做过S
- <HTML> <BODY> <
- 当你要使用data URI scheme的时候,你会发现,虽然他可以使用在绝大多数浏览器上,但无法再IE6和IE7上工作。不过值得庆幸的这一
- 1.11 – 添加缎带修饰网页局部模块中右上角的蓝色缎带修饰是这个网站界面设计中的一个亮点,只要合理的运用CSS、PNG透明图片和绝对定位属
- 由于某些原因需要把函数直接放到 img 标签上的 onload 属性执行,比如:For some reasons we have to ex
- 在程序的开发过程中,处理分页是大家接触比较频繁的事件,因为现在软件基本上都是与数据库进行挂钓的。但效率又是我们所追求的,如果是像原来那样把所
- 一、MySQL修改密码方法总结首先要说明一点的是:一般情况下,修改MySQL密码是需要有mysql里的root权限的,这样一般用户是无法更改
- <div id="d1"></div> <script > fu
- 一般开发,SQL Server的数据库所有者为dbo.但是为了安全,有时候可能把它换成其它的名称,所有者变换不是很方便.这里列出两种供参考
- 为什么能实现在线编辑呢? 首先需要ie 的支持,在 ie 5.5以后就有一个编辑状态,就是利用这个编辑状态,然后用javascript 来控
- 随着网站访问量的加大,每次从数据库读取都是以效率作为代价的,很多用ACCESS作数据库的更会深有体会,静态页加在搜索时,也会被优先考虑。互联
- 首先,我要在这里写上一些很官方的概念,意在说明面向对象是很具体化的,很实体的模式,不能让有些人看见“对象&rdq
- 我们的规范到底做到哪一步算是发挥良好的价值?其实一件事物我们理解错根本目的会导致出大不一样的结果,直接反应在设计师到底要体现什么的价值。想想
- 如何正确理解和使用Command、Connection和 Recordset三个对象?我知道它们都是连接数据库的“好手”,但在编程的具体应用
- 我们都知道ACCESS是ASP的亲密伙伴。因为两种最简单的东西碰在一起总能迸发出火花。然而,当我们过滤不严格的时候经常出现日文字符,这个时候