深入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
/*
由于非聚集索引包含绝大多数的检索数据，则只需要读取很少的数据页
这种情况下非聚集索引要比聚集索引好，如果

补充：综合编程 , 安全编程 ,