`
wyf
  • 浏览: 438037 次
  • 性别: Icon_minigender_1
  • 来自: 唐山
社区版块
存档分类
最新评论

提高sql性能

    博客分类:
  • SQL
 
阅读更多

从 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 值。所以,在使用 ADO 的 Visual 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 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 和磁盘访问,而可以使用内嵌视图得到同样的结果:

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,则会得到非常可观的回报(请参阅图 1 中的图)。

图 1 查询

加快使用 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 中的任何工具一样,如果正确使用,它们也会很有价值。例如,如果您想运行一个返回每月数据(即使某一特定月份客户没有定单也要返回)的查询,您就可以很方便地使用笛卡尔乘积。 图 2 中的 SQL 就执行了上述操作。

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

图 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 关键字只会增加系统开销。

分享到:
评论

相关推荐

    如何提高sql性能

    ### 如何提高SQL性能 #### 一、理解Oracle体系结构的重要性 在探讨如何提高SQL性能之前,我们首先需要深入理解Oracle数据库的体系结构。Oracle数据库的体系结构是其高效运行的基础,也是进行性能优化的关键所在。...

    五种提高 SQL 性能的方法

    ### 五种提高 SQL 性能的方法 在 IT 领域中,SQL(Structured Query Language)作为数据库管理和查询的标准语言,在数据支持和技术开发中扮演着至关重要的角色。随着业务需求的增长和技术的进步,如何优化 SQL 查询...

    五种提高SQL性能的方法

    【提高SQL性能的方法】 在数据库开发中,优化SQL性能至关重要,因为它直接影响到应用程序的响应速度和用户体验。这里介绍五种有效提升SQL性能的方法,这些方法是基于实际开发经验总结得出的。 1. **从INSERT返回...

    五种提高SQL性能的方法.doc

    【提高SQL性能】 在数据库管理系统中,SQL性能优化对于提升应用程序的整体效率至关重要。当SQL查询执行缓慢,导致用户等待时间过长时,就需要采取措施进行优化。以下五种方法可以帮助提升SQL性能: 1. **从INSERT...

    \提高 SQL 性能的方法

    **描述**: 当涉及到自增 ID 或者 IDENTITY 列时,正确的插入方式对于提高 SQL 性能非常重要。例如,当插入含有 IDENTITY 列的数据时,正确地处理 IDENTITY 值可以避免不必要的查询。 **建议**: - **利用 SQL Server...

    oracle_sql性能优化 文档

    建立和有效利用索引是提高SQL性能的关键,特别是对于频繁查询的列,创建索引可以显著提升查询效率。 3. **共享SQL语句** ORACLE支持SQL语句的共享,解析后的语句存储在SGA的共享池中,供所有用户共享,避免重复...

    DB2 SQL性能调优秘笈

    索引对于提高SQL查询性能至关重要。合理设计和维护索引可以显著加快查询速度。以下是一些实用的索引策略: 1. **复合索引选择**:当多个列经常被一起用作查询条件时,创建一个包含这些列的复合索引可以显著提高查询...

    ORACLE SQL性能优化系列

    ORACLE SQL性能优化系列 ORACLE SQL性能优化是数据库管理员和开发者非常关心的一个话题。为了提高数据库的性能,ORACLE 提供了多种优化技术。下面我们将详细介绍 ORACLE SQL 性能优化系列中的一些重要知识点。 一...

    ORACLE SQL性能优化

    在Oracle数据库环境中,SQL性能优化是一项至关重要的任务,它直接影响到系统的响应速度和整体效率。...对于开发者和DBA来说,深入理解Oracle的工作原理,不断学习和实践,是提高SQL性能优化能力的不二法门。

    ORACLE_SQL性能优化(全).ppt

    9. **SQL语句优化技巧**:避免全表扫描,合理使用JOIN操作,避免在WHERE子句中使用NOT、IN等操作,以及使用子查询的优化等都是提高SQL性能的有效手段。 10. **存储优化**:选择合适的表空间、数据文件、段、块大小...

    Sql Server——Sql性能优化

    Sql Server——Sql性能优化Sql Server——Sql性能优化Sql Server——Sql性能优化Sql Server——Sql性能优化Sql Server——Sql性能优化Sql Server——Sql性能优化Sql Server——Sql性能优化Sql Server——Sql性能优化

    查询数据库SQL语句及性能

    此外,数据库调优是提高SQL性能的重要手段。这包括调整数据库参数,如缓冲区大小、并发连接数等,优化查询缓存,以及定期重构和分析索引。同时,数据库设计也至关重要,良好的数据库设计能显著减少查询复杂性和执行...

Global site tag (gtag.js) - Google Analytics