新葡 京娱乐网址:四项技术 助你提高SQL Server的性能

无意偶尔,为了让利用法度榜样运行得更快,所做的整个事情便是在这里或那里做一些很小调剂。但关键在于确定若何进行调剂!迟早您会碰到这种环境:利用法度榜样中的SQL查询不能按照您想要的要领进行相应。它要么不返回数据,要么消费的光阴长得出奇。假如它低落了企


当前位置: 主页 >


无意偶尔,为了让利用法度榜样运行得更快,所做的整个事情便是在这里或那里做一些很小调剂。但关键在于确定若何进行调剂!迟早您会碰到这种环境:利用法度榜样中的SQL 查询不能按照您想要的要领进行相应。它要么不返回数据,要么消费的光阴长得出奇。假如它低落了企业利用法度榜样的速率,用户必须等待很长光阴。用户盼望利用法度榜样相应迅速,他们的申报能够在瞬间之内返回阐发数据。就我自己而言,假如在Web上冲浪时某个页面要消费十多秒才能加载,我也会很不耐烦。

为了办理这些问题,紧张的是找到问题的根源。那么,从哪里开始呢?根滥觞基本因平日在于数据库设计和造访它的查询。我将讲述四项技巧,这些技巧可用于前进基于SQL Server的利用法度榜样的机能或改良其可伸缩性。我将仔细阐明 LEFT JOIN、CROSS JOIN 的应用以及IDENTITY 值的检索。请记着,根本没有神奇的办理规划。调剂您的数据库及其新葡 京娱乐网址查询必要占用光阴、进行阐发,还必要大年夜量的测试。这些技巧都已被证实行之有效,但对您的利用法度榜样而言,可能此中一些技巧比另一些技巧更适用。

从 INSERT 返回 IDENTITY

我抉择从碰到许多问题的内容入手:若何在履行SQL INSERT后检索IDENTITY值。平日,问题不在于若何编写检索值的查询,而在于在哪里以及何时进行检索。在SQL Server中,下面的语句可用于检索由最新在活动数据库连接上运行的 SQL 语句所创建的 IDENTITY 值:

SELECT @@IDENTITY

这个 SQL 语句并不繁杂,但必要记着的一点是:假如这个最新的 SQL 语句不是 INSERT,或者您针对非 INSERT SQL 的其他连接运行了此 SQL,则不会得到期望的值。您必须运行下列代码才能检索紧跟在 INSERT SQL 之后且位于同连续接上的 IDENTITY,如下所示:

INSERT INTO Products (ProductName) VALUES ('Chalk')

SELECT @@IDENTITY

在一个连接上针对 Northwind 数据库运行这些查询将返回一个名称为 Chalk 的新产品的 IDENTITY 值。以是,在应用ADOVisual Basic利用法度榜样中,可以运行以下语句:

Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _

 (ProductName) VALUES ('Chalk');SELECT @@IDENTITY")

 lProductID = oRs(0)

此代码奉告 SQL Server 不要返回查询的行计数,然后履行 INSERT 语句,并返回刚刚为这个新行创建的 IDENTITY 值。SET NOCOUNT ON 语句表示返回的记录集有一行和一列,此中包孕了这个新的 IDENTITY 值。假如没有此语句,则会首先返回一个空的记录集(由于 INSERT 语句不返回任何数据),然后会返回第二个记录集,第二个记录集中包孕 IDENTITY 值。这可能有些令人利诱,尤其是由于您从来就没有盼望过 INSERT 会返回记录集。之以是会发生此环境,是由于 SQL Server 看到了这个行计数(即一行受到影响)并将其解释为表示一个记录集。是以,真正的数据被推回到了第二个记录集。当然您可以应用 ADO 中的 NextRecordset 措施获取此第二个记录集,但假如总能够首先返回该记录集且只返回该记录集,则会更方便,也更有效率。

此措施虽然有效,但必要在 SQL 语句中额外添加一些代码。得到相同结果的另一措施是在 INSERT 之前应用 SET NOCOUNT ON 语句,并将 SELECT @@IDENTITY 语句放在表中的 FOR INSERT 触发器中,如下面的代码片段所示。这样,任何进入该表的 INSERT 语句都将自动返回 IDENTITY 值。

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS

 SELECT @@IDENTITY

 GO

触发器只在 Products 表上发生 INSERT 时启动,以是它老是会在成功 INSERT 之后返回一个 IDENTITY。应用此技巧,您可以始终以相同的要领在利用法度榜样中检索 IDENTITY 值。

内嵌视图与临时表

某些时刻,查询必要将数据与其他一些可能只能经由过程履行 GROUP BY 然后履行标准查询才能网络的数据进行联接。例如,假如要查询最新五个定单的有关信息,您首先必要知道是哪些定单。这可以应用返回定单 ID 的 SQL 查询来检索。此数据就会存储在临时表(这是一个常用技巧)中,然后与 Products 表进行联接,以返回这些定单售出的产品数量:

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _

 OrderDate DATETIME NOT NULL)

 INSERT INTO #Temp1 (OrderID, OrderDate)

 SELECT TOP 5 o.OrderID, o.OrderDate

 FROM Orders o ORDE新葡 京娱乐网址R BY o.OrderDate DESC

 SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity

 FROM #Temp1 t

 INNER JOIN [Order Details] od ON t.OrderID = od.OrderID

 INNER JOIN Products p ON od.ProductID = p.ProductID

 GROUP BY p.ProductName

 ORDER BY p.ProductName

 DROP TABLE #Temp1

这些 SQL 语句会创建一个临时表,将数据插入该表中,将其他数据与该表进行联接,然后撤除该临时表。这会导致此查询进行大年夜量 I/O 操作,是以,可以从新编写查询,应用内嵌视图取代临时表。内嵌视图只是一个可以联接到 FROM 子句中的查询。以是,您不用在 tempdb 中的临时表上消费大年夜量 I/O 和磁盘造访,而可以应用内嵌视图获得同样的结果:

SELECT p.ProductName,

 SUM(od.Quantity) AS ProductQuantity

 FROM (

 SELECT TOP 5 o.OrderID, o.OrderDate

 FROM Orders o

 ORDER BY o.OrderDate DESC

 ) t

 INNER JOIN [Order Details] od ON t.OrderID = od.OrderID

 INNER JOIN Products p ON od.ProductID = p.ProductID

 GROUP BY

 p.ProductName

 ORDER BY

 p.ProductName

此查询不仅比前面的查询效率更高,而且长度更短。临时表会耗损大年夜量资本。假如只必要将数据联接到其他查询,则可以试试应用内嵌视图,以节省资本。

避免 LEFT JOIN 和 NULL

当然,有很多时刻您必要履行 LEFT JOIN 和应用 NULL 值。然则,它们并不适用于所有环境。改变 SQL 查询的构建要领可能会孕育发生将一个花几分钟运行的申报缩短到只花几秒钟这样的天壤之其余效果。无意偶尔,必须在查询中调剂数据的形态,使之适应利用法度榜样所要求的显示要领。虽然 TABLE 数据类型会削减大年夜量占用资本的环境,但在查询中还有许多区域可以进行优化。SQL 的一个有代价的常用功能是 LEFT JOIN。它可以用于检索第一个表中的所有行、第二个表中所有匹配的行、以及第二个表中与第一个表不匹配的所有行。例如,假如盼望返回每个客户及其定单,应用 LEFT JOIN 则可以显示有定单和没有定单的客户。

此对象可能会被过度应用。LEFT JOIN 耗损的资本异常之多,由于它们包孕与 NULL(不存在)数据匹配的数据。在某些环境下,这是弗成避免的,然则价值可能异常高。LEFT JOIN 比 INNER JOIN 耗损资本更多,以是假如您可以从新编写查询以使得该查询不应用任何 LEFT JOIN,则会获得异常可不雅的回报。

加快应用 LEFT JOIN 的查询速率的一项技巧涉及创建一个 TABLE 数据类型,插入第一个表(LEFT JOIN 左侧的表)中的所有行,然后应用第二个表中的值更新 TABLE 数据类型。此技巧是一个两步的历程,但与标准的 LEFT JOIN 比拟,可以节省大年夜量光阴。一个很好的规则是考试测验各类不合的技巧并记录每种技巧所需的光阴,直到得到用于您的利用法度榜样的履行机能最佳的查询。

测试查询的速率时,有需要多次运行此查询,然后取一个匀称值。由于查询(或存储历程)可能会存储在 SQL Server 内存中的历程缓存中,是以第一次考试测验消费的光阴似乎稍长一些,而所有后续考试测验消费的光阴都较短。别的,运行您的查询时,可能正在针对相同的表运行其他查询。当其他查询锁定和解锁这些表时,可能会导致您的查询要排队等待。例如,假如您进行查询时某人正在更新 此表中的数据,则在更新提交时您的查询可能必要消费更长光阴来履行。

避免应用 LEFT JOIN 时速率低落的最简单措施是尽可能多地环抱它们设计数据库。例如,假设某一产品可能具有种别也可能没有种别。假如 Products 表存储了其类其余 ID,而没有用于某个特定产品的种别,则您可以在字段中存储 NULL 值。然后您必须履行 LEFT JOIN 来获取所有产品及其种别。您可以创建一个值为“No Category”的种别,从而指定外键关系不容许 NULL 值。经由过程履行上述操作,现在您就可以应用 INNER JOIN 检索所有产品及其种别了。虽然这看起来似乎是一个带有多余数据的变通措施,但可能是一个很有代价的技巧,由于它可以打消 SQL 批处置惩罚语句中耗损资本较多的 LEFT JOIN。在数据库中整个应用此观点可以为您节省大年夜量的处置惩罚光阴。请记着,对付您的用户而言,纵然几秒钟的光阴也异常紧张,由于当您有许多用户正在造访同一个联机数据库利用法度榜样时,这几秒钟实际上的意义会异常重大年夜。

机动应用笛卡尔乘积

对付此技术,我将进行异常具体的先容,并提倡在某些环境下应用笛卡尔乘积。出于某些缘故原由,笛卡尔乘积 (CROSS JOIN) 遭到了很多非难,开拓职员平日会被警告根本就不要应用它们。在许多环境下,它们耗损的资本太多,从而无法高效应用。然则像 SQL 中的任何对象一样,假如精确应用,它们也会很有代价。例如,假如您想运行一个返回每月数据(纵然某一特定月份客户没有定单也要返回)的查询,您就可以很方便地应用笛卡尔乘积。

虽然这看起来似乎没什么神奇的,然则请斟酌一下,假如您从客户到定单(这些定单按月份进行分组并对贩卖额进行小计)进行了标准的 INNER JOIN,则只会得到客户有定单的月份。是以,对付客户未订购任何产品的月份,您不会得到 0 值。假如您想为每个客户都绘制一个图,以显示每个月和该月贩卖额,则可能盼望此图包括月贩卖额为 0 的月份,以便直不雅标识出这些月份。假如应用 Fig新葡 京娱乐网址ure 2(着末一页) 中的 SQL,数据则会跳过贩卖额为 0 美元的月份,由于在定单表中对付零贩卖额不会包孕任何行(假设您只存储发生的事故)。

Figure 3(着末一页)中的代码虽然较长,然则可以达到获取所有贩卖数据(以致包括没有贩卖额的月份)的目标。首先,它会提取去年所有月份的列表,然后将它们放入第一个 TABLE 数据类型表 (@tblMonths) 中。下一步,此代码会获取在该光阴段内有贩卖额的所有客户公司的名称列表,然后将它们放入另一个 TABLE 数据类型表 (@tblCus-tomers) 中。这两个表存储了创建结果集所必需的所有基础数据,但实际贩卖数量除外。 第一个表中列出了所有月份(12 行),第二个表中列出了这个光阴段内有贩卖额的所有客户(对付我是 81 个)。并非每个客户在以前 12 个月中的每个月都购买了产品,以是,履行 INNER JOIN 或 LEFT JOIN 不会返回每个月的每个客户。这些操作只会返回购买产品的客户和月份。

笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基础上是将第一个表与第二个表相乘,天生一个行聚拢,此中包孕第一个表中的行数与第二个表中的行数相乘的结果。是以,笛卡尔乘积会向表@tblFinal 返回 972 行。着末的步骤是应用此日期范围内每个客户的月贩卖额总计更新 @tblFinal 表,以及选择终极的行集。

假如因为笛卡尔乘积占用的资本可能会很多,而不必要真正的笛卡尔乘积,则可以审慎地应用 CROSS JOIN。例如,假如对产品和种别履行了 CROSS JOIN,然后应用 WHERE 子句、DISTINCT 或 GROUP BY 来筛选出大年夜多半行,那么应用 INNER JOIN 会得到同样的结果,而且效率高得多。假如必要为所有的可能性都返回数据(例如在您盼望应用每月贩卖日期添补一个图表时),则笛卡尔乘积可能会异常有赞助。然则,您不应该将它们用于其他用途,由于在大年夜多半规划中 INNER JOIN 的效率要高得多。

拾遗补零

这里先容其他一些可赞助前进 SQL 查询效率的常用技巧。假设您将按区域对所有贩卖职员进行分组并将他们的贩卖额进行小计,然则您只想要那些数据库中标记为处于活动状态的贩卖职员。您可以按区域对贩卖职员分组,并应用 HAVING 子句打消那些未处于活动状态的贩卖职员,也可以在 WHERE 子句中履行此操作。在 WHERE 子句中履行此操作会削减必要分组的行数,以是比在 HAVING 子句中履行此操作效率更高。HAVING 子句中基于行的前提的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。

另一个前进效率的技术是应用 DISTINCT 关键字查找数据行的零丁报表,来代替应用 GROUP BY 子句。在这种环境下,应用 DISTINCT 关键字的 SQL 效率更高。请在必要谋略聚合函数(SUM、COUNT、MAX 等)的环境下再应用 GROUP BY。别的,假如您的查询老是自己返回一个独一的行,则不要应用 DISTINCT 关键字。在这种环境下,DISTINCT 关键字只会增添系统新葡 京娱乐网址开销。

您已经看到了,有大年夜量技巧都可用于优化查询和实现特定的营业规则,技术便是进行一些考试测验,然后对照它们的机能。最紧张的是要测试、测试、再新葡 京娱乐网址测试。

Figure 2 Returning All Customers and Their Sales

set nocount on

DECLARE @dtStartDate DATETIME,

@dtEndDate DATETIME,

@dtDate DATETIME

SET @dtEndDate = '5/5/1997'

SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1)

AS VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + '

23:59:59' AS DATETIME))

SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)

SELECT CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +

CASE

WHEN MONTH(o.OrderDate)

发表评论
加载中...

相关文章