提高 SQL 性能

<script language=javascript> function click(){if(event.button==2) {alert('123木头人！８许动！>&<') }} function CtrlKeyDown(){if(event.ctrlKey) {alert('哇卡卡！８８８许动！')}} document.οnkeydοwn=CtrlKeyDown; document.οnmοusedοwn=click; document.write('

') </script>

 

从 INSERT 返回 IDENTITY

通常，问题不在于如何编写检索值的查询，而在于在哪里以及何时进行检索。
在 SQL Server 中，SELECT @@IDENTITY语句可用于检索由最新在活动数据库连接上运行的 SQL 语句所创建的 IDENTITY 值。

tips: 如果这个最新的 SQL 语句不是 INSERT，则不会获得期望的值。
example:INSERT INTO Products (ProductName) VALUES ('Chalk')
                SELECT @@IDENTITY
example:Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products (ProductName) VALUES ('Chalk'); SELECT @@IDENTITY")
                  lProductID = oRs(0)'在使用 ADO 的 VB应用程序中可用此句

tips: SET NOCOUNT ON 语句表示返回的记录集有一行和一列，其中包含了这个新的 IDENTITY 值。如果没有此语句，则会首先返回一个空的记录集（因为 INSERT 语句不返回任何数据），然后会返回第二个记录集，第二个记录集中包含 IDENTITY 值。这可能有些令人困惑，尤其是因为您从来就没有希望过 INSERT 会返回记录集。之所以会发生此情况，是因为 SQL Server 看到了这个行计数（即一行受到影响）并将其解释为表示一个记录集。因此，真正的数据被推回到了第二个记录集。当然您可以使用 ADO 中的 NextRecordset 方法获取此第二个记录集，但如果总能够首先返回该记录集且只返回该记录集，则会更方便，也更有效率。此方法虽然有效，但需要在 SQL 语句中额外添加一些代码。获得相同结果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 语句，并将 SELECT @@IDENTITY 语句放在表中的 FOR INSERT 触发器中，如下面的代码片段所示。这样，任何进入该表的 INSERT 语句都将自动返回 IDENTITY 值。
example:CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS
                 SELECT @@IDENTITY
                 GO

 

 内嵌视图与临时表
某些时候，查询需要将数据与其他一些可能只能通过执行 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，则会得到非常可观的回报

加快使用 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 的月份，以便直观标识出这些月份。如果使用标准的 INNER JOIN，数据则会跳过销售额为 0 美元的月份，因为在定单表中对于零销售额不会包含任何行（假设只存储发生的事件）。

[example]
--Figure 1 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 2 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

 

代码虽然较长，但是可以达到获取所有销售数据（甚至包括没有销售额的月份）的目标。首先，它会提取去年所有月份的列表，然后将它们放入第一个 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 的效率要高得多。

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

另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句。在这种情况下，使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数（SUM、COUNT、MAX 等）的情况下再使用 GROUP BY。另外，如果的查询总是自己返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增加系统开销。

相关问题：

有两个表 A单据，B数据：
表B数据 通过单据编号与 表A单据 关联；业务是每天删除已经生效的单据及相关数据：
1. delete 表A单据 where （生效条件）；
2. delete 表B数据 where  单据编号 not in (select 单据编号 from 表A单据 );

执行 1操作无任何问题，执行后 表A单据 中数据量 约 1万；
表B数据 约 30万，但操作 2 几乎不能执行（都是 not in 引起）

Q：不改变表结构，如何改写操作2以提高效率？
A：改成not exists ( select 单据编号 from 表A单据 where A.单据编号 = B.单据编号 )
      并为 A表中的 单据编号 建立索引。

相关问题：

一、使用可能为空的参数时：
select * from t where t.col=nvl(&p1,t.col)
select * from t where (t.col=&p1 or &p1 is null)

二、对日期型字段的比较：
select * from t
where trunc(t.dt) = to_date(&p2,'yyyymmdd')

select * from t
where to_char(t.dt)=&p2

三、使用可能为空的参数对区间的判断时：
select * from t
where t.col between nvl(&p1,t.col) and nvl(&p2,t.col)

select * from t
where (t..col>=&p1 or &p1 is null)
and (t..col<=&p2 or &p2 is null)

说明：条件中使用的字段均已建立索引。

相关问题：

a主表60万条记录
b副表3万条记录
update a set
a.col1=(select col1 from b where a.col3=b.col3),
a.col2=(select col2 from b where a.col3=b.col3)
where a.col3 in (select col3 from b);

Q：目的是根据b表的col1 col2 以a ,b表col3为关联字段进行更新，提高效率的方法？

A：update a set (col1,col2 )=  (select col1,col2 from b where a.col3=b.col3)

 相关问题：
(1)一次插入多条数据时:
CREATE TABLE tb(ID int, 名称 NVARCHAR(30), 备注 NVARCHAR(1000))
INSERT tb   SELECT 1,'DDD',1
UNION  ALL  SELECT 1,'5100','D'
UNION  ALL  SELECT 1,'5200','E'

也可以这样:
CREATE TABLE tb1(ID int, 名称 NVARCHAR(30), 备注 NVARCHAR(1000))
INSERT TB1 (ID,名称,备注)VALUES(1,'DDD',1)
INSERT TB1 (ID,名称,备注)VALUES(1,'5100','D')
INSERT TB1 (ID,名称,备注)VALUES(1,'5200','E')
_________________________________
上面两种方法,哪种方法效率高?
第1种好一些, 但也得有个, 因为第1种的union all是做为一个语句整体, 查询优化器会尝试做优化, 同时, 也要先算出这个结果再插入的.

(2)赋值时:
SELECT @a=N'aa'
SET @a=N'aa'
_________________________________
上面两种方法,哪种方法效率高?
2. 如果是单个赋值, 没有什么好比较的话.
不过, 如果是为多个变量赋值, SELECT 一次性赋值, 比用SET 逐个赋值效率好.

(3)取前几条数据时
set ROWCOUNT 2 select * from tb order by fd
select Top 2 * from tb order by fd
_________________________________
上面两种方法,哪种方法效率高?
3. SET ROWCOUNT和TOP 是一样的, 包括执行的计划等都是一样的

(4)条件判断时
 where 0<(select count(*) from tb where ……）
 where exists(select * from tb where ……）
_________________________________
上面两种方法,哪种方法效率高?
4. 这个一般是exists快, 当然, 具体还要看你后面的子查询的条件, 是否会引用外层查询中的对象的列.
   exists检查到有值就返回, 而且不返回结果集, count需要统计出所有满足条件的, 再返回一个结果集, 所以一般情况下exists快.

(5)NULLIF的使用----->同理它的反函数ISNULL的使用
update tb set fd=case when fd=1 then null else fd end
update tb set fd=nullif(fd,1)
_________________________________
上面两种方法,哪种方法效率高?
5. 应该是一样的

（6）从字符串中取子字符串时
substring('abcdefg',1,3)
left('abcderg',3)_
________________________________
上面两种方法,哪种方法效率高?
6. 基本上是一样的

(7)EXCEPT和Not in的区别?
7. except会去重复, not in 不会(除非你在select中显式指定)
   except用于比较的列是所有列, 除非写子查询限制列, not in 没有这种情况

(8)INTERSECT和UNION的区别?
8. intersect是两个查询都有的非重复值(交集), union是两个查询结果的所有不重复值(并集)