建立高效的数据库应用程序
关键词：Sql Server, ASP, NT
首先，本文是《全面优化ADO》一文的补充和修正。其次，本文是针对ADO2.1和Microsoft SQL Server 7.0而写的，但其中大部分内容对其它数据库访问组件（控件）和其它数据库也是有效的。 表candidates
Agenda
Design a database
首先好构建一个好的数据库结构和索引结构，才谈得上高效的访问数据库。
Cursors
访问数据库无可避免的要经常使用游标，到底该使用哪一种呢？
ASP Codes
一点细枝末节的问题，代码上的小小优化。
Design a database
关系型数据库设计的规范化范式
表结构中不能含有任何重复的数据字段（俗称“横着的表”8-）。
如一个表想记录每一个求职者的姓名和若干 电话号码
固定个数的重复字段还是可以接受的。比如用一行描述一个星期，每天用一列来表示，很自然的事。真正成问题的是，个数不定的重复字段。
Can_id
Can_name
Can_telno1
Can_telno2
Can_telno3
...
1144
Dell’Abate
916-555-2933
714-555-0900
714-555-0901
....
1145
Zanevesky
408-555-9550
602-555-8310
408-555-9553
...
若干电话号码就是重复的字段。初看顺理成章，然而等到要操作他们的时候，就会有这样那样的问题出现了。比如，我要排序列出某人的所有电话号码，这是最基本的操作了，可就连如此简单的要求，都不容易实现。可以说，这是开发效率，运行效率“双差”的设计，是应该避免的。
正规的设计方案如下
表candidates
Can_id
Can_name
1144
Dell’Abate
1145
Zanevesky
于是乎，简单问题就能简单搞定了。select Phonecode from candidates_phone where Can_id= ? order by candidates_phone 再结合下述的索引，“双高”的设计是当之无愧了。:-)
表candidates_phone
Can_id
Phone_sequence
Phonecode
1144
1
916-555-2933
1144
2
714-555-0900
1145
1
408-555-9550
1145
2
602-555-8310
每一列均函数性地依赖于主关键字，
主关键字是Lot_id 和Procedure_id 组合，但其中Address是依赖于Lot_id的，Description是依赖于Procedure_id的，很明显地产生了冗余， 所以应把这张表拆开。
表service_record
Lot_id
Procedure_id
Address
Description
Date_of_work
1767
103
2454 Legion Street
Tree trimming
Nov 15 1997
1767
11
2454 Legion Street
Winterize
Dec 01 1997
955
7
707 Condador Rd
Furnace repair
Dec 03 1997
.
.
.
.
.
表中记录符合第二范式且不存在传递依赖
什么叫传递依赖呢？简单的说，某个字段的值可以由另一个非主关键字的的字段决定的话，就产生了传递依赖。比如在表中存储平均值、总和，或者是这样的情况：
ID
BoardID
UserID
Username
Topic
Content
1
1
1
Master
测试
Null
这张表中存放了UserID和UserName，而Username是可以由UserID决定的，也存在冗余，所以这也是一个传递依赖。
计算值
一个设计正统的关系数据库不存储任何计算数据如总和、平均值、最大值和最小值。这些值通常在查询的时候利用基本数据来计算。
但是，在一些场合，采用存储计算值的方法可能更简单、更快。
例如：在一个表特别大的时候（几万行以上的），而且经常需要得到其中一些数据的总和，每一次查询都要花费几秒的时间，这将降低数据库使用的效率。我们可以采用这样的方法来解决这个问题。
将总和的信息记录在另外一张表中，每天晚上运行一个程序（定时触发）来求和放到表中。如果需要更快的得到准确的总和，也可以采用Insert、Delete和Update触发器来做，不过这样比较影响系统效率，如果表更新频繁的话。
历史信息
如果一个表记录了所有的历史信息，而用户往往只想知道近几个月来的信息。这样可以把表分成几个，每张表记录一个月的信息。不过这样很麻烦的，但是可以用分区这一更好的办法来解决这个问题。（Access不可以的）分区是和复制紧密相关的，简单的说，就是定义一个复制事务，就可以按时把多余的数据（比如说上个月的呀）复制到别的地方去放着，平时常用的表就只放最近的记录。
其它
一般的说，表的列数不应该太多，以不要超过10个字段为宜。
特殊数据类型
变长数据类型
Varchar 、nvarchar和 Varbinary
原则上，在数据长度比较固定的情况下要尽量使用定长数据类型。如邮政编码，应该用char[6]而不是varchar[6]。另外在冗余量不大的情况下，还是推荐使用定长数据类型。原因很简单，存取不定常型数据的开销更大。
文本和图形
Text、nText和Image
这两种数据类型不能用索引、局部变量、内建函数、数学运算符、Order By、 Group By
在以前版本的SQL Server里面，这样的数据类型如果放很少的内容，将会造成很大的空间的浪费，但是现在SQL 7已经较好的解决了这个问题。
Uniqueidentifier
这是一个SQL 7新增的字段类型，可能大家不是太熟悉，所以也介绍一下：
它记录的是一个全球唯一标志(GUID)，就象我们常见到的CLSID那样
00020803-0000-0000-C000-000000000046
D54EEE56-AAAB-11D0-9E1D-00A0C922E6EC
有一个函数可以产生新的GUID： NewID()
TimeStamp
在《数据库探讨》里面经常发现有人把这个字段当做放时间的了，所以还是说明一下。
它是一般用于显示SQL Server更改记录的顺序，是有一个渐增的8 byte的binary数据表示，当一条记录被插入或者被修改的时候，数据库引擎就会把从@@DBTS函数中得到的值来更新timestamp字段。这种字段类型有一个常见的用途就是防止一条记录被两个人修改。
Example:
一般在我们更新某条记录的时候，我们会使用一个ID之类的主关键字作为更新条件。
editAnnounce.asp部分代码[版主编辑帖子]
Response.Write "<input type='hidden' name='announceID' value='" & CStr(announceID) & "'>" & vbCrLf
其中使用announceID作为参数传递给下一个页面，而在下一个页面中执行
objConn.Execute("Update Announce Set Topic = '" & strTopic ... & " Where announceID = " & announceID)
这样的话就存在一个问题，假设moslem在改动某个帖子，而morning在moslem还没保存改动的时候也在改这个帖子，那么先改动的人做的改动就会被覆盖掉，为了避免这种情况，我们可以采用这样的方法。
Response.Write "<input type='hidden' name='announceID' value='" & CStr(announceID) & "'>" & vbCrLf & "<input type='hidden' name='timestamp' value='" & rs("ts") & "'>" 'ts是一个timestamp类型的字段
objConn.Execute "Update Announce Set Topic = '" & strTopic ... & " Where announceID = " & announceID & " and ts = " & timestamp, i
If i = 0 Then Response.Write "帖子已被人改动，请刷新上一个页面"
timestamp的值实际上跟时间并没有什么关系，要想自动记录时间，还是用一个datetime 字段，缺省值赋为getdate()的好，如果想记录每次修改的时间，可以在触发器里面用getdate()取得时间。
主关键字和索引
很复杂，却又很关键的问题。真的要研讨如何建立最优的索引的话，足可以写很厚的一本书，而且我不一定写得了
8-)。然而不要害怕，这不等于我们没法偷懒，(sql 7.0的场合下)简单而又凑合的策略还是有的：
不要瞎建索引。喂，那谁，先别笑！的确有很多人建索引用“飞机播种”法，曰”反正总有用得着的“。这是首先要避免的。因为，第一”用得着“的东西不见得是”最有用“的，第二“用不着”的索引会消耗大量空间并严重降低插，删，（相当的）改操作的性能。
不要随便用聚合索引，比如总是把聚合索引缺省地用在主key上。一个表只能有一个聚合索引，要把这好钢用在刀刃上。详见后述。
UNIQUE是非常重要的信息，如果能加一定要加上。千万不要以为identity之类的列上建索引,sql server会自动地认为是UNIQUE。软件这种东西，宁信其愚不信其智， 但是，相信我，没错的8-)。
写sql时多用wisql，少用各种画板wizard之类的。先不要“咦？” ，我没有跑题，相信我，没错的(我这人是不是有点烦？8-)。wisql 现在自称是“查询分析者(Query Analyzer)”，总得有点道道8。那就是show plan和索引建议（sql 7.0新功能）。show plan告诉你该次查询的执行方案和开销，索引建议则是由sql server来分析你的查询，并提供建索引建议。所以，所以，尽量在wisql里编写调试sql，对于那些频繁执行的sql，有时没事地show plan一下，隔三差五地看一把索引建议，你会找到适合你需要的索引。但是：
索引建议总是建议造非聚合索引，什么时候用聚合索引这得由你自己来决定。如果符合下列条件之一，你应该考虑用聚合索引。一：结果集很大。二：检索条件中有“范围”条件，即<,>,between之类的。三：需要结果集group by 和/或order by。另一方面，如果某个列的变动很频繁，则不宜在该列上建聚合索引。到底怎么定，还是要让事实说话，用show plan比较一下各种方案的差异总是有必要的。
对于like '%xxx%'的查询，普通的索引起不了作用，可以使用FullText Search
Return to Top
Cursors
首先，我们要尽量避免在服务器上使用大的游标，所以在执行一些没有返回结果的语句时，不要返回结果集。
Example:
objConn.Execute "update table set field = field + 1", iRecordsAffected, adCmdText + adExecuteNoRecords
但是由于要得到结果集、记录总数、更新数据等的时候，考虑避免复杂的编程（比如使用Recordset.RecordCount和Recordset.Update），要使用不同的游标，否则将无法得到正确的执行结果，下面我们来看一下不同游标之间的区别。
Server Side Cursor
Client Side Cursor
使用方法object.
CursorLocation = adUseServer
adUseClient
实现方式
游标元数据由SQL Server提供
游标元数据由OLEDB provider游标库提供
系统开销
较高的服务器端开销，较低的客户端开销
较低的服务器端开销，较高的客户端开销
网络往返
较少的网络往返
较多的网络往返
数据库限制
与SQL Server紧密集成
可以和任何数据库一起工作
其它
是SQL Server应用程序的通常选择
比较灵活和不受限制，但效率较低
ASP Codes
ForwardOnly
Static
Keyset
Dynamic
使用方法Recordset.
CursorType =adOpenForwardOnly
adOpenStatic
adOpenKeyset
adOpenDynamic
实现方式
直接在基表上操作
在Tempdb中为游标记录建立一个快照
在Tempdb中为游标记录建立一个对应的Keyset
直接在基表上操作
建立开销
最低
最高（考虑异步入口）
高（当结果集很大时考虑异步入口）
低（多表连接会影响性能）
访问开销
最低
低
最高
高
可否更新
否
否
是
是
滚动方式
向前或到起始位置
允许任意滚动方式
允许任意滚动方式
允许任意滚动方式（只向前滚动可以提高性能）
是否可反映数据的改变
是
否
可以反映数据的更改，但不能反映数据的增加
是
AbsolutePage
无效
读/写
读/写
无效
AbsolutePosition
无效
读/写
读/写
无效
Bookmark
无效
读/写
读/写
无效
PageCount
无效
只读
只读
无效
RecordCount
无效
只读
只读
无效
Clone
无效
有效
有效
无效
Resync
无效
有效
有效
无效
首先再提一下Pooling Pool的问题，我记得以前说过是应该用释放但不关闭Connection对象的方式来使用缓冲池，后来我发现这种做法是错误的，ASP是自动使用缓冲池的，照常关闭和释放Connection对象就可以了，在此鞠躬致歉！
访问结果集的时候，使用Index(objRS(0))比使用Name(objRS("ID"))要快一丁点儿，但是会降低代码的可读性，虽然也可以把Name定义成一个数字的常量，不过也怪麻烦的。
尽量少使用几个对象，ADO和DAO等一个显著的区别就是可以独立的创建对象，而不需要逐级建立对象，你可以只建立你需要的对象，这样可以减轻ADO的负担和代码的数量（这意味着程序的可靠性增加了），比如你不再需要建立Command Object，而可以直接从Connection Object的 Execute Method得到一个Recordset Object，或者使用Recordset 的 Open Method。
另外说明一点，ADO中使用的常量在ASP中是没有定义的（在VB中可以直接使用），需要引用adovbs.inc，而且一些ADO 2.1中的常量在里面没有，需要自行添加，新的adocbs.inc可以在这里下载http://www.chinaasp.com/user/lucy/htdocs/adovbs.inc（我自己加了一部分），当然，你也可以直接使用数字，不过这样影响代码的可读性。
在连接到数据库的时候，最好不好使用DSN，更不要使用文件DSN，在指定数据库服务器路径的时候，最好使用命名管道而不是TCP/IP，还有，以前也提到过最好根据不同的数据库使用相应的Provider。
不仅是ADO的对象，建立的每一个对象都应该释放掉(Set Object = Nothing)，否则多用一段时间IIS就该出错了。
ADO 2.1有一个新方法，类似于RDO的GetClipString，可以用于显示记录集的全部或部分列并加上分隔符。
Recordset.GetString(adClipString, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)
Example:
Response.Write objRs.GetString(adClipString, -1, "</TD><TD>", "</TR><TR><TD>", " ")
适当提高Recordset.CacheSize的数值可以加速数据的访问，CacheSize的默认值是1，可以提高到30或者50
建议使用Top语句来控制返回的记录集的行数
使用嵌套查询来减少访问数据库的次数