sql-server - 日期之间和基于员工姓名的 SQL 查询

Question

我有一个 SQL Azure 数据库，其中有两个主表，我试图通过 View 连接这两个表。我已经可以运行了，但是执行时间超过了 2 分钟。

以下是我正在处理的主要表格和列:

交易表:

PostedDate          | EmployeeFirstName | EmployeeLastName | DollarsCollected | UserName
---------------------------------------------------------------------------------------------
09/08/2017 09:05 am | 'John'            | 'Smith'          |            42.25 | 'john.smith'
09/08/2017 09:07 am | 'Jane'            | 'Jones'          |            58.50 | 'jane.jones'
09/08/2017 09:15 am | 'Tom'             | 'Holland'        |            62.75 | 'tom.holland' 
09/08/2017 09:17 am | 'John'            | 'Smith'          |            48.50 | 'john.smith'    
09/08/2017 09:19 am | 'Jane'            | 'Jones'          |            32.25 | 'jane.jones'

客户历史记录

CustomerID | StartDate           | Duration | UserName      | TransactionType
-----------------------------------------------------------------------------
         1 | 09/08/2017 09:02 am |      600 | 'john.smit'h  | 'PropertyTax'
         2 | 09/08/2017 09:03 am |      500 | 'tom.holland' | 'TagRenewal'
         3 | 09/08/2017 09:04 am |      450 | 'jane.jones'  | 'PropertyTax'
         4 | 09/08/2017 09:12 am |      700 | 'john.smith'  | 'TagRenewal'
         5 | 09/08/2017 09:16 am |      300 | 'jane.jones'  | 'TagRenewal'

因此，这里的协议(protocol)是 - 一名员工一次只能接待一名客户。如果我们知道交易发布的时间以及发布该交易的员工，那么我们应该能够使用StartDate将该信息连接到 CustomerHistory 表。和StartDate + Duration作为总交易的“保护伞”。考虑StartDate + Duration等于 EndDate 。因此，这是我尝试运行来完成此任务的查询:

SELECT
    *
FROM
    CustomerHistory
    JOIN TransactionsTable ON
        CustomerHistory.UserName = TransactionsTable.UserName
        AND
        TransactionsTable.PostedDate >= CustomerHistory.StartDate
        AND
        TransactionsTable.PostedDate <= DATEADD( ss, CustomerHistory.StartDate, CustomerHistory.Duration )

作为引用，我在 UserName 上有索引字段以及日期字段。可以说我在这里过度简化了我的表，因为我希望加入每个表中更多的数据列。我在SQL Server中运行了执行计划，它告诉我哈希匹配将花费大约38%的执行时间，而对事务表的表扫描将花费42%。我对 SQL 很熟悉，但从未深入研究过像我在这里处理的资源密集型查询，并且它给我的服务器带来了相当大的负载，试图以这种方式运行它。

有人可以帮忙吗？

Answer 1

加速很困难，因为您的查询是不可SARGable的。我相信添加持久计算列应该非常适合您的查询。不过，您需要阅读它，因为这可能会减慢 DML(插入、更新和删除)。

我的表创建脚本供引用(SQL Fiddle http://sqlfiddle.com/#!6/f1a39/8 ):

Create Table dbo.TransactionsTable
(
  PostedDate Datetime,
  EmployeeFirstName Varchar(100),
  EmployeeLastName Varchar(100),
  DollarsCollected Money,
  UserName Varchar(100)
);

Create Table dbo.CustomerHistory
(
  CustomerID Int,
  StartDate DateTime,
  Duration Int,
  UserName Varchar(100),
  TransactionType Varchar(100)
);


Insert Into dbo.TransactionsTable
Values 
('09/08/2017 09:05 am','John','Smith',42.25 , 'john.smith'),
('09/08/2017 09:07 am','Jane','Jones',58.50 , 'jane.jones'),
('09/08/2017 09:15 am','Tom','Holland',62.75 , 'tom.holland'), 
('09/08/2017 09:17 am','John','Smith',48.50 , 'john.smith'),    
('09/08/2017 09:19 am','Jane','Jones',32.25 , 'jane.jones');
GO

Insert Into dbo.CustomerHistory
  Values (1,'09/08/2017 09:02 am',600,'john.smith' ,'PropertyTax'),
         (2,'09/08/2017 09:03 am',500,'tom.holland','TagRenewal'),
         (3,'09/08/2017 09:04 am',450,'jane.jones','PropertyTax'),
         (4,'09/08/2017 09:12 am',700,'john.smith','TagRenewal'),
         (5,'09/08/2017 09:16 am',300,'jane.jones','TagRenewal');
Go

确保您有此索引。

CREATE NONCLUSTERED INDEX ix_test1
  ON TransactionsTable(UserName,PostedDate) 
  Include (EmployeeFirstName,EmployeeLastName,DollarsCollected);
GO

对于客户历史记录，您可以添加持久列吗？然后索引该列

Alter Table CustomerHistory
Add EndDate As (DateAdd(ss,Duration,StartDate)) Persisted;
GO
CREATE NONCLUSTERED INDEX ix_test2
  ON CustomerHistory(UserName,StartDate,EndDate)
  Include(CustomerID,TransactionType);
GO

这是查询

SELECT *
FROM CustomerHistory AS A
INNER JOIN TransactionsTable AS B
  ON A.UserName = B.UserName
  AND B.PostedDate Between A.StartDate And A.EndDate;