SQL查询选择优化

Question

我正在使用ms sqlserver 2005。我有一个查询，需要根据日期过滤。假设我有一个包含电话号码和日期的表。我需要提供一个时间框架(开始日期和结束日期)的电话号码计数数。如果这些电话号码出现在过去，就不应该出现在结果计数中。我正在做这样的事情:

select (phoneNumber) from someTbl
where phoneNumber not in (select phoneNumber from someTbl where date<@startDate)

对我来说，这看起来根本没有效率(而且它花费了太多时间来执行，导致一些副作用，可能应该在另一个问题中提出)我有大约300K行在sometable应该检查。

在我做了这个检查之后，我需要再检查一件事。我有一个过去的数据库，其中包含另外3万的电话号码。所以我添加了

and phoneNumber not in (select pastPhoneNumber from somePastTbl)

这真的是压垮骆驼的最后一根稻草或者任何你用来解释致命状态的短语

所以我正在寻找一个更好的方法来执行这两个动作。

我选择了Alexander的解决方案，最终得到了这样的查询:

SELECT t.number
FROM tbl t
WHERE t.Date > @startDate
--this is a filter for different customers
AND t.userId in (
                    SELECT UserId
                    FROM Customer INNER JOIN UserToCustomer ON Customer.customerId = UserToCustomer.CustomerId 
                    Where customerName = @customer
                )
--this is the filter for past number
AND NOT EXISTS (                          
                  SELECT 1
                  FROM pastTbl t2
                  WHERE t2.Numbers = t.number                            
                )
  -- this is the filter for checking if the number appeared in the table before  startdate               
AND NOT EXISTS (                          
                  SELECT *
                  FROM tbl t3
                  WHERE t3.Date<@startDate and t.number=t3.number
                )

谢谢Gilad

Answer 1

既然不是in就把小于换成大于

select phoneNumber from someTbl where date > @startDate

接下来过滤掉somePastTbl

select s1.phoneNumber from someTbl s1
LEFT JOIN somePastTbl s2 on s1.phoneNumber = s2.phonenumber
where s1.date > @startDate and s2 IS NULL

As Per comment:

小于开始日期的月份

SELECT COUNT(s1.phoneNumber) FROM someTbl s1
LEFT JOIN somePastTbl s2 on s1.phoneNumber = s2.phonenumber
where DATEADD(MONTH,-1,@startDate) < s1.date AND s1.date < @startDate and s2 IS NULL

Answer 2

多一个选项

SELECT t.phoneNumber
FROM SomeTbl t
WHERE t.date > @startDate
  AND NOT EXISTS (                          
                  SELECT 1
                  FROM SomePastTbl t2
                  WHERE t2.phoneNumber = t.phoneNumber                            
                  )

Answer 3

一个简单的索引

CREATE NONCLUSTERED INDEX IX_SomeTbl_date_phoneNumber
    ON SomeTbl
(
    date ASC,
    phoneNumber ASC
)

然后

SELECT phoneNumber FROM SomeTbl WHERE date > @startDate
EXCEPT
SELECT phoneNumber FROM SomePastTbl;

Answer 4

您希望电话号码的最小起始日期大于您的起始日期。这建议在计数(或创建列表)之前在电话号码级别进行聚合。

这里有一种方法，使用having子句中的条件:

select COUNT(*)
from (select t.phonenumber,
      from someTble t left outer join
           somePastTble pt
           on t.phonenumber = pt.phonenumber
      where pt.phonenumber is null
      having MIN(date) >= @startdate 
     ) t

也可以使用窗口函数(SQL 2005或更高版本)来编写。以下是使用min()的版本:

  select COUNT(distinct t.phonenumber)
  from (select t.*, t.phonenumber, MIN(date) over (partition by phonenumber) as mindate
        from someTble t
       ) t left outer join
       somePastTble pt
       on t.phonenumber = pt.phonenumber
  where pt.phonenumber is null and mindate >= @startdate