VFP和SQL SERVER搭配做C/S系统
这个话题比较大，我现在上班闲着没事，把自己能想到的整理一下。我做VFP和SQL SERVER的C/S系统有3年了，现在刚得到DBA的职位，以后可能管理数据 库的时候多些了。
1.CLIENT/SERVER 到底是什么?
C/S属于2-TIER系统，适合于中小型应用系统。大系统一般都用3-TIER了。
打个比方单机数据库系统,相当于前店后库.店里需要什么东西,得自己去库房找,库房管理也是由你自己进行. 而C/S系统下,店和库是相对独立的,有一个专门的库房管理(数据SERVER),店里需要什么,按照手续把单子给
库房管理人员,由他们去操作.
因此可以看出C/S的优点: 支持多用户; 更有效的数据管理,数据安全和可靠得多;远程使用数据.
如果你是单机使用当然没有太大必要使用C/S了.
如果用VFP本身做C/S系统,效果不是很明显,因为VFP的数据管理功能不是很强,比如加锁解锁都需要程序来操作. 我主要说的是VFP/SQL SERVER系统.
2. SQL SERVER
SQL SERVER是微软发布的RDBMS(关系数据库管理系统), ORACLE, INFOMIX,POWERBUILD,也都是类似的系统. 他们就相当于仓库的管理系统,但功能不仅仅是数据管理. 微软的数据库产品从功能和规模由小到大排列依次是: ACCESS, VFP, SQL SERVER.
稍微大一点的系统,SQL SERVER是需要专人管理的,这就是DBA (DATABASE ADMINISTRATOR)的位置.现在北美人才市场上, DBA的工作比程序员的工作好找些. 工资比普通程序员略高, 工作稳定性也强一些.
SQL SERVER是一套大的软件系统,可以安装在专门的NT数据SERVER上,也有个人版可以安装在WIN95/98上, 主要是为了咱们这些程序员方便测试. 它的功能主要有这么几块: 数据库的管理和维护,用户/安全管理, 数据的发布/转换.
3. 用VFP/SQL SERVER做C/S系统
VFP 和SQL SERVER的搭配应该是比较完美的, 但因为VB才是微软的主流产品(VB一直是微软的,FOXPRO是后来才买的,后娘生的), 所以微软从来没有大力推荐或者宣传过VFP, 而是把VFP独有的数据库技术融合到自己的其它产品里了: SQL SERVER, ODBC, OLE DB/ADO. 最近甚至把VFP从VISUAL STUDIO里独立出来了.
VFP和SQL SERVER的交流,可以通过3种方式进行:
A. 远程视图 B. ADO控件 C.SPT (SQL PASS THROUGH)
3种方式各有优缺点, 而我最偏好第3种. 远程视图和ADO都在VFP和SQL SERVER中间加了一个层次, 而这层次就相当于一个黑箱,你不清楚它们到底怎么操作数据的,只需按照它们的规则进行设置,使用
相应的命令就可以. 反正我作为程序员,喜欢清楚地知道我的程序每一步都在干什么,所以我喜欢用SPT, 也从不用向导来建立表单报表一类的.
SPT技术是通过VFP的函数SQLCONNECT()来和SQL SERVER建立连接, 然后用SQLEXEC()函数把要执行的SQL命令 送到SQL SERVER上去执行,
所有对数据的操作都是通过这些SQL命令来进行的. 每一个细节都由自己的代码来控制. 因为对数据的操作命令都是送到SQL SERVER上去运行的,所以叫PASS THROUGH.
4. VFP数据的升迁
VFP的数据库,可以直接用VFP自带的升迁向导转到SQL SERVER上,但必须把所有的表都放到数据库里,自由表不能直接升迁, 而实际上升迁上去的又只是表,数据库本身不能转到SQL SERVER里. 感觉怪怪的. 也许是为了升迁索引或者关系吧.
要把VFP数据升迁到SQL SERVER上, 必须先在SQL SERVER里建立相应的数据库. SQL SERVER的管理是通过ENTERPRISE MANAGER (EM)来进行的. 通过它建立数据库, 每个数据库里可以包含数据表,用户/用户群,视图,存储过程等等.
1.CLIENT/SERVER 到底是什么?
C/S属于2-TIER系统，适合于中小型应用系统。大系统一般都用3-TIER了。
打个比方单机数据库系统,相当于前店后库.店里需要什么东西,得自己去库房找,库房管理也是由你自己进行. 而C/S系统下,店和库是相对独立的,有一个专门的库房管理(数据SERVER),店里需要什么,按照手续把单子给
库房管理人员,由他们去操作.
因此可以看出C/S的优点: 支持多用户; 更有效的数据管理,数据安全和可靠得多;远程使用数据.
如果你是单机使用当然没有太大必要使用C/S了.
如果用VFP本身做C/S系统,效果不是很明显,因为VFP的数据管理功能不是很强,比如加锁解锁都需要程序来操作. 我主要说的是VFP/SQL SERVER系统.
2. SQL SERVER
SQL SERVER是微软发布的RDBMS(关系数据库管理系统), ORACLE, INFOMIX,POWERBUILD,也都是类似的系统. 他们就相当于仓库的管理系统,但功能不仅仅是数据管理. 微软的数据库产品从功能和规模由小到大排列依次是: ACCESS, VFP, SQL SERVER.
稍微大一点的系统,SQL SERVER是需要专人管理的,这就是DBA (DATABASE ADMINISTRATOR)的位置.现在北美人才市场上, DBA的工作比程序员的工作好找些. 工资比普通程序员略高, 工作稳定性也强一些.
SQL SERVER是一套大的软件系统,可以安装在专门的NT数据SERVER上,也有个人版可以安装在WIN95/98上, 主要是为了咱们这些程序员方便测试. 它的功能主要有这么几块: 数据库的管理和维护,用户/安全管理, 数据的发布/转换.
3. 用VFP/SQL SERVER做C/S系统
VFP 和SQL SERVER的搭配应该是比较完美的, 但因为VB才是微软的主流产品(VB一直是微软的,FOXPRO是后来才买的,后娘生的), 所以微软从来没有大力推荐或者宣传过VFP, 而是把VFP独有的数据库技术融合到自己的其它产品里了: SQL SERVER, ODBC, OLE DB/ADO. 最近甚至把VFP从VISUAL STUDIO里独立出来了.
VFP和SQL SERVER的交流,可以通过3种方式进行:
A. 远程视图 B. ADO控件 C.SPT (SQL PASS THROUGH)
3种方式各有优缺点, 而我最偏好第3种. 远程视图和ADO都在VFP和SQL SERVER中间加了一个层次, 而这层次就相当于一个黑箱,你不清楚它们到底怎么操作数据的,只需按照它们的规则进行设置,使用
相应的命令就可以. 反正我作为程序员,喜欢清楚地知道我的程序每一步都在干什么,所以我喜欢用SPT, 也从不用向导来建立表单报表一类的.
SPT技术是通过VFP的函数SQLCONNECT()来和SQL SERVER建立连接, 然后用SQLEXEC()函数把要执行的SQL命令 送到SQL SERVER上去执行,
所有对数据的操作都是通过这些SQL命令来进行的. 每一个细节都由自己的代码来控制. 因为对数据的操作命令都是送到SQL SERVER上去运行的,所以叫PASS THROUGH.
4. VFP数据的升迁
VFP的数据库,可以直接用VFP自带的升迁向导转到SQL SERVER上,但必须把所有的表都放到数据库里,自由表不能直接升迁, 而实际上升迁上去的又只是表,数据库本身不能转到SQL SERVER里. 感觉怪怪的. 也许是为了升迁索引或者关系吧.
要把VFP数据升迁到SQL SERVER上, 必须先在SQL SERVER里建立相应的数据库. SQL SERVER的管理是通过ENTERPRISE MANAGER (EM)来进行的. 通过它建立数据库, 每个数据库里可以包含数据表,用户/用户群,视图,存储过程等等.
改用SQL SERVER后,原来的VFP程序是肯定需要修改的, 而且是比较大的改动.
用SQL SERVER做数据库，用户权限有两个层次，
一是用户必须有NT登录权或者SQL SERVER的直接登录权限，这样才能连接到相应的SQL
SERVER。
二是用户必须是相应数据库的用户(在数据库的用户列表里)。而用户在数据库的的权限是设置在表层次的。 对不同的表，可以分别给用户设置Select, Update, Insert, Delete权限。也可以设置ROLE(相当于用户组)，对每个ROLE设置权限，然后把用户加到不同的ROLE里。
在VFP里如何得到用户权限？？
如果是用视图或者ADO，很难得到用户的详细情况，比如你不知道该用户是否有修改权，如果发送更新命令到SERVER而用户没有相应权限，则命令会失败，但你不知道是什么原因。在程序里没法进行出错处理。
另外，我们希望程序运行时就知道用户的权限，这样可以根据权限ENABLE或者DISABLE相应的菜单项或者工具栏的按钮。例，用户没有删除权，程序一打开，就可以把删除键设为DISABLED。
用SPT技术就可以得到用户的权限信息。但必须对SQL SERVER的系统数据库有一定了解。比较常用的系统表有：
1.Sysusers: 存放SQL Roles和用户资料
比如下面的命令可以列出当前库里的ROLES
Select GID,NAME from Sysusers where IsSQLRole=1 AND gid<>0
2.Sysprotects:存放OBJECT(比如表,视图,存储过程等)层次的用户权限
ID: Object ID UID:用户 ID Action: 用户可以进行的操作(数字表示)
**单看这个表是看不出什么的,因为全是代号和数字
Select ID, UID, Action from SysProtects
3. Sysobjects:存放当前库里的所以OBJECT信息:ID, Name, Type....
Select Name, ID, Type from Sysobjects
4.Master.Dbo.Spt_Values: 这个表在系统数据库MASTER里,存放对操作的定义,比如195 是Insert
Select Number, Name From Master.Dbo.Spt_Values Where Type='T'
把上述4个表连接起来,就可以得到用户在某个表上的权限了. 这个连接有点复杂. 但只要明白一次就行了,可以把它做成一个方法.
下面是我的例子,该代码返回对表OLINES和PROJECTS有INSERT权限的所有用户:
只要修改下面的2个参数就可以, 后面的代码是通用的
lcUser="buffer" &&用户名
lcTables="('Olines','Projects')" &&表名
lcSel="SELECT d.Name As TableName, c.Name As UserName,c.UID,c.gid , b.Name As UserRight ,e.Name As Action ,a.columns As ColumnMask, a.id"
lcFrom="From SysProtects a, (Select Number, Name From Master.Dbo.Spt_Values Where Type='T') As b, SysUsers c, SysObjects d, (Select Number, Name From Master.Dbo.Spt_Values Where Type='T') As e "
lcWhere="Where a.Action=b.Number And (a.Uid=c.Uid OR a.Uid=c.Gid) And a.Id=d.Id And a.ProtectType=e.Number And c.Name='"+lcUser+"'d.Name In"+lcTables+" AND B.Name='INSERT'"
lnSQLHandle=SQLCONNECT("SHARED","用户","密码")
lnReturn=SQLExec(lnHandle,lcSel+lcFrom+lcWhere,"UserRights")
If lnReturn>0
***UserRights表里存放了该用户对OLINES和PROJECTS表的权限
EndIf
EndIf
注: 使用当前库里的表时,直接用表名就可以,如果使用别的数据库里的表,需要在前面加库名和库创建者名字,比如上面的 Master.Dbo.Spt_Values.
二是用户必须是相应数据库的用户(在数据库的用户列表里)。而用户在数据库的的权限是设置在表层次的。 对不同的表，可以分别给用户设置Select, Update, Insert, Delete权限。也可以设置ROLE(相当于用户组)，对每个ROLE设置权限，然后把用户加到不同的ROLE里。
在VFP里如何得到用户权限？？
如果是用视图或者ADO，很难得到用户的详细情况，比如你不知道该用户是否有修改权，如果发送更新命令到SERVER而用户没有相应权限，则命令会失败，但你不知道是什么原因。在程序里没法进行出错处理。
另外，我们希望程序运行时就知道用户的权限，这样可以根据权限ENABLE或者DISABLE相应的菜单项或者工具栏的按钮。例，用户没有删除权，程序一打开，就可以把删除键设为DISABLED。
用SPT技术就可以得到用户的权限信息。但必须对SQL SERVER的系统数据库有一定了解。比较常用的系统表有：
1.Sysusers: 存放SQL Roles和用户资料
比如下面的命令可以列出当前库里的ROLES
Select GID,NAME from Sysusers where IsSQLRole=1 AND gid<>0
2.Sysprotects:存放OBJECT(比如表,视图,存储过程等)层次的用户权限
ID: Object ID UID:用户 ID Action: 用户可以进行的操作(数字表示)
**单看这个表是看不出什么的,因为全是代号和数字
Select ID, UID, Action from SysProtects
3. Sysobjects:存放当前库里的所以OBJECT信息:ID, Name, Type....
Select Name, ID, Type from Sysobjects
4.Master.Dbo.Spt_Values: 这个表在系统数据库MASTER里,存放对操作的定义,比如195 是Insert
Select Number, Name From Master.Dbo.Spt_Values Where Type='T'
把上述4个表连接起来,就可以得到用户在某个表上的权限了. 这个连接有点复杂. 但只要明白一次就行了,可以把它做成一个方法.
下面是我的例子,该代码返回对表OLINES和PROJECTS有INSERT权限的所有用户:
只要修改下面的2个参数就可以, 后面的代码是通用的
lcUser="buffer" &&用户名
lcTables="('Olines','Projects')" &&表名
lcSel="SELECT d.Name As TableName, c.Name As UserName,c.UID,c.gid , b.Name As UserRight ,e.Name As Action ,a.columns As ColumnMask, a.id"
lcFrom="From SysProtects a, (Select Number, Name From Master.Dbo.Spt_Values Where Type='T') As b, SysUsers c, SysObjects d, (Select Number, Name From Master.Dbo.Spt_Values Where Type='T') As e "
lcWhere="Where a.Action=b.Number And (a.Uid=c.Uid OR a.Uid=c.Gid) And a.Id=d.Id And a.ProtectType=e.Number And c.Name='"+lcUser+"'d.Name In"+lcTables+" AND B.Name='INSERT'"
lnSQLHandle=SQLCONNECT("SHARED","用户","密码")
lnReturn=SQLExec(lnHandle,lcSel+lcFrom+lcWhere,"UserRights")
If lnReturn>0
***UserRights表里存放了该用户对OLINES和PROJECTS表的权限
EndIf
EndIf
注: 使用当前库里的表时,直接用表名就可以,如果使用别的数据库里的表,需要在前面加库名和库创建者名字,比如上面的 Master.Dbo.Spt_Values.
只有当以SA身份登录SQL SERVER建立数据库,库的创建者才是DBO.这很容易出错.
用SPT技术更新数据时，必须通过SQL命令进行，SQL命令必须符合ANSI或者T-SQL(微软的TRANSACTION
SQL)，因为命令是通过VFP的SQLEXEC()函数送到SQL SERVER去执行的，语法必须遵守SQL
SERVER的规则，而不是VFP的规则。不熟悉的人，最容易犯的错误就是把VFP的函数传送到SQL
SERVER上执行，结果总出错。两者大部分函数都是不一样的。
比如ALLTRIM()是VFP的， SQL SERVER里是LTRIM()和RTRIM()
VFP日期以{}分界，但SQL SERVER里不认，必须用单引号.
如果SQL命令里用到VFP程序里的变量，变量前必须加问号“？”
比如，更新一个表的字段
lcLName="Zhang"
lcFName="San"
lnReturn=SQLEXEC(连接句柄，“Update Emp Set cLName=?lcName, cFName=?lcFName Where cEmpNo='733000'")
如果返回值lnReturn>0，就更新成功了
插入记录，或者逐条修改记录时，只能每次操作一条记录。
比如要把临时表TmpEmp里的记录加到SQL SERVER的EMP表里，就得用循环
Select TmpEmp
Scan
lnReturn=SQLEXEC(连接句柄，"Insert Into Emp (cEmpNo,cLName,cFName) Values (?TmpEmp.cEmpNo,?cTmpEmp.cLName,?cTmpEmp.cFName)"
EndScan
*** 要得到SQL SERVER上某个表的结构，有两种办法，一是运行SQL SERVER自带的系统存储过程。一是运行一个SELECT命令。
比如要从SQL SERVER得到EMP表的结构
lnReturn=SQLEXEC(连接句柄,"Select Top 0 From Emp","TmpEmp")
返回的结果都是临时表，是只读的，要想变成可读写的，需要做点小变化：
Select 0
Use DBF("TmpEmp") Again Alias Emp
Use In TmpEmp
现在得到的EMP临时表就是可读写的了。
*注 "Select Top 0 From Emp" 命令在VFP里是错误命令，但SQL SERVER里可以执行.
比如ALLTRIM()是VFP的， SQL SERVER里是LTRIM()和RTRIM()
VFP日期以{}分界，但SQL SERVER里不认，必须用单引号.
如果SQL命令里用到VFP程序里的变量，变量前必须加问号“？”
比如，更新一个表的字段
lcLName="Zhang"
lcFName="San"
lnReturn=SQLEXEC(连接句柄，“Update Emp Set cLName=?lcName, cFName=?lcFName Where cEmpNo='733000'")
如果返回值lnReturn>0，就更新成功了
插入记录，或者逐条修改记录时，只能每次操作一条记录。
比如要把临时表TmpEmp里的记录加到SQL SERVER的EMP表里，就得用循环
Select TmpEmp
Scan
lnReturn=SQLEXEC(连接句柄，"Insert Into Emp (cEmpNo,cLName,cFName) Values (?TmpEmp.cEmpNo,?cTmpEmp.cLName,?cTmpEmp.cFName)"
EndScan
*** 要得到SQL SERVER上某个表的结构，有两种办法，一是运行SQL SERVER自带的系统存储过程。一是运行一个SELECT命令。
比如要从SQL SERVER得到EMP表的结构
lnReturn=SQLEXEC(连接句柄,"Select Top 0 From Emp","TmpEmp")
返回的结果都是临时表，是只读的，要想变成可读写的，需要做点小变化：
Select 0
Use DBF("TmpEmp") Again Alias Emp
Use In TmpEmp
现在得到的EMP临时表就是可读写的了。
*注 "Select Top 0 From Emp" 命令在VFP里是错误命令，但SQL SERVER里可以执行.
SPT和使用视图相比，优点是每一步你都清楚自己在做什么，知道为什么命令会出错。缺点是你需要多写命令，多了解SQL SERVER的语法。
**建立与SQL
SERVER的连接
lnHandle=SQLConnect("ODBC数据源","用户名"，"密码")
If lnHandle>0
&& 设置成手工事务处理模式，由代码来控制
= SQLSETPROP(lnSQL_Hand, 'Transactions', 2)
**下面的命令从EMPPAY里选取记录，并给该表加上独占锁(TABLELOCKX)，一直到该事务结束(HOLDLOCK)
ln1=SQLEXEC(lnHandle, "SELECT * FROM Emppay WITH (TABLOCKX, HOLDLOCK)","Emppay")
**执行其它命令，比如更新数据
ln2=SQLEXEC(lnHandle,"Update PayTotal Set ......")
...
...
**如果所有命令都正确执行了，则
=SQLCOMMIT(lnHandle)
***如果要放弃整个事务处理，用
=SQLROLLBACK(lnHandle)
***关闭连接，事务自动结束，锁也解开
=SQLDISCONNECT(lnHandle)
Else
****连接失败
EndIf
*** SQLSETPROP()函数
这个VFP函数是用来设置当前连接的属性的。 比如上边的手工事务处理。
还有一个比较常用的属性是DISPLOGIN,该属性控制是否显示SQL SERVER的登录表单
第3个参数： 1 - 显示登录表单，如果登录信息(用户名，密码)不完全 2 - 总显示登录表单 3 - 不显示登录表单
例如：
=SQLSETPROP(lnHandle, "DISPLOGIN",1)
lnHandle=SQLConnect("ODBC数据源","用户名"，"密码")
If lnHandle>0
&& 设置成手工事务处理模式，由代码来控制
= SQLSETPROP(lnSQL_Hand, 'Transactions', 2)
**下面的命令从EMPPAY里选取记录，并给该表加上独占锁(TABLELOCKX)，一直到该事务结束(HOLDLOCK)
ln1=SQLEXEC(lnHandle, "SELECT * FROM Emppay WITH (TABLOCKX, HOLDLOCK)","Emppay")
**执行其它命令，比如更新数据
ln2=SQLEXEC(lnHandle,"Update PayTotal Set ......")
...
...
**如果所有命令都正确执行了，则
=SQLCOMMIT(lnHandle)
***如果要放弃整个事务处理，用
=SQLROLLBACK(lnHandle)
***关闭连接，事务自动结束，锁也解开
=SQLDISCONNECT(lnHandle)
Else
****连接失败
EndIf
*** SQLSETPROP()函数
这个VFP函数是用来设置当前连接的属性的。 比如上边的手工事务处理。
还有一个比较常用的属性是DISPLOGIN,该属性控制是否显示SQL SERVER的登录表单
第3个参数： 1 - 显示登录表单，如果登录信息(用户名，密码)不完全 2 - 总显示登录表单 3 - 不显示登录表单
例如：
=SQLSETPROP(lnHandle, "DISPLOGIN",1)
当使用SPT技术时，为节约连接数减少服务器负担，需要经常连接和断开SQL SERVER，你并不希望每次连接时都让用户重新登录，当用户首次登录后，可以把名字和密码存起来，以后的连接可以自动登录了。
作为VFP程序员，我们深知索引对表的重要性，好的索引可以大大缩短程序读取数据的时间。
索引对于SQL SERVER数据表来说，同样是很重要的。你可以做个简单的测试。
1. 索引测试
从SQL SERVER程序组里或者ENTERPRISE MANAGER的TOOLS菜单上打开QUERY ANALYZER工具.
连接到你的SERVER后, 选择QUERY菜单上的CURRENT CONNECTION OPTIONS(当前连接选项),
把SHOW STATS TIME 和SHOW STATS I/O两个选项打勾,然后点OK. 这两个选项将会告诉你命令的运行时间和读写次数.
如果你的SQL命令里用到多个表,还可以选上QUERY菜单上的SHOW EXECUTION PLAN, 它会告诉你的命令在各各表上所花费的时间.
选一个没有索引的大小适当的表,然后执行一条SELECT命令,你会看到运行时间.
比如我用了表CHQ,有将近40万条记录, 执行下面的命令,返回5000多个记录
select * from Chq where date >='02/01/2001' and date<='02/28/2001'
显示的时间和读写情况:
Table 'chq'. Scan count 1, logical reads 13145, physical reads 0, read-ahead reads 13152.
SQL Server Execution Times: CPU time = 5781 ms, elapsed time = 23526 ms.
然后我在CHQ表上对DATE字段建立索引,再运行相同的SELECT, 得到如下结果:
Table 'chq'. Scan count 1, logical reads 3965, physical reads 14, read-ahead reads 0.
SQL Server Execution Times: CPU time = 704 ms, elapsed time = 6432 ms.
比较两个结果可以看出, 建立索引后,逻辑读写和预读写次数大大减少, 占用CPU时间从5.781秒减少到了0.704秒, 总运行时间从23.526秒减少到6.432秒
如果运行更复杂的命令,效果会更加明显.
2. 了解SQL SERVER的索引
SQL SERVER的数据文件和索引文件都是以页为单位存放的,每页是8K. 相当于把磁盘划分成8K大小的块,以块为单位存放数据.
了解这一点是非常重要的, 它能帮助你理解下面的内容.
SQL SERVER的索引有2大类,
一类是CLUSTERED(物理索引),每个表只能有一个, 记录在磁盘上存放时完全按照物理索引的顺序.
另一类是NONCLUSTERED(逻辑索引), 记录顺序存放在索引表里.
不管是哪类索引,只要能到达目的,索引表达式(包含的字段)越简单越有效.
什么字段作为物理索引最合适?
有两个原则: 一是在根据范围来选择记录时,哪个字段最有效, 二是在往磁盘上写记录是不会引起热点(HOT SPOT).
热点是指,大量读写发生在磁盘的同一区域,引起I/O瓶颈效应,降低运行速度.
我们用例子来看看这些原则怎么用.
假如有个单据表, 包含这些字段: 单据号,单据日期,金额,销售地点,已经其它数据.
哪个适合作为物理索引呢? 一般的想法是用没有重复值的字段(相当于VFP里的主键).
a. 用单据号, 其实这不是恰当的选择. 因为很少有根据单据的范围来选择记录的, 比如你很少用命令来查询单据号大于或者小于某个值的记录. 而且在输入时,也比较容易产生热点, 因为插入的记录一般都加在表尾,当有大量用户同时输入时,会产生热点. 单据号不符合上面的2条原则.
b. 用单据日期, 似乎是个比较适当的选择. 因为经常需要用日期范围来选择记录, 但同样会因为大量用户同时输入最新单据而在磁盘上引起热点.
c. 用销售地点, 这应该是最恰当的物理索引人选了. 因为我们会经常根据地点来选择记录, 而在同一时刻插入的记录也不太可能来自同一个销售地点,避免了热点.
当然,不同的环境下,你的选择可能不同,比如你没有成千上万的用户,就不太需要考虑热点问题. 但要记住, 有唯一值的字段并不是物理索引的最佳选择.
3. 选择适当的FillFactor
在SQL-CREATE INDEX命令里,有个FILLFACTOR选项, 这个选项对索引的效率是有很大作用的.
FILLFACTOR是指建立索引时, 每一页存放数据的填满程度, 比如100%,表示把索引文件每一页都填满,隐含值是0 (100%).
如果你的表经常需要插入记录, 选择适当的FILLFACTOR就很重要了. 如果总是用100%, 当你插入一个新记录时, 由于每页都是满的, 就需要进行分页操作(把当前页分成2页),频繁的分页操作会占用服务器的资源和时间,也降低索引文件的效率. 而如果你的FILLFACTOR不是 100%,比如设为80%, 在插入新记录时,由于当前页还有空间, 可以直接加入不需要分页. 当然, FILLFACTOR太低也会降低效率,因为那样的话,页数就多了,搜索数据用的时间长,占用空间也多.
如果是只读表,或者插入记录很少的表,用100% FILLFACTOR最好.
对于经常插入新记录的表,需要定期重建索引, 因为索引文件随着页数的增加, 效率会越来越低. 因为索引都是树状结构, 当下面的页加得没有规律时, 连接就会变乱, 重建索引可以重新整理树状结构.
4. 索引分析工具
在QUERY ANALYZER的QUERY菜单里,还有个选项PERFORM INDEX ANALYSIS.
在输入你的命令后,选择这一项, SQL SERVER会对你的命令进行分析,然后告诉你建立什么样的索引对当前命令最有利.
可以把你程序里常用的命令放到这里进行分析,找出最恰当的索引.
索引对于SQL SERVER数据表来说，同样是很重要的。你可以做个简单的测试。
1. 索引测试
从SQL SERVER程序组里或者ENTERPRISE MANAGER的TOOLS菜单上打开QUERY ANALYZER工具.
连接到你的SERVER后, 选择QUERY菜单上的CURRENT CONNECTION OPTIONS(当前连接选项),
把SHOW STATS TIME 和SHOW STATS I/O两个选项打勾,然后点OK. 这两个选项将会告诉你命令的运行时间和读写次数.
如果你的SQL命令里用到多个表,还可以选上QUERY菜单上的SHOW EXECUTION PLAN, 它会告诉你的命令在各各表上所花费的时间.
选一个没有索引的大小适当的表,然后执行一条SELECT命令,你会看到运行时间.
比如我用了表CHQ,有将近40万条记录, 执行下面的命令,返回5000多个记录
select * from Chq where date >='02/01/2001' and date<='02/28/2001'
显示的时间和读写情况:
Table 'chq'. Scan count 1, logical reads 13145, physical reads 0, read-ahead reads 13152.
SQL Server Execution Times: CPU time = 5781 ms, elapsed time = 23526 ms.
然后我在CHQ表上对DATE字段建立索引,再运行相同的SELECT, 得到如下结果:
Table 'chq'. Scan count 1, logical reads 3965, physical reads 14, read-ahead reads 0.
SQL Server Execution Times: CPU time = 704 ms, elapsed time = 6432 ms.
比较两个结果可以看出, 建立索引后,逻辑读写和预读写次数大大减少, 占用CPU时间从5.781秒减少到了0.704秒, 总运行时间从23.526秒减少到6.432秒
如果运行更复杂的命令,效果会更加明显.
2. 了解SQL SERVER的索引
SQL SERVER的数据文件和索引文件都是以页为单位存放的,每页是8K. 相当于把磁盘划分成8K大小的块,以块为单位存放数据.
了解这一点是非常重要的, 它能帮助你理解下面的内容.
SQL SERVER的索引有2大类,
一类是CLUSTERED(物理索引),每个表只能有一个, 记录在磁盘上存放时完全按照物理索引的顺序.
另一类是NONCLUSTERED(逻辑索引), 记录顺序存放在索引表里.
不管是哪类索引,只要能到达目的,索引表达式(包含的字段)越简单越有效.
什么字段作为物理索引最合适?
有两个原则: 一是在根据范围来选择记录时,哪个字段最有效, 二是在往磁盘上写记录是不会引起热点(HOT SPOT).
热点是指,大量读写发生在磁盘的同一区域,引起I/O瓶颈效应,降低运行速度.
我们用例子来看看这些原则怎么用.
假如有个单据表, 包含这些字段: 单据号,单据日期,金额,销售地点,已经其它数据.
哪个适合作为物理索引呢? 一般的想法是用没有重复值的字段(相当于VFP里的主键).
a. 用单据号, 其实这不是恰当的选择. 因为很少有根据单据的范围来选择记录的, 比如你很少用命令来查询单据号大于或者小于某个值的记录. 而且在输入时,也比较容易产生热点, 因为插入的记录一般都加在表尾,当有大量用户同时输入时,会产生热点. 单据号不符合上面的2条原则.
b. 用单据日期, 似乎是个比较适当的选择. 因为经常需要用日期范围来选择记录, 但同样会因为大量用户同时输入最新单据而在磁盘上引起热点.
c. 用销售地点, 这应该是最恰当的物理索引人选了. 因为我们会经常根据地点来选择记录, 而在同一时刻插入的记录也不太可能来自同一个销售地点,避免了热点.
当然,不同的环境下,你的选择可能不同,比如你没有成千上万的用户,就不太需要考虑热点问题. 但要记住, 有唯一值的字段并不是物理索引的最佳选择.
3. 选择适当的FillFactor
在SQL-CREATE INDEX命令里,有个FILLFACTOR选项, 这个选项对索引的效率是有很大作用的.
FILLFACTOR是指建立索引时, 每一页存放数据的填满程度, 比如100%,表示把索引文件每一页都填满,隐含值是0 (100%).
如果你的表经常需要插入记录, 选择适当的FILLFACTOR就很重要了. 如果总是用100%, 当你插入一个新记录时, 由于每页都是满的, 就需要进行分页操作(把当前页分成2页),频繁的分页操作会占用服务器的资源和时间,也降低索引文件的效率. 而如果你的FILLFACTOR不是 100%,比如设为80%, 在插入新记录时,由于当前页还有空间, 可以直接加入不需要分页. 当然, FILLFACTOR太低也会降低效率,因为那样的话,页数就多了,搜索数据用的时间长,占用空间也多.
如果是只读表,或者插入记录很少的表,用100% FILLFACTOR最好.
对于经常插入新记录的表,需要定期重建索引, 因为索引文件随着页数的增加, 效率会越来越低. 因为索引都是树状结构, 当下面的页加得没有规律时, 连接就会变乱, 重建索引可以重新整理树状结构.
4. 索引分析工具
在QUERY ANALYZER的QUERY菜单里,还有个选项PERFORM INDEX ANALYSIS.
在输入你的命令后,选择这一项, SQL SERVER会对你的命令进行分析,然后告诉你建立什么样的索引对当前命令最有利.
可以把你程序里常用的命令放到这里进行分析,找出最恰当的索引.
