如何提高SQL Server数据仓库性能

如何提高SQL Server数据仓库性能（精选8篇）

篇1：如何提高SQL Server数据仓库性能

数据仓库通常是企业内部最大的数据库了，构建和管理系统是项大的任务，这些项目会由于众多用户提供的不兼容的输入而很快变得难以控制。提高系统的查询性能是可以实现的，但是必须要经过周密计划，随后还有具有远见的设计和开发阶段。在这篇文章中，我们将会列出获得并且为性能需求计划的一些技术，然后我们会在SQL Server上提高你的数据仓库性能。

需求

对于需要支持数百个GB到几个TB的数据系统而言，性能永远不会是你需要考虑的最后一件事情。当你收集数据仓库需求的时候，你就会被训练掌握用户性能需求的规则了。基于这些期望，可以考虑以各种方式将数据传送给他们。

系统确立

一旦你确定了用户的需求，为系统的未来六个月到接下来的3年到5年内的增长作出一个估计。判断在经过一段时间之后用户的数量。接下来，判断哪些业务功能会平衡数据和缓慢的性能对他们生产效率的影响。这些估计都可以为你提供关于用户想要使用数据还有对他们对性能的期望的概念。现在是时候为分散的数据确定一个策略了。

数据传递

要理解用户是如何想要使用和访问数据是非常重要的。如果他们基本上每天看报告和一些随时的报告，那么你可以非常幸福地在这些时间之后完成大部分繁重的任务了。其中包括从操作系统中载入数据，然后查询数据来构建和传递报告。这与支持一个几乎是实时的数据仓库是完全不同的过程，在实时的数据仓库中，数据必须要从操作系统中以小时为单位进行更新，以便决策可以及时到达各个部分或者整个企业。

应用程序选择

一旦你确立了系统和数据的传递，那么开始考虑支持数据仓库的基础架构吧。这不仅仅是硬件设备和数据仓库的SQL Server版本，还包括客户端应用程序和客户端硬件设备。你一定不会想要这样的情况，你有非常有力的SQL Server数据仓库，但是客户端机器却非常弱，几乎不能支持报告中的大量数据。另一个需要考虑的就是前端应用程序，这根据供应商而定，从一个简单的接口到为你的业务定制的复杂特性。

SQL Server硬件设计

一旦你配置了你的硬件，那么要对整个IT企业内的系统进行修改，不明显增加时间上的投资是很难的。为未来作出计划。要么是购买一个可以让你升级CPU，内存，磁盘驱动器，控制器等东西的系统，并且你很自信你可以在未来的1年到3年购买设备;要么在最开始就购买一个超大马力的服务器，但是会在很长时间内支持你的需求。当涉及到配置磁盘驱动器以正确支持SQL Server，请参考我的文章优化SQL Server 硬件性能。根据数据量，用户和预算的多少，你可能会考虑支持I/O需求的SAN。

系统的生命

基于硬件，应用程序和用户需求，从IT的角度告诉你的用户这个系统的生命长度。让他们知道当前的系统将会在一段时间之内支持特定数量的用户需求。例如，这个系统会在三年之内支持50个并发用户，到那个时候，系统就需要重新评估了。

数据仓库数据库设计

基于报告和团队对数据仓库的经验，你可能会有一个或者多个数据库。在多个数据库的设计中，你可以为下列目标建立特定的数据库:

从操作系统中载入数据

从数据装载数据库中清洁行数据

为用户查询对数据进行合计或者摘要

归档数据

这些数据库都为开发人员提供了一种方式来从操作系统中导入数据，操作数据，以确保它没有错误，然后再构建报告或者为用户的使用进行数据摘要。从性能的角度来说，把这些数据库分散到不同的磁盘驱动器上是有好处的。这可以防止I/O冲突导致的对系统间性能的限制。

从前在的数据库设计角度来说，支持数据装载的数据库可能会与操作系统匹配，

经过清洁的行数据可以在空间上或者关系设计上满足数据仓库的需求。最后，数据库的合计/摘要设计可能会结合空间或者关系设计。对于单个的报告，它还可以简化为一个表来提供数据支持。

下一部分我将会解释一下归档数据。

数据管理

基于数据库对于数据类型的设计，要时刻注意数据集增长的速度。例如，考虑将数据根据日期在逻辑上划分到小一点的表上去，例如每季度或者每月一张表。如果你可以在功能上划分数据，判断它是否具有意义。例如，如果你有电话数据，那么将所有的表放到同一张表上有意义吗，还是把它们分散到手机电话数据、家庭电话数据、公司电话数据、长途、本地等更有意义?

另外一个考虑就是与你的用户协商归档数据的频率。根据数据或者行业，你可能有一些法定的限制。数据量会对性能产生最大的影响。判断你的原始数据库是否可以支持有限时间长度，然后移动老一些的数据到归档数据库中，以同样的数据库设计。如果需要研究和报告的话，这个数据仍然可以接受访问，但是它不会影响主数据库支持99%的查询。

SQL Server特性和配置

这篇文章中列出的很多项目实际上都与SQL Server无关，并且潜在地会被支持数据仓库的任何数据库平台使用到。现在我么跳到特别为SQL Server准备的内容上去，看看它如何提高一个新的或者现有的数据仓库的性能。这些项目包括:

SQL Server 分区

分区是一个新的SQL Server 2005特性。在分区上，可以预先判断的基础文件群的数量内，水平分割表的数之范围之外再选中一卷或者多个卷。一个常见的分区卷就是日期卷或者对标进行的逻辑分组，例如区域、时区等。这会潜移默化地为大表上的查询提高性能，这在数据仓库中是很常见的，因为文件组都可以划分在多个基础的物理磁盘上，这可以提高I/O速度。你还可以为索引创建分区，同样也会提高性能。要获得有关分区的额外信息，请参考计划分区表和索引的指导。

如果分区表对你来说是个新事物的话，那么考虑使用Database Engine Tuning Advisor作为了解你的系统和分区特性的一种方式。数据库引擎调整指导为你提供一些性能改善的建议，其中就包括分区。只要保存SQL Server Profile会话的结果，数据库引擎调整指导可以分析这些结果，并且提供潜在的分区建议。要采纳这些结果要非常谨慎，并且充分计算了与分区建议有关的性能测量参数。接下来，用现有的配置和推荐的配置来测试性能测量标准以判断优势。

SQL Server 本地和分散分区视图

沿着同一条线，分区特性也是本地和分散的分区视图。本地视图包括了同一个服务器上的表和分散在可以认为是一组服务器联合的多个服务器的视图。这些视图对于查询位于多个地点的静态数据非常有好处。视图作为一种可以结合存储在潜在表中的水平数据的方式提供服务。要获得有关分区视图的更多信息，请参考创建分区视图。此外，查看联合数据库服务器。

最大并发程度是一项SQL Server配置，它可以让数据库管理员良好地调整优化器如何使用CPU。当激活最大并发数的时候，在SQL Server下是默认情况，所有的CPU都可以在并行处理中使用，余下的被非并行处理使用。这个配置可以改善CPU的能力，以获得更高的并发程度。要限制查询使用并发查询计划，根据查看你环境中的代表性查询的查询计划成本来设置并发配置的Cost Threshold。要获得更多有关并行最大数的信息，请参考并发选项最大数.

SQL Server数据库维护

数据仓库需要维护，就像是其他的关系型数据库引擎应用程序。当你开始这个项目的时候，确保列个时间表为每个负载规律地打开维护窗口，每周或者每月。小量的维护，例如索引重建和更新统计数字，长期进行都可以带来性能的提高。后台表的设计可以决定维护的时间。你可以在短期的维护窗口中与一些当前的维护处理一起执行处理，或者你需要更多的时间在非常大的表和索引上执行。你可以在SQL Server综合服务:简化数据库维护中的SQL Server 2005数据库维护中找到更多有用的信息

结论

提高你的数据仓库的性能在项目的任何阶段都是可能的。你可以通过在系统的生命周期中首先收集需求，然后构建一个满足用户需求的系统来获得最大的收益。SQL Server 2005提供了额外的优势来提高当前实现的系统，所以要确保找到这些功能来帮助你的应用程序。编码快乐……

篇2：如何提高SQL Server数据仓库性能

好了，咱们开始吧。我说的这个项目需求很简单，因为是简化版本的么。这是一个游戏中使用到的物 品的销售分析软件。里面包括几个概念，游戏，销售部门，物品，交易金额，交易笔数，发布单数等，

我们要做的事情就是按游戏，按部门，按物品来实现对交易金额，笔数等的数据交叉分析。

在我们这个系统里面，我们的数据颗粒度是天。好了，既然是简化版，我们也就不用那么罗嗦，什么 需求分析，分析设计都省了吧，下面直接进入数据库设计。

我们的数据库一共包括四张维度表(部门维度，游戏维度，物品维度，时间维度)，一张事实表(游 戏交易数据事实表)。

部门维度表

游戏维度表

物品维度表

时间维度表

交易数据事实表

由于我们的这个案例比较简单，所以维度与事实表之间的关系也比较简单，是一个简单的星型架构。

篇3：如何提高SQL Server数据仓库性能

Microsoft SQL Server视图的主要作用是:

1.1提供一种安全机制, 将用户限制到一个或多个基表的某个数据子集中。

1.2提供一种机制, 允许开发人员自定义用户通过逻辑方式查看存储在基表中的数据的方式。

在SQL Server 2005中, 具有唯一的聚集索引的视图称为索引视图。

2利用索引视图提高系统性能的方法

2.1可在视图上使用非聚集索引。

视图上的非聚集索引可提供更好的查询性能。与表上的非聚集索引类似, 视图上的非聚集索引可提供更多选项, 供查询优化器在编译过程中选择。例如, 如果查询包含聚集索引所未涉及的列, 那么优化器可在计划中选择一个或多个辅助索引, 避免对索引视图或基表进行费时的完全扫描。

对架构添加索引会增加数据库的开销, 因为索引需要持续的维护。在索引数量和维护开销间寻求适当的平衡点时, 应谨慎权衡。

2.2可在查询优化器中使用索引视图。

SQL Server查询优化器自动决定何时对给定的查询执行使用索引视图。不必在查询中直接引用视图以供优化器在查询执行计划中使用。所以, 现有的应用程序可运用索引视图, 而不用更改应用程序本身。

查询优化器通过考虑几个条件来决定索引视图能否涵盖整个或部分查询。这些条件对应查询中的一个FROM子句并由下列这几个部分组成:

2.2.1查询FROM子句中的表必须是索引视图FROM子句中的表的超集。

2.2.2查询中的联接条件必须是视图中的联接条件的超集。

2.2.3查询中的聚合列必须可从视图中的聚合列的子集派生。

2.2.4查询选择列表中的所有表达式必须可从视图选择列表或未包含在视图定义中的表派生。

2.2.5如果与其他谓词所匹配的行的超集相匹配, 那么该谓词将归入另一个谓词。例如, “T.a=10”归入“T.a=10 and T.b=20”。任何谓词都可归入其自身。视图中限制表值的那部分谓词必须归入查询中限制相同表的那部分谓词。此外, 必须以SQL Server可验证的方式实现这一点。

2.2.6属于视图定义中的表的查询搜索条件谓词的所有列必须出现在下列视图定义的一项或多项中:

(1) 一个GROUP BY列表。

(2) 视图选择列表 (如不存在GROUP BY) 。

(3) 视图定义中相同或等价的谓词。

情况 (1) 和 (2) 允许SQL Server对视图的列应用查询谓词, 以便进一步限制视图的列。情况 (3) 比较特殊, 它不需要对列进行筛选, 因此该列不必出现在视图中。如果查询不止包含一个FROM子句 (子查询、派生表、UNION) , 优化器可能选择几个索引视图来处理查询, 并将它们应用到不同FROM子句。

2.3可使用NOEXPAND视图提示。

当SQL Server处理按名称引用视图的查询时, 视图的定义只有在仅引用基表时才会被正常扩展。这个过程称为视图扩展。其属于一种宏扩展形式。

NOEXPAND视图提示可强制查询优化器将视图视为带有聚集索引的普通表。其可防止视图扩展。只有在FROM子句中直接引用索引视图, 才会应用NOEXPAND提示。例如:

SELECT Column1, Column2, ...FROM Table1, View1WITH (NOEXPAND) WHERE...

2.4可使用EXPAND VIEWS查询提示。

处理按名称引用视图的查询时, 除非对视图引用添加NOEXPAND提示, 否则SQL Server总会扩展视图。该提示会尝试匹配索引视图和扩展查询, 除非在查询末尾的一个OPTION子句中指定EXPAND VIEWS查询提示。例如, 假设数据库中有一个索引视图View1, 以下, 根据其逻辑定义 (其CREATE VIEW语句) 对View1进行了扩展, 然后EXPAND VIEWS选项会阻止在计划中使用View1的索引视图来解析该查询。使用语句如下:

SELECT Column1, Column2, ...FROM Table1, View1WHERE...

OPTION (EXPAND VIEWS)

如要确保让SQL Server通过从查询所引用的基表直接访问数据来处理该查询, 而不必访问索引视图, 那么可使用EXPAND VIEWS。在某些情况下, EXPAND视图有助于消除因使用索引视图而导致的锁争用。在测试应用程序时, NOEXPAND和EXPAND VIEWS都可帮助用户在使用和不使用索引视图的情况下进行性能评估。

对数据库系统确定一组适当的索引可能很复杂。如果在设计一般索引时需要考虑众多可能性, 那么对架构添加索引视图会大幅提高设计和潜在结果的复杂性。例如, 索引视图可用于:

2.4.1查询中引用的表的任何子集。

2.4.2该表子集的查询中的条件的任何子集。

2.4.3组合的列。

2.4.4聚合函数 (比如:SUM) 。

2.5可应用DTA优化物理数据库的设计。

数据库优化顾问 (DTA) 是SQL Server 2005的一项功能, 可有效优化物理数据库设计。使用DTA可让管理员有一个更清晰的思路, 可以更准确地、有针对性地组合使用确定索引、索引视图和分区策略, 从而简化了物理数据库的设计过程。

3结束语

下图说明了使用以上方法通常所能取得的性能改进程度:

实践证明, 灵活使用上述方法, 能提高数据库整体性能。

摘要：本文阐述如何使用SQL Server 2005 Enterprise Edition的索引视图改善数据库系统的整体性能。

关键词：SQL Server 2005,database,DBMS

参考文献

[1]Microsoft SQL Server 2005 Enterprise Edition English version helper document.

[2]ROBIN DEWSON.《SQL SERVER2005基础教程》[M].北京:人民邮电出版社, 2006.

篇4：如何提高SQL Server数据仓库性能

摘要：本文对数据库应用系统的各个部分特别是数据库服务器、SQL语句、存储过程等的性能调整作了大量的分析和试验，提出了一些具体的性能调整方法和措施，并取得较好的应用效果。

关键词：SQL;数据库;性能优化

一、 基于SQLServer2000的数据库性能调整

1.系统规划

数据库服务器是整个数据库应用系统的核心，它的性能高低直接影响整个系统的性能。SQL Server2000数据库的许多方面都可以被优化或调整，以便给予系统更好的性能，诸如硬件、SQL Server配置、数据库设计、SQL语句、SQL索引、复制、备份与恢复及其他。

2.优化SQL语句

SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。在这里就不展开了，在第五章将有专题讨论。

3.存储过程调整

SQL Server存储过程是用Transact-SQL语句PROCEDURE创建的，并可用ALTER PROCEDURE语句进行修改。存储过程定义包含两个主要组成部分：过程名称及其参数的说明，以及过程的主体所有设计优良的Microsoft SQL ServerTM 2000应用程序都应当使用存储过程。不论是否将应用程序的业务逻辑写入存储过程都应如此。

4.高性能备份与恢复

需要确定数据的可用性要求，以便选择适当的备份和还原策略。总体备份策略定义备份的类型和频率以及所需的硬件特性和速度。测试备份和恢复过程。测试有助于确保拥有从各种故障中恢复所需的备份，并且当真正的故障发生时可以快速平稳地执行恢复过程。

5.用户管理

工程设计企业传统的组织结构按专业及职责设置，是面向部门的层次管理结构。这种组织结构管理层次多，各个机构间协调复杂，造成了信息交流和传递困难，设计周期长等问题。

二、优化SQL语句和存储过程

数据库调整中一个很重要的方面就是应用程序的调整，关键在于SQL语句的优化和存储过程的应用。本章结合具体的项目实践，讨论了一些关于SQL语句的优化和存储过程的应用的方法和措施。

1.优化SQL语句

SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

2.存储过程

存储过程(Stored Procedure)是一组编译在单个执行计划中的Transact一SQL语句。Microsoft SQLServerTM2000的存储过程可以通过输入参数接受输入，并能够以下面四种方式之一返回数据：输出参数，既可以返回数据(整型值或字符值等)，也可以返回游标变量(游标是可以逐行检索的结果集)；如果返回代码，始终是整型值；SE比CT语句的结果集，这些语句包含在该存储过程内或该存储过程所调用的任何其它存储过程内；可从存储过程外引用的全局游标。

3.B/S模式下的备份与恢复

B/S模式下的备份与恢复的实现步骤：

（1）编写存储过程

存储过程的编写需要遵循SQL语言语法，在SQLServer企业管理器中打开master数据库，打开存储过程，右键新建存储过程，会出现存储过程属性的SQL编辑器，然后按语法直接编写。下面显示文件备份的编写过程。

（2）JSP语句调用存储过程

为清楚说明JSP语句调用存储过程的实现过程，现将JSP语句按功能分解：

—实现与数据库连接功能

Driver DriverCallablel=(Driver)C1ass.forName(MM_Cmaster_ DRIVER). newInstance();

Connection ConnCallablel =DriverManager. getConnection (MM_Cmaster_ STRING, MM_Cmaster_ USERNAME,MM_Cmaster_PASSWORD);

—实现调用存储过程功能

CallableStatement Caliablel=ConnCallablel.prepareCall(“{?=call dbo.backup_diffrience(?，?)}”);

—实现存储过程中变量传递功能

Object Callablel_data;

Callablel.registerOutParameter(1，Types.LONGVARCHAR);

Callablel.setString(2, Callablel_bname);

Callablel.setString(3, Callablel_dir);

—实现存储过程执行和关闭功能

Callablel.execute();

ConnCallablel.close();

三、结论

文对基于SQL Server2000的数据库性能调整进行了较为全面、系统的研究，希望总结出数据库性能调整的一般性原则和方法，并取得了一些成果。

参考文献:

[1]袁鹏飞:SQLServer数据库应用开发技术人民邮电出版社1998. 5.

[2]赵 敏:基于SQL Server性能调整和测评方法计算机工程2000.5.

篇5：如何提高SQL Server数据仓库性能

-08-08SQL 返回期间内的所有日期

-02-02sqlserver 复制表 复制数据库存储过程的方法

-05-05SQL Server修改标识列方法 如自增列的批量化修改

2009-07-07SQLServer 批量插入数据的两种方法

2013-04-04sql存储过程的使用和介绍

-03-03SQL Server DBA日常检查常用SQL

2014-04-04通过系统数据库获取用户所有数据库中的视图、表、存储过程

-02-02一个查看MSSQLServer数据库空间使用情况的存储过程 SpaceUsed

篇6：如何提高SQL Server数据仓库性能

SQL Server作为一种被广泛使用着的数据库管理系统, 它能满足当下数据库系统的各种要求和不同类型的数据库解决方案, 具有着许多显著的优点, 如:易用性、集成性、可伸缩性以及用于决策支持的数据仓库功能等, 在越来越多的商业技术领域中使用。但随着使用的时间增长, 必须要面对的问题是:当数据信息积累到一定的规模时, 必将导致数据库的性能下降, 数据查询的效率变慢, 甚至到无法容忍的地步。定期维护数据库和对系统的性能进行优化就显得尤为的重要。

1 设计优化策略

设计阶段是决定系统性能的关键阶段, 只有设计合理, 才能减少瓶颈问题, 减少CUP利用率, 减少资源争用。

(1) 数据库语言编程优化。在设计阶段, 数据库系统应遵循规范化的要求, 注重语句的书写规范, 避免因语句的大小写、输入错误等影响数据库的查询。避免大量的使用SELECT语句, 通过SELECT语句对数据进行数据查询是程序编写中最常用的一种方法。但是, SELECT语句使用过多, 需要较长的时间从数据库系统中提取数据, 容易造成数据库系统运行缓慢。

(2) 索引优化。根据数据量决定哪些表需要增加索引, 数据量小的可以只有主键。根据使用的频率决定哪些列需要建立索引, 将经常作为连接条件、筛选条件、排序、经常使用在WHERE子句中的列作为索引侯选列;还可以把经常出现的列组合在一起, 组成复合索引, 在创建的时候注意重复率低的列放在前面。一个表中不要有太多的索引;如果表经常被用来做修改操作的或者定义为text、image和bit数据类型的列, 就不建议添加索引了。要注重对索引的维护, 周期性的重新组织或重新生成索引。如果不正确的使用索引, 不但不会提高查询效率, 反而会降低更新速度, 造成严重的问题。

(3) 数据库对象的存储。数据库对象存储也是有一定策略的。一般数据库对象应是均匀地把数据分布在系统磁盘中, 这样访问分散到不同的磁盘, 使用户数据尽可能跨越多个设备, 多个I/O运转, 避免I/O竞争, 克服访问上的瓶颈。同时, 分离系统数据库和应用数据库, 把系统审计表和临时表放在不忙的磁盘上, 把事务日志放在单独的磁盘上, 减少磁盘I/O的开销;把频繁访问的表放在不同的磁盘上;把频繁用的表、频繁做Join操作的表分别放在不同的磁盘上;甚至可以把频繁访问的表的列放在不同的磁盘上, 这样做可以把访问分散到不同的磁盘上, 避免I/O的争用问题。

2 查询优化策略

优化数据库系统查询功能, 可以让用户在短时间内寻找到所需要的数据, 提高效率。在一般情况下, 我们可以考虑以下几点来对查询进行优化:

(1) 不要写SELECT *的语句。当我们想要列出表中所有的信息时, 这是一个简便的方法, 但是数据库在解析的时候会将”*”转换成所有的列名, 这意味着将耗费更多的时间。所以要在查询中选择需要的列。

(2) 应避免在WHERE中进行数据的计算, 若计算的数据过多、过大, 容易造成数据库系统索引失效而进行全表扫描, 进而影响数据库系统运行效率。另外, 应多使用AND连接, 减少长字符串连接或OR连接的使用。

(3) 当采用谓词LIKE作查询时, 如果以”_”或”%”等通配符作为条件的开始就会导致SQL Server无法使用索引而影响查询效率。如:SELECT Student Name FROM Student WHEREStudent Name LIKE“_海”, 在使用的时候尽可能避免这种语句的出现。

(4) 避免使用子查询语句。当一个查询中使用了子查询, 查询速度难免就会受到影响。查询嵌套的层次越多, 查询的效率就越差。

(5) 查询中避免使用”!=”、”not exist”和”not in”等, 这些查询的效率极低, 不但需要花费大量的时间, 而且会耗费相当大的系统资源, 甚至会造成其它进程的阻塞。在实际应用中, 尽可能用其它替代。

(6) IS NULL或IS NOT NULL操作的限量使用。这是因为不能用null作索引, 任何包含null值的列都将不会被包含在索引中。即使索引有多列的情况下, 只要这些列中有一列含有null, 该列就会从索引中排除。这也就是说如果某列存在空值, 即使对该列建索引也不会提高性能。

(7) DELETE语句的优化。在一般的删除操作中我们都使用DELETE语句。对于小数量的使用该语句没有任何问题, 但是一些大的数据表来说, DELETE就会产生一定的影响, 它是一种事物性操作, 会将操作记录到SQL的事务日志中, 这样不但增加时间, 而且由于频繁的写入, 会导致Log文件过大, 过于消耗磁盘空间。在这种情况下, 可以考虑使用TRUNCATE来代替DELETE, 这样既不会写入事务日志, 执行速度较快。

除此之外, 对于SQL Server数据库查询优化, 还可以采用SQL Server数据库本身自带的一些数据库管理工具, 熟练运用这些工具能够提高优化工作的效率。如在SQL Server2008中, SQLServer Profiler和数据库引擎优化顾问是比较常用的两个工具。

3 存储过程的优化策略

存储过程是由一些SQL语句和控制语句组成的被封闭起来的过程, 它驻留在数据库中, 可以被客户应用程序调用, 也可以从另一个过程或触发器调用。使用存储过程的好处在于可以减少网络通信量;提供单点维护;抽象化业务规则;增强安全性;能支持执行计划重复使用等。在环境允许的情况下, 将易于变化的业务规则;需要集中管理和控制的逻辑与运算处理;需要对基本表的数据进行较复杂的逻辑处理才能返回所需的结果数据集等这样的一些操作可以考虑放入存储过程中完成。但在使用的过程中要注意:

(1) 使用SETNOCOUNT ON。在默认情况下, 存储过程将返回过程中每个语句影响的行数。如果不需要在应用程序中使用该信息, 那么就在存储过程中使用SET NOCOUNT ON语句以终止行数的统计。尽管这不是一个大的问题, 但它可以为高流量应用程序的性能产生负面影响。

(2) 尽量使用OUTPUT参数。通过使用OUTPUT参数返回标量数据, 可以略微提高速度并节省少量的处理功率。

(3) 提供返回值。将一组返回值及其含义标准化, 并一致地使用这些状态信息返回给进行调用的应用程序, 这会使得处理调用应用程序中的错误更加容易。

(4) DDL和DML语句混合使用中的问题。在DDL和DML语句混合使用的时候, 将DML语句放在DDL语句之后执行, SQL Server将重新编译存储过程, 这是由于为了给DML创建计划, SQL Server需要考虑由DDL对该对象所作的更改, 强制存储过程多次进行重新编译, 对性能造成负面影响。

在SQL Server数据库被普及的今天, 人们对保障数据库运行的高效性、安全性的需求日益加大, 对其进行合理的设计和优化, 已显得尤为重要。本文中所提到的一些策略, 只是简单阐述了数据库性能优化的一些原则和方法。实际上, 影响数据库系统性能的因素还有很多, 这就需要数据库的设计师和管理员不断的进行探索、改进。

参考文献

[1]马力.SQL Server数据库性能优化研究[J].国际IT传媒品牌, 2013.11 (7) :146-148.

[2]罗云.SQL Server数据库查询语句优化的研究[J].信息通信, 2014, 4.

[3]李红丽.SQL Server数据库的查询优化探析[J[.长春教育学院学报, 2014.4 (7) .

篇7：如何提高SQL Server数据仓库性能

一、前期准备

为了使示例能够正常运行，需要在本机上安装Microsoft SQL Server2000数据库、Visual Basic6.0及Microsoft Excel，并准备一个Excel数据文件。这里以乌兰察布市气象局气象资料个别字段为例，格式如表1所示，数据记录数和字段数可根据需要增加。

打开Visual Basic6.0应用程序，新建一个工程，在form1中添加按钮，将其caption属性设为“Excel数据导入 (OpenSchema) ”，以实现通过点击命令按钮来运行VB代码。为了使示例能够自动选取存在本机上的Excel文件，本例应用了commandialog控件，因此这里需要首先添加相应的部件 (详情如图1) ，部件添加后，在工具栏里就会出现一个工具，将它添加到form1窗口的任意位置即可，做好的界面如图2所示。

二、代码编写

1. OpenSchema方法

OpenSchema方法是一种本地Excel导入异地SQL的方法。

双击“Excel数据导入 (OpenSchema) ”按钮，在代码编辑器中输入以下代码。

这种方法的优势就在于它可以自动读取Excel工作簿中各工作表名称, 循环将本地Excel数据批量导入到远程的SQL Server数据库。

2.利用ODBC连接远程数据库

其中tabdata为数据库中事先建的同结构的数据表名称, currdata!TABLE_NAME为调用的当前工作表的名称。

三、结束语

通过详述了在VB6环境下, 用代码实现常用办公数据文件Excel批量导入SQL Server数据库系统的方法。该方法实现了本地Excel数据导入异地SQL Server数据库, 可以这说是类似应用的一个难点, 希望能给广大编程爱好者以启迪。

摘要：基于Visual Basic编程语言, 举例说明利用VB实现循环将本地Excel数据批量导入到远程的SQL Server数据库的方法。

关键词：VB,Excel,数据导入,SQL Server

参考文献

[1]孔志勇, 王毅, 房大伟.SQL应用范例宝典.北京:人民邮电出版社, 2008.

篇8：如何提高SQL Server数据仓库性能

目前电子商务系统随着业务的发展都演变成基于多数据库服务器的系统, 多台数据库服务器分散在不同的物理机器或者虚拟机上。基于这样的平台如何搭建统一的数据仓库应对快速增长的业务数据成为了企业经营分析过程中的一大难题, 其中数据导入数据仓库的过程耗时越来越多和数据分析性能逐渐下降则成为主要问题, 很大程度上影响了用户的体验。基于这样的现实, 结合目前流行的Hadoop技术, 重构系统的数据仓库以及CUBES势在必行。通过重构, 希望能够达到的目标是数据的导入工作耗时缩短, 数据分析的性能得到提升及数据仓库的扩展性得到强化。

本文第一部分介绍研究背景;第二部分主要介绍研究的相关工作;第三部分为研究的核心部分, 包含数据仓库的建立、数据导入和查询设置相关设计;第四部分为实验部分包含介绍实验环境和实验测试性能。最后总结研究过程中需要注意和在今后的研究中需要改进的地方。

1 相关工作

Hadoop是Apache基金会所开发的分布式系统基础架构。其核心产品为HDFS (Hadoop Distributed File System, Hadoop的分布式文件系统) 和Map Reduce。其中HDFS提供存储, Map Reduce提供计算。从而提供了海量数据的存储计算功能。是目前比较流行的海量数据处理的框架。Hadoop的架构以其高可靠性、高扩展性、高效性、高容错性和低成本深受广大互联网公司和研究机构的重视。目前已经被应用于各行各业。基于Hadoop的数据仓库的设计和实现涉及以下相关相产品。

HDFS是对Google的GFS的开源实现。为Hadoop上层应用提供高吞吐量、大扩展的文件存储服务。其高吞吐量, 高容错性及其部署廉价为其主要特点。主要用于存储业务数据。

Map Reduce分布式并行计算的编程模式, 是对Google开发模式的开源实现。这个编程模式主要由两大步组成Map (映射) 和Reduce (化简) 。Map面对的是杂乱无章的互不相关的数据, 它解析每个数据, 从中提取出key和value, 也就是提取了数据的特征。在Reduce阶段获得已经归纳好的数据, 在此基础上我们可以做进一步的处理以便得到结果。

Hive作为Hadoop的数据仓库工具, 能够将结构化的数据文件映射成一张数据库表, 并提供类SQL的查询功能, 并能与Map Reduce无缝结合, 便于数据的统计分析。

Sqoop是Hadoop与传统数据库之间的转化工具。提供了很多的操作命令, 能够批量导入, 导出为数据的初始准备提供了条件。目前, Hadoop相关技术已趋于成熟。Facebook在数据仓库的建设方面有很深刻见解, Hive就是出于他们之手。国内淘宝也采用Hadoop的相关技术构架大数据的分析平台, 其中“数据魔方”产品就是典型的基于Hadoop的数据仓库的产品。国内学术界, 清华大学, 中科院都在尝试采用Hadoop的技术打造一个大数据的分析挖掘平台。国外则更加倾向于提高性能方面的研究, 改善Map Reduce的执行机制, 更加高效快速的执行查询等。基于Hadoop的上述相关产品, 结合国内外相关研究成果, 整合其优势, 搭建一个数据仓库并建立相关的CUBES, 能够很好地应对数据量的增长, 提升企业经营分析的效率。

2 系统架构

首先是搭建Hadoop的云环境。基于这样的云环境结合业务数据的特点搭建数据仓库和设计创建CUBES。Hadoop数据仓库的架构图, 见图1。

在建立好的数据仓库上加载数据, 对数据进行分析对数据处理过程中的相关属性进行设置以提升其处理能力。目前的电子商务系统其数据库大部分都是分散在不同的物理位置上或者虚拟机上, 如何从这些分散的数据源中获取数据搭建统一的数据仓库, 对于分析系统中的相关指标是具一定商业价值的。例如接下来会介绍搭建企业经营的数据仓库, 用于分析企业的相关指标。这个过程分为两步。

2.1 搭建数据仓库和CUBES

分析企业的经营状况主要可以从企业的利润率, 到账率, 库房存货量, 销售额四个方面评价, 所以基于这样的业务背景和数据特点, 本文采用星型模式, CUBES模型, 见图2。

Company表为事实表, 主要记录企业的相关信息和四个维度表的标记值。Profit维度表主要是记录企业的收入和支出。用于在分析过程中计算企业的利润率。Sale维度表主要是记录企业销售情况, 用于在分析过程中记录企业的销售额。Payment维度表主要记录企业的账目信息, 用于分析过程中计算企业的到账率。Store维度表主要是记录企业的库存信息, 用于分析过程计算企业的库存量。

以上五个表, 都是通过Hive的DDL语句进行创建, 对于每个表都采用了时间的分区方式进行分区, 精确到天, 对于数据的导入来说这是一个很大的改善。通过时间分区对于每天新增加的数据只需要另外存储起来, 不用重新将所有的业务数据导入到数据仓库中。而且这样的设计也大大提升按时间分析企业经营状况的性能。

2.2 数据导入以及查询的设置

基于上述建立好的数据仓库及其CUBES, 需要从关系型数据库中将业务数据导入到数据仓库中, 对此本文采用了Sqoop工具, 通过并发执行导入脚本从多个数据源中将关系型数据库数据导入到HDFS。在导入过程中我们采用Query查询条件对于原始数据进行处理例如过滤掉不需要的属性, 去重, 对于为空的字段采用填补的方式等。数据导入的实现是基于Map Reduce的并行计算方式, 相对于传统的数据导入, 性能和速度都有了很大的提升, 在之后的实验部分会有数据对比。完成数据从关系型数据库到HDFS的转换之后, 接下来工作就是加载HDFS上的数据到指定的数据仓库中。该部分中采用Hive的DML语句对数据进行加载, 该过程按照时间分区进行加载。至此, 数据仓库的导入工作完成。

在数据分析过程中, 基于对业务理解, 为了提升分析的性能, 采用索引的方式, 提升查询性能, 对于各个维度表及事实表中经常涉及到查询的列属性建立相应的索引。进一步提升了数据分析性能。考虑到基于Hive的数据仓库, 数据分析都是通过Map Reduce形式进行, 所以可以通过实际环境的要求对于Map和Reduce的个数进行设置, 以保证分析过程中的高性能。

3 实验

这部分首先介绍实验环境以及设置相关属性, 其次介绍实验内容、实验结果和分析。论文从以下三个方面进行实验:数据加载性能;对外提供分析服务性能;数据仓库扩展性。

3.1 实验环境

Dell64位服务器, 32G内存, 5T硬盘。Window server 2008操作系统, 虚拟7台Linux centos 6.5机器。主要配置见表1。

Master机器为namenode, 其他六台为datanode。master和六个node上面都配置了Hadoop的环境。其中在Master节点上额外配置了Hive和Sqoop的环境, 便于数据的提取和数据仓库的建立。

3.2 实验内容及结果

该部分旨在完成三个实验, 以实验结果为依据展示出基于Hadoop的数据仓库CUBES在各方面的优势。另外在图表中TW代表现有的传统数据仓库, HW代表基于Hadoop的数据仓库。横轴为数据的大小, 单位为GB;纵轴为时间, 单位为S (秒) 。

3.2.1 实验1

数据加载性能测试, 旨在对比现有系统的数据仓库和4个Node节点的数据仓库系统在数据加载方面的性能消耗。加载的数据量从从1G, 2G, 4G, 8G, 16G逐渐变化。从分散的数据源中加载数据, 不断修改导入数据的大小, 记录数据导入过程中消耗的时间。并记录是否出错。实验结果见图3。

随着加载数据量越来越大, 传统方式所消耗的时间急剧上升, 而采用Hadoop的方式显示出缓慢上升的趋势。通过实验可以得出, 采用Hadoop方式, 能够在数据加载方面节省时间, 提高数据仓库搭建的性能。从这个方面体现出基于Hadoop数据仓库在实际问题中的优越性。

3.2.2 实验2

对外提供查询服务的性能测试, 旨在对比现有系统的数据仓库VS 4个Node节点的数据仓库系统对外提供查询功能的效率。查询数据集大小从从1G, 2G, 4G, 8G, 16G变化。设置一个共同的查询请求。不断修改测试数据的大小, 从而记录查询返回的时间, 并记录实验过程中的出错情况。实验结果见图4。

通过本次实验, 对于同一个查询请求, 两种实现方式所呈现出来的效率是不一样的, 通过图表可以看出传统的方式对外提供服务的能力随着数据量的增大变得越来越弱, 消耗的时间越来越多, 而基于Hadoop方式对于应付大数量情况下的查询所体现出来的性能优势明显, 所以从另一方面体现了基于Hadoop的数据仓库在实际问题的解决中是可行的。

3.2.3 实验3

基于Hadoop的数据仓库可扩展性测试, 旨在对比6个Node节点的数据仓库和4个Node节点的数据仓库系统在数据加载和数据查询方面的性能。实验数据量分别为1G、2G、3G、4G、8G和16G。从数据加载和数据查询两个方面进行试验, 并记录试验的结果, 见图5, 6。

本实验主要是为了验证其数据仓库的扩展性, 通过图表可以看出, 不管是从数据加载还是对外提供查询服务来说, 基于6个Node节点的数据仓库比基于4个Node的数据仓库性能都要高。从加载数据方面, 基于6个Node数据仓库其性能更强, 速率更快, 更好的发挥出了分布式计算的优势。而在查询部分, 对于相同的查询请求, 其性能也是有很大的提高, 能够更加快速的响应用户请求。基于这个实验, 证明了基于Hadoop的数据仓库扩展性强, 能够通过扩展Node节点的个数, 提升整体的性能, 便于在以后发展中及时的调整, 以应对现实的需求。

4 结语

通过基于Hadoop的数据仓库Cubes的研究, 熟练的掌握了Hadoop下HDFS、Map Reduce、Hive及Sqoop技术的特性, 以及在实际项目中的使用。并对相关配置做了深入理解, 对于提升数据分析处理性能有很大的帮助。在后期的研究中, 会进一步对性能和扩展性进行优化, 以达到一个更优的状态。本文立足于实际项目中的问题, 借助于Hadoop的相关技术, 重构了系统的数据仓库设计并对其性能进行了验证。很好的解决了项目中的实际问题。在研究的过程中, 必须通过实验数据证明新技术的优势, 这样才能很好地投入到实际使用中去。在今后的学习研究中, 继续研究Hadoop的相关技术从而, 更好的为实际问题服务。

参考文献

[1]Abeló A, Ferrarons J, Romero O.Building cubes with Map Reduce[A].Proceedings of the ACM14th international workshop on Data Warehousing and OLAP[C].ACM, 2011:17-24.

[2]Choi, Hyunsik, et al.Tajo:A distributed data warehouse system on large clusters[A].Data Engineering (ICDE) , 2013 IEEE 29th International Conference on[C].IEEE, 2013:1320-1323.

[3]Thusoo, Ashish.Hive:aware housing solution over a mapreduce a framework[A].Proceedings of the VLDB Endowment 2.2 (2009) [C].2009:1626-1629.