五种提高 SQL 性能的方法

转载 2007年09月24日 09:23:00

Five Ways to Rev up Your SQL Performance

本文节选自MSDN的文章《五种提高 SQL 性能的方法》,提出如何提高基于SQL Server应用程序的运行效率,非常值得推荐。对一些Traffic很高的应用系统而言,如何提高和改进SQL指令,是非常重要的,也是一个很好的突破点。
*文章主要包括如下一些内容(如感兴趣,请直接访问下面的URL阅读完整的中英文文档):

1, 从 INSERT 返回 IDENTITY 
SELECT @@IDENTITY

2, 内嵌视图与临时表 
临时表 - 在 tempdb 中的临时表会导致查询进行大量 I/O 操作和磁盘访问,临时表会消耗大量资源。
内嵌视图 -使用内嵌视图取代临时表。内嵌视图只是一个可以联接到 FROM 子句中的查询。如果只需要将数据联接到其他查询,则可以试试使用内嵌视图,以节省资源。

3, 避免 LEFT JOIN 和 NULL 
LEFT JOIN 消耗的资源非常之多,因为它们包含与 NULL(不存在)数据匹配的数据。在某些情况下,这是不可避免的,但是代价可能非常高。LEFT JOIN 比 INNER JOIN 消耗资源更多,所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN,则会得到非常可观的回报。

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

4, 灵活使用笛卡尔乘积 
对于此技巧,我将进行非常详细的介绍,并提倡在某些情况下使用笛卡尔乘积。出于某些原因,笛卡尔乘积 (CROSS JOIN) 遭到了很多谴责,开发人员通常会被警告根本就不要使用它们。在许多情况下,它们消耗的资源太多,从而无法高效使用。但是像 SQL 中的任何工具一样,如果正确使用,它们也会很有价值。

其中一段示例代码,值得效仿:
-- 笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基本上是将第一个表与第二个表相乘,生成一个行集合,其中包含第一个表中的行数与第二个表中的行数相乘的结果。因此,笛卡尔乘积会向表 @tblFinal 返回 12(所有月份)*81(所有客户)=972 行。最后的步骤是使用此日期范围内每个客户的月销售额总计更新 @tblFinal 表,以及选择最终的行集。

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



5, 拾遗补零 
这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计,但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组,并使用 HAVING 子句消除那些未处于活动状态的销售人员,也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数,所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。

另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表,来代替使用 GROUP BY 子句。在这种情况下,使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数(SUM、COUNT、MAX 等)的情况下再使用 GROUP BY。另外,如果您的查询总是自己返回一个唯一的行,则不要使用 DISTINCT 关键字。在这种情况下,DISTINCT 关键字只会增加系统开销。
---------------------
中文URL:
http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx
英文URL:
http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/

  

5种提高SQL性能的方法

五种提高 SQL 性能的方法 (http://topic.csdn.net/u/20080509/10/c9bee345-3f57-4632-a84d-1673b1e96bf8.html) 有...
  • qq_405930170
  • qq_405930170
  • 2015年04月16日 11:35
  • 1928

五种提高 SQL 性能的方法

有时, 为了让应用程序运行得更快,所做的全部工作就是在这里或那里做一些很小调整。啊,但关键在于确定如何进行调整!迟早您会遇到这种情况:应用程序中的 SQL 查询不能按照您想要的方式进行响应。它要么不返...
  • qq719365064
  • qq719365064
  • 2016年10月25日 17:41
  • 48

第五章 优化程序性能

写程序的最主要目标就是使它在所有可能的情况下都正确工作。 程序员必须写出“清晰简洁”的代码,读懂、理解、修改   代码。        编写高效程序: 1.          选择合适的算法和数据结构...
  • yvhqbat
  • yvhqbat
  • 2015年10月11日 11:45
  • 649

提高MySQL效率与性能的技巧

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据...
  • cdl123456
  • cdl123456
  • 2014年07月18日 17:08
  • 2566

如何提高数据库性能

一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的,它是随着用户量的增加,基础架构才逐渐完善。这篇博文主要谈MySQL数据库发展周期中所面临的问题及优化方案,暂且抛开前端应用不说,大致分...
  • wulove52
  • wulove52
  • 2016年09月06日 21:56
  • 2780

如何提高数据库访问性能

特别说明: 1、  本文只是面对数据库应用开发的程序员,不适合专业DBA,DBA在数据库性能优化方面需要了解更多的知识; 2、  本文许多示例及概念是基于Oracle数据库描述,对于其它关...
  • u010327174
  • u010327174
  • 2014年10月16日 16:35
  • 1759

Hibernate提高性能的方法总结

Hibernate 提高性能的方法:(未完) 一、缓存机制:                缓存是介于物理数据源与应用程序之间,是对数据库中的数据复制一份临时放在内存中的容器,其作用是为了减少应用...
  • u014078192
  • u014078192
  • 2014年03月26日 00:35
  • 1011

css优化、提高性能的方法

关注一下CSS Lint (CSS Lint),这是一个发现CSS书写问题,提升性能的工具 我复制规则过来: 修复解析错误(Parsing errors should be fixed) 避...
  • github_35549695
  • github_35549695
  • 2016年09月22日 16:28
  • 1646

前端优化:九个技巧,提高Web性能

当今数字世界,存在着无数的网站,每天都需要处理各种不同的原因的访问。然而,这些网站中有很大一部分显得笨重,使用起来也很麻烦。没怎么优化的网站会被各种各样的问题困扰,包括加载时间、不支持移动设备、浏览器...
  • VermouthDream
  • VermouthDream
  • 2017年02月11日 21:37
  • 1514

提升Java性能的基本方法

Java从诞生之日起就被质疑:字节码在JVM中运行是否会比机器码直接运行的效率会 低很多?很多技术高手、权威网站都有类似的测试和争论,从而来表明Java比C (或C++) 更快或效率相同。此类话题我们...
  • lexang1
  • lexang1
  • 2015年11月18日 23:52
  • 720
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章: 五种提高 SQL 性能的方法
举报原因:
原因补充:

(最多只允许输入30个字)