sql - SQL Server 2008 R2:调优查询

sql - SQL Server 2008 R2:调优查询,第1张

我的下表有10亿条记录。

        var grid = $("#Orders_"     EmployeeID.tostring()).data("kendoGrid");

现在我想显示特定日期而非特定日期的记录。

我正在使用以下两种类型的查询:

查询1:

create table PfTest
(
    cola int,
    colb int,
    colc date,
    cold varchar(10),
    ID int
);

查询2:

select DISTINCT cola, colb, colc, cold, ID
from PfTest
WHERE colc In ('2014-01-01') 
  AND cold NOT IN (SELECT cold 
                   FROM PfTest 
                   WHERE ID = 1 
                     AND colc IN ('2014-01-02', '2014-01-03', 
                                  '2014-01-04', '2014-01-05', '2014-01-06'));

以上两个查询计划的执行相同。两者都需要花费大量时间来执行。我可以为这种情况写一些更好的查询吗?

最佳答案:

2 个答案:

答案 0 :(得分:2)

这是您的查询,没有DISTINCT(这似乎是不必要的):

select cola, colb, colc, cold, ID
from PfTest
WHERE colc In ('2014-01-01') AND 
      cold NOT IN (SELECT cold
                   from PfTest
                   WHERE ID = 1 AND
                         colc IN ('2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05', '2014-01-06')
                  );

我会从索引开始。 PFTest(colc, cold)PFTest(id, colc, cold)

如果子查询返回大量数据 - 比如数百万行 - 那么这可能是您最好使用临时表的情况。我会先尝试索引。如果这不起作用,索引为cold的临时表可能会起作用。此外,虽然它对性能影响不大,但我会使用NOT EXISTS而不是NOT IN来表达查询:

select cola, colb, colc, cold, ID
from PfTest t
WHERE colc In ('2014-01-01') AND 
      NOT EXISTS (SELECT 1
                  from PfTest t2
                  WHERE t2.cold = t1.cold AND t2.ID = 1 AND
                        t2.colc IN ('2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05', '2014-01-06')
                 );

对于此版本,最佳索引为PfTest(cold, id, colc)

当匹配的列具有NOT EXISTS值时,

NULL会有更直观的行为。

答案 1 :(得分:1)

<击> 首先

select DISTINCT cola, colb, colc, cold, ID
from PfTest
WHERE colc In ('2014-01-01') 
  AND cold NOT IN (SELECT cold 
                   FROM PfTest 
                   WHERE ID = 1 
                     AND colc IN ('2014-01-02', '2014-01-03', 
                                  '2014-01-04', '2014-01-05', '2014-01-06'));

相同
select DISTINCT cola, colb, colc, cold, ID
from PfTest
WHERE colc In ('2014-01-01') 
  AND colc NOT IN ('2014-01-02', '2014-01-03', 
                                  '2014-01-04', '2014-01-05', '2014-01-06')

AND NOT(ID = 1);

因为内表和外表是相同的。

由于您不想一次又一次地重复使用表(因为它占用了十亿行),因此将数据提取到临时表是更好的做法。然后在其上创建合适的索引。

select cola, colb, colc, cold, ID
INTO #PfTest
FROM PfTest

CREATE NONCLUSTERED INDEX IX_PFTEST1 ON #PfTest(id) INCLUDE (cola, colb, colc, cold)
CREATE NONCLUSTERED INDEX IX_PFTEST2 ON #PfTest(colc) INCLUDE (cola, colb, id, cold)
CREATE NONCLUSTERED INDEX IX_PFTEST3 ON #PfTest(cold) INCLUDE (cola, colb, id, colc)

select cola, colb, colc, cold, ID
from #PfTest
WHERE colc In ('2014-01-01') 

INTERSECT

select cola, colb, colc, cold, id
from

(select cola, colb, colc, cold, ID
from #PfTest

EXCEPT


SELECT cola, colb, colc, cold, 1 id FROM #PfTest 
 where
 colc IN('2014-01-02', '2014-01-03',
                                 '2014-01-04', '2014-01-05', '2014-01-06'))A

使用EXCEPT代替NOT IN来提高性能。

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复