深入SQl编程

/ns/wz/comp/data/20010129040152.htm

这里所指的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,

有一点需要说明的是,索引虽有助于提高性能但不是索引越多越好,恰好相反

过多的索引会导致系统低效。用户在表中每加进一个索引,维护索引集合就要做

相应的更新工作。