I am having an issue with performance since changing our Linked server connection to SQL 2012. The query in 2008R2 ran in 9 seconds and now it takes around 10 minutes. When I trace the query on the 2 servers, the statement is completely different.
CREATE TABLE [dbo].[PS_OCC_ADDRESS_S]( [EMPLID] [varchar](11) NULL, [ADDRESS_TYPE] [varchar](4) NULL, [OCC_ADDR_TYP_DESCR] [varchar](30) NULL, [ADDRESS1] [varchar](55) NULL, [ADDRESS2] [varchar](55) NULL, [ADDRESS3] [varchar](55) NULL, [CITY] [varchar](30) NULL, [STATE] [varchar](6) NULL, [POSTAL] [varchar](12) NULL, [COUNTY] [varchar](30) NULL, [COUNTRY] [varchar](3) NULL, [DESCR] [varchar](30) NULL, [FERPA] [varchar](1) NULL, [LASTUPDDTTM] [varchar](75) NULL, [LASTUPDOPRID] [varchar](30) NULL ) ON [PRIMARY] Statement: SELECT EmplId, Address1, Address2, Address3, City, State = substring(State,1,2), Zip = substring(Postal,1,10), County, Country --INTO tmp_Addr FROM [Sql03].[DWHCRPT].[dbo].[PS_OCC_ADDRESS_S] WHERE (Address_Type = 'PERM') and (EmplID IN ( SELECT UserID As EmplID FROM Collegium.dbo.Users UNION SELECT EmplID As EmplID FROM Emap.dbo.Applications UNION SELECT ID As EmplID FROM HS_Program_Application.dbo.Applications ))Any suggestions would be appreciated