EDI报告的人口统计信息

Question

我正在查看我的一些公司的EDI报告，看看我是否能使他们更快。在我们的AWS RDS环境中，下面的查询在不到1秒的时间内运行，但是在办公室的刀片上大约需要55秒，因此还有改进的余地。我没有看到任何常见的基于谷歌搜索的表现罪魁祸首，但我不是专家。

当我说"EDI“时，我表示这个查询的目的是将数据从一个计算机系统传输到另一个计算机系统。例如，这就是为什么我将所有日期转换为字符串的原因，因为这正是目标系统所需要的(以"mm/dd/yyyy“格式)。

一些背景信息：

• 这两个表中各有大约11,000行。
• 两个表是非规范化的“快照”表。
• 查询返回3500条记录。
• 这两个快照表具有非唯一的非聚集索引。
• 第三个表(CaseOpenEnrollmentPeriod)有一个唯一的聚集索引，尽管它只有四个记录。

DECLARE @MostRecentSnapshotTime datetime
SET @MostRecentSnapshotTime = (SELECT MAX(SnapshotLoadStartTime) AS Expr1
                       FROM   Snapshot.SnapshotLog)

SELECT 
   SPE.SSN          
  ,SPE.SSN 
  ,CONVERT(NVARCHAR(10), SPE.BirthDate, 101)
  ,SPE.LastName
  ,SPE.FirstName 
  ,LEFT(SPE.MiddleName, 1)
  ,SPE.Address1
  ,SPE.Address2
  ,SPE.City
  ,SPE.StateCode
  ,LEFT(SPE.ZipCode, 5)
  ,'SampleText'                         
  ,'SampleText'                     
  ,CASE WHEN SPE.MaritalStatusCode = 'SampleText' THEN 'SampleText' END
  ,SPE.Gender
  ,SPE.[Status]
  ,CASE
    WHEN SEE.PlanCode = 'MED' THEN 'MED 16'
    WHEN SEE.PlanCode = 'MEDP' THEN 'MEDPlus 16'
    WHEN SEE.PlanCode = 'MEDH' THEN 'MEDHeavy 16'
    WHEN SEE.PlanCode = 'MEDHP' THEN 'MEDHeavyPlus 16'
    WHEN SEE.PlanCode = 'MVP' THEN 'MVP 16'
    ELSE NULL 
   END                              
  ,'4'                          
  ,CASE
    WHEN SEE.TierCode =  'EO' THEN 'E'   
    WHEN SEE.TierCode =  'ESP' THEN 'ES' 
    WHEN SEE.TierCode =  'ECH' THEN 'EC'   
    WHEN SEE.TierCode =  'EFam' THEN 'F' 
    ELSE NULL 
   END                              
  ,'4'                          
  ,SPE.DepartmentCode
  ,CONVERT(NVARCHAR(10), SPE.HireDate,                  101)    
  ,CONVERT(NVARCHAR(10), COEP.BenefitsEffectiveDate,    101)    
  ,CONVERT(NVARCHAR(10), SEE.EffectiveDate,             101)    
  ,CONVERT(NVARCHAR(10), SEE.StopDate,                  101)    
  ,CASE WHEN NULLIF(LTRIM(SPE.TerminationDate), '') IS NOT NULL AND SEE.LifeEventActionID IS NOT NULL THEN CONVERT(VARCHAR(4), SEE.LifeEventActionID)
        WHEN NULLIF(LTRIM(SPE.TerminationDate), '') IS NOT NULL AND SEE.LifeEventActionID IS NULL THEN 'AI'
        ELSE ''                 
   END                          

  ,SEE.EnrollerID               
  ,'1'                                          
  ,(SEE.IssCost * 12 / SPE.PayCycle)                    
  ,CASE WHEN SPE.PayCycle = 52 THEN 'Weekly'
        WHEN SPE.PayCycle = 12 THEN 'Monthly'
        WHEN SPE.PayCycle = 24 THEN 'Semi-Monthly'
        ELSE 'Other' 
   END                              
  ,SPE.WorkPhone
  ,SPE.HomePhone
  ,SPE.Email
  ,ISNULL(SPE.HeightInInches, '')
  ,ISNULL(SPE.WeightInPounds, '')

FROM Snapshot.EmployeeElection SEE
  JOIN Snapshot.PersonEmployee SPE
    ON  SEE.EmployeeID = SPE.AssignedID AND 
        SEE.ConfirmationID = SPE.ConfirmationID AND 
        SEE.CaseOpenEnrollmentPeriodID = SPE.CaseOpenEnrollmentPeriodID AND 
        SEE.LoadDateTime = SPE.LoadDateTime
  JOIN CaseOpenEnrollmentPeriod COEP
    ON COEP.CaseOpenEnrollmentPeriod_ID = SEE.CaseOpenEnrollmentPeriodID
WHERE SEE.LoadDateTime = @MostRecentSnapshotTime
  AND PlanID <> 8
  AND SPE.ConfirmationID > 0;

以下是XML执行计划。我是新的执行计划，所以我不确定这是否是你需要的信息。

Answer 1

别名

别名SEE、SPE和COEP不是描述性的。常常很容易用缩略词来表示表，并将它们用作别名。对于已经熟悉数据库结构的人来说，这一切都是好事，但是当您向“局外人”展示这段代码时，这些别名就会成为理解代码的一个障碍。

我想像这样的东西会读得更好：

FROM Snapshot.EmployeeElection AS EmpElecs --was SEE
  JOIN Snapshot.PersonEmployee AS Emps --was SPE
    ...
  JOIN CaseOpenEnrollmentPeriod AS Periods --was COEP

执行计划

发布执行计划后的更新。

看到SnapshotPersonEMployee上的RID查找(Heap)操作了吗？对我来说，这表明SQK引擎无法使用索引进行查询，请参阅识别关键问题和排除查找问题及解决方法 by 亚伦·伯特兰在DBA.SE站点上的操作。引用他的话：

这些查找发生在索引不满足查询(未覆盖的查询)时，因此需要从聚集索引或堆中检索其他数据。未覆盖的查询可能是一个问题，因为对于索引中的每一行，都必须获取额外的列(S)；这可能会对大型数据集产生重大影响并影响整体性能。

如果很难找出确切的问题，我建议从查看这个联接中的列开始，看看它们是否被索引，如果不是，这可能就是您的问题所在。如果不是所有这些列都是索引的，那么它很可能必须按照执行计划的指示在表本身中查找。

  JOIN Snapshot.PersonEmployee SPE
    ON  SEE.EmployeeID = SPE.AssignedID AND 
        SEE.ConfirmationID = SPE.ConfirmationID AND 
        SEE.CaseOpenEnrollmentPeriodID = SPE.CaseOpenEnrollmentPeriodID AND 
        SEE.LoadDateTime = SPE.LoadDateTime

您可以考虑添加索引，这将提高所有使用索引的查询的性能，使用可能会将非索引列移动到末尾的HAVING子句，这可能会将这些匹配移到结果集，而不是初始查找。(也许)

数据转换

您已经提到，该代码用于将日期作为字符串(以mm/dd/yyyy格式)接受的EDI，我必须非常频繁地处理这样的事情，而且很可能您无法更改EDI本身(或者至少不需要花费大量的精力/费用)，所以这是可以理解的。

我本打算建议转换日期而不是转换，但后来我意识到，转换不允许选择输出格式，它将以yyyy-mm-dd的形式出现，这是默认的Server/ that格式。不幸的是，这可能是查询中最昂贵的操作集之一。考虑到这些限制，可能实际上并没有一个改善这些问题的方法。

日期时间处理可能很烦人，尤其是当您必须在实际日期时间值和它们的字符串表示之间进行转换时。