深入SQl编程

导读：
　　这里所指的SQL编程并不是那些在象ASP，PHP脚本语言里用的某个SQL语句, 如果你是个程序员并在做DB C/S开发，我想你会很清楚的知道SQL编程是很复杂的，先抛开嵌入语句，动态执行，高级函数，表达试等这些不谈 单就解决性能问题就很头疼，下面就性能问题给出一些解决放案。
　　深入SQL编程:
　　作者：病毒
　　这里所指的SQL编程并不是那些在象ASP，PHP脚本语言里用的某个SQL语句，
　　如果你是个程序员并在做DB C/S开发，我想你会很清楚的知道SQL编程是
　　很复杂的，先抛开嵌入语句，动态执行，高级函数，表达试等这些不谈
　　单就解决性能问题就很头疼，下面就性能问题给出一些解决放案
　　（以下程序均在NT+SP6 SQL SERVER 7下调试通过）
　　一，存储过程
　　我的一个朋友用VC/SQL SERVER做C/S项目开发，再开发过程中他的程序
　　虽顺利执行，但遇到了由于需要大批量插入数据而引出的性能问题。他
　　找到了我，虽然我没有用过VC但很明显在他程序中看出是在前台用循环操
　　作象后台插入数据。这种方法再处理大批量数时无疑是不可取的，因编
　　译器并不会处理SQL语句而是通过ODBC传输到后台，再在后台编译执行。
　　由此可见经过以上三步性能问题以大打折扣，后我将他的程序段改为
　　后台SQL编程，用存储过程实现。然后在前台用VC调用，
　　这样一来问题以得到完美的解决。改后程序如下：（遇到此类问题的朋友可参考解决）
　　CREATE PROC usp_insert_temp
　　@iCount VARCHAR(10),
　　@Text VARCHAR(50),
　　@price VARCHAR(15)
　　AS
　　DECLARE @iIndex INT
　　DECLARE @pMoney FLOAT
　　SET @iIndex=CONVERT (INT, @iCount )
　　SET @pMoney=CONVERT (FLOAT, @price )
　　BEGIN TRAN
　　SELECT rygl_id,title,price
　　INTO rygl_temp FROM rqk
　　WHERE EXISTS
　　(SELECT rygl_id
　　FROM rygl
　　WHERE rqk.rygl_id=rygl.rygl_id
　　AND qty<30)
　　ORDER BY title_id
　　IF @@ERROR<>0
　　ROLLBACK TRAN
　　ELSE
　　COMMIT TRAN
　　WHILE @iIndex >0
　　BEGIN
　　BEGIN TRAN
　　SET @pMoney= @pMoney+1.1111
　　INSERT INTO rygl_temp(rygl_id,title,price)
　　VALUES( @iIndex , @Text ,CONVERT(SMALLMONEY, @pMoney ))
　　IF @@ERROR<>0 OR @@ROWCOUNT=0
　　ROLLBACK TRAN
　　ELSE
　　COMMIT TRAN
　　SET @iIndex= @iIndex-1
　　END
　　二，索引测试，比较
　　合理的索引建立，运用可很大幅度提高程序性能，以下是在
　　工作当中得出的经验，与大家共享。
　　1,ORDER BY和GROPU BY
　　如果用户经常选择数据和使用ORDER BY和GROUP BY短语，任何
　　一种索引都有助于SELECT的性能提高。如果用户选择的是顾客并按其姓名
　　分类，两种索引都能快速检索数据。但下面的一些因素会使用户选择使用
　　某一种索引。
　　2,返回范围内的数据
　　列如，如果拥护希望返回在SMITH和TALBERT之间的所有顾客姓名，或者
　　返回在日期“11/1/98”和“11/30/98”之间的订货量，并且用户经常
　　做这类事情，那么最好在该范围所在的指定列使用聚类索引。因聚类
　　索引已包含了经过分类排序的数据，这对于在指定范围内检索数据更为有
　　效。聚类索引只需找到要检索的所有数据中的开头和结尾数据即可；而不
　　象非聚类索，必须在数据层专查找来字叶层的每一个数据项。
　　3,列中有一个或极少的不同值
　　在用户表中的某些列中喊有极少不同值，列如状态列中只包含INACVTIVE，
　　ACVIVE或者TERMINATED。在这种情况下，在该列上使用任何类型索引都是
　　不明智的，原因很简单：如果用户表包含了1500行大概有三分之一的行即
　　500行在状态列中含有ACTIVE。扫描整个表，如果不是更高效，至少也是
　　同先在索引页面中查找每个数据项而后寻找到包含ACTIVE状态的行所在的数
　　据页面也相同的效率。下面这个列子创建了一个表，它在有很很多重复值的
　　列上进行索引，而该列具有很少的不同值。运行该脚本可能要花几分钟。
　　*/
　　DROP TABLE IndexTestTable
　　CREATE TABLE IndexTestTable
　　(
　　Tid INT IDENTITY(1,1) NOT NULL,
　　Status CHAR(10) NULL
　　)
　　GO
　　SET IDENTITY_INSERT IndexTestTable ON
　　DECLARE @intCount INT
　　BEGIN TRAN
　　SET @intCount=1
　　WHILE @intCount <=1500
　　BEGIN
　　INSERT IndexTestTable(Tid,Status) VALUES( @intCount ,'Active')
　　SET @intCount= @intCount+3
　　END
　　SET @intCount=2
　　WHILE @intCount <=1500
　　BEGIN
　　INSERT IndexTestTable(Tid,Status) VALUES( @intCount ,'inactive')
　　SET @intCount= @intCount+3
　　END
　　SET @intCount=3
　　WHILE @intCount <=1500
　　BEGIN
　　INSERT IndexTestTable(Tid,Status) VALUES( @intCount ,'Terminated')
　　SET @intCount= @intCount+3
　　END
　　COMMIT TRAN
　　SET IDENTITY_INSERT IndexTestTable OFF
　　GO
　　DUMP TRANSACTION pubs WITH NO_LOG
　　GO
　　CREATE INDEX inTableUniquesStatus
　　ON IndexTestTable(Status)
　　GO
　　--不用索引查询
　　SELECT *
　　FROM IndexTestTable WITH(index(0))
　　WHERE Status='inactive'
　　--用索引查询
　　SELECT *
　　FROM IndexTestTable WITH(index(inTableUniquesStatus))
　　WHERE Status='inactive'
　　/*
　　选中SHOW STATS I/O查看运行结果会另人吃惊。第一个SELECT语句引起全表
　　扫描几乎不需要内存操作(因为只是进行插入，所有所有数据都在内存中，并不
　　需要进行磁盘或物理读操作)。第二个SELECT语句则需要执行500个读操作,这
　　就证实了我们所说的在这种情况下,使用任何类型索引都是不明智的。
　　4，
　　以上举列说明了在何种情况下不应使用索引，现在咱们再反过来看看当索引列中
　　不同数目值增加时即有较少不同值时会怎样？见如下代码*/
　　DROP TABLE IndexTestTable
　　GO
　　CREATE TABLE IndexTestTable
　　(
　　Tid INT IDENTITY(1,1) NOT NULL,
　　Status CHAR(10) NULL,
　　Co3 CHAR(20) NOT NULL,
　　Co4 CHAR(50) NOT NULL
　　)
　　GO
　　DECLARE @intNum INT
　　SET @intNum=0
　　BEGIN TRAN
　　WHILE @intNum <=1300
　　BEGIN
　　INSERT indexTestTable
　　VALUES(CHAR( @intNum %26 +65),'test3','test4')
　　SET @intNum= @intNum+1
　　END
　　COMMIT TRAN
　　GO
　　--不用索引查询
　　SELECT * FROM IndexTestTable WHIT(INDEX(0))
　　WHERE Status='B'
　　--创建聚集索引
　　CREATE CLUSTERED INDEX icIndexTestTable
　　ON IndexTestTable(Status)
　　GO
　　--使用索引查询
　　SELECT * FROM IndexTestTable WITH(INDEX(icIndexTestTable))
　　WHERE Status='B'
　　/*
　　5，
　　用户很明显地能看出，随着表中行的数目和列中不同值的增长。使用索引可以
　　较大幅度提高效率，由此又引出另一个问题，在何种情况下用何种索引更有
　　效？上面列子已经介绍了聚类索引，大家都能看出在对于有较少不同植时
　　使用聚类索引是有很大帮助的，但当不同值的数木增加并达到表中行的树木时
　　则应该选非聚类索引。此时使用非聚类索在读操作上和聚类似索引并无
　　二异，但在对表进行写操作上的性能却提高不少，如果用户经常从表中
　　的一个或少是数几个字段中检索数据，当非聚集索引包含要检索的所有字段
　　时就会减少所需的读操作，如果不是那么正如上面第二条所说使用非聚集索
　　引通常是钟很差的选择，下面这个列子说明了在何时应该使用聚集索引
　　*/
　　DROP TABLE IndexTestTable
　　GO
　　CREATE TABLE IndexTestTable
　　(
　　Tid INT IDENTITY(1,1)NOT NULL,
　　Status CHAR(20) NOT NULL
　　)
　　GO
　　DECLARE @intCount INT
　　SET @intCount=0
　　BEGIN TRAN
　　WHILE @intCount <=500
　　BEGIN
　　INSERT INTO IndexTestTable(Status) VALUES('test'+CONVERT(CHAR(6), @intCount ))
　　SET @intCount= @intCount+1
　　END
　　COMMIT TRAN
　　GO
　　--创建聚集索引
　　CREATE CLUSTERED INDEX icIndexTestTable
　　ON IndexTestTable(Tid)
　　GO
　　--创建非聚集索引
　　CREATE INDEX inIndexTestTable
　　ON IndexTestTable(Tid)
　　GO
　　--使用非聚集索引查询
　　SELECT Tid FROM IndexTestTable WITH(INDEX(inIndexTestTable))
　　WHERE Tid BETWEEN 100 AND 500
　　--使用聚集索引查询
　　SELECT Tid FROM IndexTestTable WITH(INDEX(icIndexTestTable))
　　WHERE Tid BETWEEN 100 AND 500
　　/*
　　由于非聚集索引包含绝大多数的检索数据，则只需要读取很少的数据页
　　这种情况下非聚集索引要比聚集索引好，如果表的数据行很庞大效果会更加明显。
　　*/
　　/*
　　6，
　　有一点需要说明的是，索引虽有助于提高性能但不是索引越多越好，恰好相反
　　过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做
　　相应的更新工作。

本文转自
http://www.20cn.net/ns/wz/comp/data/20010129040152.htm