Demographics information for EDI report I am going through some of my companies EDI reports to see if I can make them faster.  The query below runs in less than one second in our AWS RDS environment, but it takes ~55 seconds on a blade in the office, so there is room for improvement.  I do not see any common performance culprits based upon Google searches, but I am not an expert.

When I say, "EDI", I am expressing that the purpose of this query is to transfer data from one computer system to another. For instance, that is why I am converting all of the dates to strings, because that is what the target system wants (in "mm/dd/yyyy" format).

Some background info:


There are about 11,000 rows in each of the two tables
Two of tables are de-normalized "Snapshot" tables
The query returns 3500 records
The two Snapshot tables have non-unique, non-clustered indexes
The third table (CaseOpenEnrollmentPeriod) has a unique clustered index, although it is only four records long.




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 &lt;&gt; 8
  AND SPE.ConfirmationID &gt; 0;


Here is the XML execution plan. I am new to execution plans, so I am not sure if this is the info you need or not.
Aliases
The alias names SEE, SPE and COEP are not descriptive. It's often tempting to make acronyms for tables and use those as aliases. It's all well and good for people who are already familiar with the structure of the database, but when you show this code to an &quot;outsider&quot; those aliases become a bit of an obstacle in understanding the code.
I think something like this would read a lot better:
FROM Snapshot.EmployeeElection AS EmpElecs --was SEE
  JOIN Snapshot.PersonEmployee AS Emps --was SPE
    ...
  JOIN CaseOpenEnrollmentPeriod AS Periods --was COEP


Execution plan
An update after you posted the execution plan.
See that RID Lookup (Heap) operation on SnapshotPersonEMployee? That to me indicates that the SQK engine could not use the index for that query, see Identifying Key and RID Lookup Issues and How to Resolve by Aaron Bertrand on the DBA.SE site. Quoting him:

These lookups occur when an index does not satisfy the query (non-covered query) and therefore additional data needs to be retrieved from the clustered index or the heap. Non-covered queries can be a problem because, for every row in the index, the additional column(s) must then be fetched; this can have a significant impact on large data sets and impact overall performance.

If hard to pinpoint the exact problem, I would suggest starting by looking at the columns in this join and see if they are indexed, if not that might be your problem right there. If not all those columns are indexed then it likely has to go lookup in the table itself as hinted by your execution plan.
  JOIN Snapshot.PersonEmployee SPE
    ON  SEE.EmployeeID = SPE.AssignedID AND 
        SEE.ConfirmationID = SPE.ConfirmationID AND 
        SEE.CaseOpenEnrollmentPeriodID = SPE.CaseOpenEnrollmentPeriodID AND 
        SEE.LoadDateTime = SPE.LoadDateTime

You could either look into adding indexes, which will speed up performance for all queries that use them, use perhaps move the non-indexed columns to an HAVING clause at the end, which might move these matches to the result set instead of the initial lookup. (maybe).

Date conversions
You've mentioned that the code is meant for an EDI which accepts dates as string (in mm/dd/yyyy format), I have to deal with things like that pretty frequently, and chances are you probably cannot change the EDI itself (or not without significant effort/expense, at least) so that's understandable.
I was going to suggest casting the dates instead of converting, but then I realized that casting does not allow to pick the output format, it would come out as yyyy-mm-dd which is default SQL Server/T-SQL date format. Unfortunately, that's probably one of the most expensive set of operations in your query. There may not actually be a way to improve these, given the limitations.
Date-Time handling can be annoying, and more so when you have to convert between actual date-time values and string representations of them.