创新性应用-黄卫

创新性应用：
1、 通过工作实践，建立大型ERP产品软件的数据库优化策略。
根据不同模块特性制定优化目标，如SQL运行时间/数据量、数据库资源占用率等优化指标。在不同数据量和软件模块完成阶段，利用跟踪测试工具分析运行结果。并且不断验证优化效果。将程序（外模式）响应时间和数据库（模式）响应时间进行综合考虑。将数据库表、存储过程优化与程序优化和程序开发方式相结合，从整体上优化软件性能。根据各阶段优化目标，有条不紊的开展数据库优化工作。
2、利用SQL Server 2005分区表特性处理历史数据。
分区表对于Oracle可能不算是新的概念，但是微软在最新推出的SQL Server 2005才开始支持这一功能。而且由于分区表的推出，SQL Server传统的表组织结构也发生了变化：每个普通表都默认是一个分区表。根据这一特性提出“滑动窗口”概念，在处理历史数据转移时只需要修改数据库字典即可实现实时和历史数据的分离，极大的提高了数据分离的效率，并且几乎不影响数据的物理存储结构。在工作过程中根据SQL Server 2005分区表特性制定历史数据管理策略，处理软件产生的历史数据。
3、利用SQL Server 2005 的数据库快照机制快速恢复数据。
当SQL Server数据达到100G以上时，对其进行备份恢复操作非常耗时。即使使用sp_attach_db也要等待数据文件长时间的拷贝。使用SQL Server 2005 的数据库快照机制可以快速的实现数据库恢复。并且操作非常简单。在工作中对要求一致性的报表统计和实验数据的恢复都使用了这一新技术，并且取得了良好的效果。
4、利用SQL Server 2005 的Ranking 函数集实现数据分页。
在SQL Server 2005之前的SQL Server实现分页都需要预先存储在表变量或临时表中，究其原因是因为没有类似Oracle的Rownum功能，只能通过建表语句中的identity功能实现，效率比较低下。在SQL Server 2005 的Ranking 函数集中已经支持了Rownum功能，在工作中的数据库记录分页也是通过这一特性实现的。
5、利用SQL Server 2005的索引附加字段功能提高查询效率。
当一次查询涉及到的表中字段总和超过900Bytes限制的时候，以前的SQL Server版本一般都是为where谓词中的字段建立非聚集索引。这样无法实现“索引覆盖”，需要重新定位到表中检索select谓词中的字段，效率较低。在使用新特性索引附加字段后，将select谓词字段作为附加字段存储在非聚集索引树的叶子节点上，既保持了索引键更“窄”的原则，而相对于表检索又减少了I/O，因此将很大的提高查询效率。通过日常使用过程中的观察，使用索引附加字段，相比传统的非“索引覆盖”索引效率平均提高20%-30%左右。
6、利用SQL Server 2005的T-SQL功能扩展实现更优雅的开发。
在利用SQL Server 2005的T-SQL中新提供的诸如Try-Catch错误处理、CTE、APPLY等机制，为T-SQL的开发提供了更优雅的实现。不光带来了现代开发语言的气息而且增加了程序可读性和严谨性。

行业借鉴经验：
1、数据库优化的关键在数据库设计。
数据库物理设计是影响数据库软件性能的重要因素，而在设计开发阶段优化数据库也是一个难点。数据库的优化工作应该在需求设计阶段就开始着手。请需求人员说明模块的主要应用场景和今后大致的数据量。在做数据库物理设计的时候充分考虑这些因素，并结合所使用的DBMS特性，设计性能优良的数据库。因为系统并没有经过用户的检验，无法得知存在的性能瓶颈。所以必须通过不同数据量的反复测试找出“热表”等性能瓶颈，并相应做出调整，再进行检验。整个数据库优化过程是一个迭代的过程，要求数据库优化人员不但要有丰富的数据库和软件开发经验而且要有足够的耐心和对细节的敏锐观察。在开发阶段解决大部分的数据库性能问题，不光节约了软件投入使用后的修改成本，而且对提升客户满意度也有着重要的意义。


2、OLTP和DSS应用中表的设计。
OLTP系统的主要特点是对表频繁的DML操作，而大规模统计查询较少。为了保证数据的逻辑完整性，会利用数据库锁频繁锁定表或其中的记录。根据这一特点，在设计表的时候应该尽量提高范式级别，使表更“窄”，以减少每次锁定的范围。必要的时候可以将一个完整的业务实体进行纵向分割，将频繁更新的字段分布到更多的表中存储。对于Oracle这种采用乐观锁事务隔离级别的DBMS系统，锁阻塞的几率会较少。但是对于早期SQL Server几乎全部是悲观锁事务隔离级别的DBMS系统，对表的纵向拆分就显得非常重要了。
DSS系统主要以查询统计为主。对于这种系统可以适当降低范式级别，将可能会频繁关联查询的表组合在一个表中，减少频繁关联查询所带来的CPU等资源消耗。并且可以根据DBMS类型的不同，适当的使用实体化视图（Oracle）或索引视图（SQL Server）来缓存统计结果，加快统计速度。
3、利用存储过程提高效率和方便DBMS迁移。
存储过程可以减少客户端与数据库之间的交互次数，提高批处理和数据库端业务算法的性能。而且存储过程作为DBMS的全局变量存储，相比常规SQL减少了SQL语句的解析编译时间。使用存储过程是提高数据库效率的重要手段，而且能够提高后期维护人员修改软件Bug的效率。
目前有的应用系统为了减少数据库移植带来的麻烦，尽量避免使用存储过程。这种做法是值得商榷的，其带来的直接后果就是系统性能低下。在工作负荷较轻的系统中可能影响并不明显，但是在需要频繁进行复杂数据库运算的系统中，这种负面影响是很容易暴露出来的。正确的做法是，在系统中如果使用到某种DBMS系统特定函数时，应该将其相关算法作为存储过程存储在数据库中，然后由前端程序调用该存储过程。程序代码（或O/R Mapping）中只包含符合SQL标准的SQL语句。当需要更换DBMS系统时，可以由DBA对存储过程进行集中翻译或者通过相关软件批量完成这一工作。在国外一些成熟的软件系统中存储过程的数量有时会超过数据库中表的数量。
4、杜绝数据库对象使用DBMS关键字命名。
在进行数据库物理设计之前，应该首先规范数据库对象的命名规则。表、视图等对象利用前缀能够很好的避免使用DBMS关键字。但是字段很少采用前缀命名，一般都是采用英文或其缩写命名。这种做法很容易导致字段命名使用到DBMS关键字或保留字。这将为今后系统的运行带来隐患。本人在至少三个软件系统中遇到过字段命名使用关键字所引发的Bug。而且一旦发生Bug很难查找，修改的时候也会带来很多连带修改。

应用难点技巧：
1、动态传入参数：
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level',
N'@level tinyint',
@level = 109;














2、动态传出参数：
declare @OutCount int
EXECUTE sp_executesql
N'select @InCount=count(*)
from sys.tables',
N'@InCount int output',
@InCount=@OutCount output;
select @OutCount

3、使用表变量得到动态SQL结果集：
Declare @MinID bigint
Declare @MaxID bigint
Declare @strSelect NVarchar(255)
Declare @SourceTableName NVarchar(255)
Set @strSelect = 'Select Min(ID),Max(ID) From ' + @SourceTableName
--声明表变量，其效率要高于临时表
DECLARE @TempTable TABLE(MinCount BIGINT,MaxCount BIGINT);
INSERT INTO @TempTable(MinCount,MaxCount) execute SP_Executesql @strSelect;
select @MinID=MinCount,
@MaxID=MaxCount
from @TempTable;