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 tablesTwo of tables are de-normalized Snapshot tablesThe query returns 3500 recordsThe two Snapshot tables have non-unique, non-clustered indexesThe third table (CaseOpenEnrollmentPeriod) has a unique clustered index, although it is only four records long.DECLARE @MostRecentSnapshotTime datetimeSET @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.CaseOpenEnrollmentPeriodIDWHERE SEE.LoadDateTime = @MostRecentSnapshotTime  AND PlanID <> 8  AND SPE.ConfirmationID > 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.
Demographics information for EDI report
performance;sql;sql server;t sql
AliasesThe 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 outsider 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 COEPExecution planAn 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.LoadDateTimeYou 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 conversionsYou'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. 