厦门速达软件-厦门商启信息服务有限公司|厦门速达软件|速达软件经销商-速达-网站制作-财务软件-进销存软件-ERP-厦门速达软件SISP服务商-速达软件-seo优化-技术支持

速达软件

进步SQL Server性能的几种

发布日期：2009年07月31日浏览次数：作者：佚名

偶然，为了让应用程序运行得更快，所做的悉数工作就是在这里或那里做一些很小调整。但关键在于确定如何进行调整!迟早您会碰到这种情况：应用程序中的 SQL 查询不能按照您想要的体例进行相应。它要么不返回数据，要么耗费的时间长得出奇。假如它降低了企业应用程序的速度，用户必须等待很长时间。用户盼望应用程序相应敏捷，他们的报告能够在瞬间之内返回分析数据。就我本身而言，假如在Web上冲浪时某个页面要耗费十多秒才能加载，我也会很不耐烦。 8.lc4B:?€.@
GHzPE(77+nBz
　　为了解决这些题目，紧张的是找到题目的根源。那么，从哪里开始呢?根本缘故原由通常在于数据库设计和访问它的查询。我将讲述四项技术，这些技术可用于进步基于SQL Server的应用程序的性能或改善其可伸缩性。我将细心说明 LEFT JOIN、CROSS JOIN 的使用以及IDENTITY 值的检索。请记住，根本没有神奇的解决方案。调整您的数据库及其查询必要占用时间、进行分析，还必要大量的测试。这些技术都已被证实行之有用，但对您的应用程序而言，可能其中一些技术比另一些技术更适用。 G,,X(45}!6q^Ph 4/
AZ[.iv (k=kg|?3
　　从 INSERT 返回 IDENTITY qnkOk6wSRs&cddi;
v(~p8-GO.Pjfm` ?
　　我决定从碰到很多题目的内容入手：如何在实行SQL INSERT后检索IDENTITY值。通常，题目不在于如何编写检索值的查询，而在于在哪里以及何时进行检索。在SQL Server中，下面的语句可用于检索由最新在运动数据库连接上运行的 SQL 语句所创建的 IDENTITY 值： \= ~^dB\hP(ExW M`Eo
?b]q|€z/3y*sH`+
　　SELECT @@IDENTITY #xt`EN^HrD!O
]Q|bt8YN#^ttdEa
9Ty"aoY|*4Q\J*G
　　这个 SQL 语句并不复杂，但必要记住的一点是：假如这个最新的 SQL 语句不是 INSERT，或者您针对非 INSERT SQL 的其他连接运行了此 SQL，则不会获得期望的值。您必须运行下列代码才能检索紧跟在 INSERT SQL 之后且位于统一连接上的 IDENTITY，如下所示： h_-DX: :~H0€
bJLMliBpHsN€ 9=
　　INSERT INTO Products (ProductName) VALUES ('Chalk') #f='X#]Zv%j"~d=)2
　　SELECT @@IDENTITY 2Kn]` O,*-E9e^xN
:*W}k€d)(~5K.wd
　　在一个连接上针对 Northwind 数据库运行这些查询将返回一个名称为 Chalk 的新产品的 IDENTITY 值。所以，在使用ADOVisual Basic应用程序中，可以运行以下语句： b?Qq 3r zoyZb1
E9PAHB^C`7a-cl2#=

Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _
　　(ProductName) VALUES ('Chalk');SELECT @@IDENTITY")
　　lProductID = oRs(0)

\X%Q . #;_M.b5k
Av%W&B*5b&SX@XuiK#
　　yRIyr-Ewi67S=
`qL-=lf#(=k4/-
此代码告诉 SQL Server 不要返回查询的行计数，然后实行 INSERT 语句，并返回刚刚为这个新行创建的 IDENTITY 值。SET NOCOUNT ON 语句透露表现返回的记录集有一行和一列，其中包含了这个新的 IDENTITY 值。假如没有此语句，则会首先返回一个空的记录集(由于 INSERT 语句不返回任何数据)，然后会返回第二个记录集，第二个记录集中包含 IDENTITY 值。这可能有些令人疑心，尤其是由于您从来就没有盼望过 INSERT 会返回记录集。之所以会发生此情况，是由于 SQL Server 看到了这个行计数(即一行受到影响)并将其诠释为透露表现一个记录集。因此，真正的数据被推回到了第二个记录集。当然您可以使用 ADO 中的 NextRecordset 方法获取此第二个记录集，但假如总能够首先返回该记录集且只返回该记录集，则会更方便，也更有用率。 r}4=Pg!0lI1'd$
nhM z#1H6il€
　　此方法虽然有用，但必要在 SQL 语句中额外添加一些代码。获得雷同效果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 语句，并将 SELECT @@IDENTITY 语句放在表中的 FOR INSERT 触发器中，如下面的代码片段所示。如许，任何进入该表的 INSERT 语句都将主动返回 IDENTITY 值。 AP!8ZrGD+TX [=
o8a nR5%jfJ;Y^JK
　　CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS O*|\k()=WM=3VNX!
　　SELECT @@IDENTITY p{ i'Z5!c#DJM-ylK
　　GO [(Y'v$Tgm6b
kx*?Zy\+,P )A l
ASsvt~ H]AfXVyr6VK'
　　触发器只在 Products 表上发生 INSERT 时启动，所以它总是会在成功 INSERT 之后返回一个 IDENTITY。使用此技术，您可以始终以雷同的体例在应用程序中检索 IDENTITY 值。 ?spLr+xB*gSa@zr
9l*$OM+YOhBxdnl

内嵌视图与一时表 G47&l|c?tLGzNiZ

　　某些时候，查询必要将数据与其他一些可能只能通过实行 GROUP BY 然后实行标准查询才能收集的数据进行联接。例如，假如要查询最新五个定单的有关信息，您首先必要知道是哪些定单。这可以使用返回定单 ID 的 SQL 查询来检索。此数据就会存储在一时表(这是一个常用技术)中，然后与 Products 表进行联接，以返回这些定单售出的产品数量：y@fiRY8cKHl[Yxfs

　　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 ORDER 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 和磁盘访问，而可以使用内嵌视图得到同样的效果：cLWE;wbXI€#8xU

　　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

　　此查询不仅比前面的查询服从更高，而且长度更短。一时表会消费大量资源。假如只必要将数据联接到其他查询，则可以试试使用内嵌视图，以节省资源。5&1j,;FVS}UB1tT/FF

　　避免 LEFT JOIN 和 NULLQv)l4)Z+?G^$g:e.

　　当然，有许多时候您必要实行 LEFT JOIN 和使用 NULL 值。但是，它们并不适用于所有情况。改变 SQL 查询的构建体例可能会产生将一个花几分钟运行的报告缩短到只花几秒钟如许的天地之别的结果。偶然，必须在查询中调整数据的形态，使之适应应用程序所要求的表现体例。虽然 TABLE 数据类型会削减大量占用资源的情况，但在查询中还有很多区域可以进行优化。SQL 的一个有价值的常勤奋能是 LEFT JOIN。它可以用于检索第一个表中的所有行、第二个表中所有匹配的行、以及第二个表中与第一个表不匹配的所有行。例如，假如盼望返回每个客户及其定单，使用 LEFT JOIN 则可以表现有定单和没有定单的客户。DMe=2[UD3(S96m+UU

　　此工具可能会被过度使用。LEFT JOIN 消费的资源特别很是之多，由于它们包含与 NULL(不存在)数据匹配的数据。在某些情况下，这是不可避免的，但是代价可能特别很是高。LEFT JOIN 比 INNER JOIN 消费资源更多，所以假如您可以重新编写查询以使得该查询不使用任何 LEFT JOIN，则会得到特别很是可观的回报。0`l`9%2l7!a`F ?

　　加快使用 LEFT JOIN 的查询速度的一项技术涉及创建一个 TABLE 数据类型，插入第一个表(LEFT JOIN 左侧的表)中的所有行，然后使用第二个表中的值更新 TABLE 数据类型。此技术是一个两步的过程，但与标准的 LEFT JOIN 相比，可以节省大量时间。一个很好的规则是尝试各种不同的技术并记录每种技术所需的时间，直到获得用于您的应用程序的实行性能最佳的查询。3]^r62M4^d4€

测试查询的速度时，有需要多次运行此查询，然后取一个平均值。由于查询(或存储过程)可能会存储在 SQL Server 内存中的过程缓存中，因此第一次尝试耗费的时间彷佛稍长一些，而所有后续尝试耗费的时间都较短。另外，运行您的查询时，可能正在针对雷同的表运行其他查询。当其他查询锁定息争锁这些表时，可能会导致您的查询要排队等待。例如，假如您进行查询时某人正在更新此表中的数据，则在更新提交时您的查询可能必要耗费更长时间来实行。

　　避免使用 LEFT JOIN 时速度降低的最简单方法是尽可能多地围绕它们设计数据库。例如，假设某一产品可能具有类别也可能没有类别。假如 Products 表存储了其类别的 ID，而没有效于某个特定产品的类别，则您可以在字段中存储 NULL 值。然后您必须实行 LEFT JOIN 来获取所有产品及其类别。您可以创建一个值为“No Category”的类别，从而指定外键关系不许可 NULL 值。通过实行上述操作，如今您就可以使用 INNER JOIN 检索所有产品及其类别了。虽然这看起来彷佛是一个带有多余数据的变通方法，但可能是一个很有价值的技术，由于它可以消弭 SQL 批处理语句中消费资源较多的 LEFT JOIN。在数据库中悉数使用此概念可以为您节省大量的处理时间。请记住，对于您的用户而言，即使几秒钟的时间也特别很是紧张，由于当您有很多用户正在访问统一个联机数据库应用程序时，这几秒钟现实上的意义会特别很是庞大。tiq]cln/(&& 0c!$~z

　　天真使用笛卡尔乘积:Y{Rl42myF{,{cj

　　对于此技巧，我将进行特别很是细致的介绍，并提倡在某些情况下使用笛卡尔乘积。出于某些缘故原由，笛卡尔乘积 (CROSS JOIN) 遭到了许多训斥，开发人员通常会被警告根本就不要使用它们。在很多情况下，它们消费的资源太多，从而无法高效使用。但是像 SQL 中的任何工具一样，假如精确使用，它们也会很有价值。例如，假如您想运行一个返回每月数据(即使某一特定月份客户没有定单也要返回)的查询，您就可以很方便地使用笛卡尔乘积。6$|Ai}]S%/L1|6 |wJ

　　虽然这看起来彷佛没什么神奇的，但是请考虑一下，假如您从客户到定单(这些定单按月份进行分组并对贩卖额进行小计)进行了标准的 INNER JOIN，则只会获得客户有定单的月份。因此，对于客户未订购任何产品的月份，您不会获得 0 值。假如您想为每个客户都绘制一个图，以表现每个月和该月贩卖额，则可能盼望此图包括月贩卖额为 0 的月份，以便直观标识出这些月份。假如使用 Figure 2(最后一页) 中的 SQL，数据则会跳过贩卖额为 0 美元的月份，由于在定单表中对于零贩卖额不会包含任何行(假设您只存储发生的事件)。`h 6Vt7Q3tX`5QF

　　Figure 3(最后一页)中的代码虽然较长，但是可以达到获取所有贩卖数据(甚至包括没有贩卖额的月份)的目标。首先，它会提取去年所有月份的列表，然后将它们放入第一个 TABLE 数据类型表 (@tblMonths) 中。下一步，此代码会获取在该时间段内有贩卖额的所有客户公司的名称列表，然后将它们放入另一个 TABLE 数据类型表 (@tblCus-tomers) 中。这两个表存储了创建效果集所必需的所有基本数据，但现实贩卖数量除外。第一个表中列出了所有月份(12 行)，第二个表中列出了这个时间段内有贩卖额的所有客户(对于我是 81 个)。并非每个客户在曩昔 12 个月中的每个月都购买了产品，所以，实行 INNER JOIN 或 LEFT JOIN 不会返回每个月的每个客户。这些操作只会返回购买产品的客户和月份。~^XTx9mi?Pa{wT

　　笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基本上是将第一个表与第二个表相乘，生成一个行荟萃，其中包含第一个表中的行数与第二个表中的行数相乘的效果。因此，笛卡尔乘积会向表 @tblFinal 返回 972 行。最后的步骤是使用此日期范围内每个客户的月贩卖额总计更新 @tblFinal 表，以及选择最终的行集。Azw;ePC)s XU~^

　　假如因为笛卡尔乘积占用的资源可能会许多，而不必要真正的笛卡尔乘积，则可以郑重地使用 CROSS JOIN。例如，假如对产品和类别实行了 CROSS JOIN，然后使用 WHERE 子句、DISTINCT 或 GROUP BY 来筛选出大多数行，那么使用 INNER JOIN 会获得同样的效果，而且服从高得多。假如必要为所有的可能性都返回数据(例如在您盼望使用每月贩卖日期添补一个图表时)，则笛卡尔乘积可能会特别很是有帮助。但是，您不应该将它们用于其他用途，由于在大多数方案中 INNER JOIN 的服从要高得多。 #j5bi')Vk\$!d

　　拾遗补零H -bpj')QI%t|^

　　这里介绍其他一些可帮助进步 SQL 查询服从的常用技术。假设您将按区域对所有贩卖人员进行分组并将他们的贩卖额进行小计，但是您只想要那些数据库中标记为处于运动状况的贩卖人员。您可以按区域对贩卖人员分组，并使用 HAVING 子句消弭那些未处于运动状况的贩卖人员，也可以在 WHERE 子句中实行此操作。在 WHERE 子句中实行此操作会削减必要分组的行数，所以比在 HAVING 子句中实行此操作服从更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。*ce(}?rGu{oUWQVAla

　　另一个进步服从的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句。在这种情况下，使用 DISTINCT 关键字的 SQL 服从更高。请在必要计算聚合函数(SUM、COUNT、MAX 等)的情况下再使用 GROUP BY。另外，假如您的查询总是本身返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增长体系开销。t\\t;-Wr*O"kBHN

您已经看到了，有大量技术都可用于优化查询和实现特定的营业规则，技巧就是进行一些尝试，然后比较它们的性能。最紧张的是要测试、测试、再测试。 M €pDiA?nC oB:qU

　　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) < 10
　　THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
　　ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))
　　END AS sMonth,
　　c.CustomerID,
　　c.CompanyName,
　　c.ContactName,
　　SUM(od.Quantity * od.UnitPrice) AS mSales
　　FROM Customers c
　　INNER JOIN Orders o ON c.CustomerID = o.CustomerID
　　INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
　　WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate
　　GROUP BY
　　CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +
　　CASE
　　WHEN MONTH(o.OrderDate) < 10
　　THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
　　ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))
　　END,
　　c.CustomerID,
　　c.CompanyName,
　　c.ContactName
　　ORDER BY
　　c.CompanyName,
　　sMonth
　　Figure 3 Cartesian Product at Work
　　DECLARE @tblMonths TABLE (sMonth VARCHAR(7))
　　DECLARE @tblCustomers TABLE ( CustomerID CHAR(10),
　　CompanyName VARCHAR(50),
　　ContactName VARCHAR(50))
　　DECLARE @tblFinal TABLE ( sMonth VARCHAR(7),
　　CustomerID CHAR(10),
　　CompanyName VARCHAR(50),
　　ContactName VARCHAR(50),
　　mSales MONEY)
　　DECLARE @dtStartDate DATETIME,
　　@dtEndDate DATETIME,
　　@dtDate DATETIME,
　　@i INTEGER
　　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)
　　— Get all months into the first table
　　SET @i = 0
　　WHILE (@i < 12)
　　BEGIN
　　SET @dtDate = DATEADD(mm, -1 * @i, @dtEndDate)
　　INSERT INTO @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR(4)) + '-' +
　　CASE
　　WHEN MONTH(@dtDate) < 10
　　THEN '0' + CAST(MONTH(@dtDate) AS VARCHAR(2))
　　ELSE CAST(MONTH(@dtDate) AS VARCHAR(2))
　　END AS sMonth
　　SET @i = @i + 1
　　END
　　— Get all clients who had sales during that period into the "y" table
　　INSERT INTO @tblCustomers
　　SELECT DISTINCT
　　c.CustomerID,
　　c.CompanyName,
　　c.ContactName
　　FROM Customers c
　　INNER JOIN Orders o ON c.CustomerID = o.CustomerID
　　WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate
　　INSERT INTO @tblFinal
　　SELECT m.sMonth,
　　c.CustomerID,
　　c.CompanyName,
　　c.ContactName,
　　0
　　FROM @tblMonths m CROSS JOIN @tblCustomers c
　　UPDATE @tblFinal SET
　　mSales = mydata.mSales
　　FROM @tblFinal f INNER JOIN
　　(
　　SELECT c.CustomerID,
　　CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +
　　CASE WHEN MONTH(o.OrderDate) < 10
　　THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
　　ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))
　　END AS sMonth,
　　SUM(od.Quantity * od.UnitPrice) AS mSales
　　FROM Customers c
　　INNER JOIN Orders o ON c.CustomerID = o.CustomerID
　　INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
　　WHERE o.OrderDate BETWEEN @dtStartDate AND @dtEndDate
　　GROUP BY
　　c.CustomerID,
　　CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +
　　CASE WHEN MONTH(o.OrderDate) < 10
　　THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
　　ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))
　　END
　　) mydata on f.CustomerID = mydata.CustomerID AND f.sMonth =
　　mydata.sMonth
　　SELECT f.sMonth,
　　f.CustomerID,
　　f.CompanyName,
　　f.ContactName,
　　f.mSales
　　FROM @tblFinal f
　　ORDER BY
　　f.CompanyName,
　　f.sMonth

[版权归原作者及coolcxm共同拥有，转载请注明出处]


上一篇：速达软件中操作员密码丢失怎么办	下一篇：SQL Server死锁产生缘故原由及解决办法