Data Map query runs slow after upgrading to SQL Server 2012

PROBLEM:
The below Data Map query executes slowly after upgrade to SQL Server 2012.  In older versions of SQL Server, this query would run virtually instantaneously:

select [SIGNEDDATACURR] = cast((SELECT SUM(C.SIGNEDDATA) FROM DBO.ETL_CASHFLOW_SRC AS C WHERE C.ACCOUNT = ME.ACCOUNTPRE AND C.RPTCURRENCY = ME.RPTCURRENCY AND C.COSTCENTER = ME.COSTCENTER AND C.TIMEID = ME.MAXTIMEID) as [decimal](25, 10)), * from [dbo].[ETL_CASHFLOW_MAP_4] [me] 

Additionally, forcing a HASH Join hint and that won’t work either.

option (hash join)

CAUSE:
The problem was with the implicit conversion which was happening.  Implicit conversion behaves differently in SQL Server 2012 than older versions.

ANALYSIS:
Look at the query plan of the query, example below

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  |--Compute Scalar(DEFINE:([Expr1008]=CONVERT(decimal(25,10),[Expr1006],0)))

       |--Nested Loops(Inner Join, OUTER REFERENCES:([me].[MAXTIMEID], [me].[ACCOUNTPRE], [me].[COSTCENTER], [me].[RPTCURRENCY]))

            |--Table Scan(OBJECT:([BAD].[dbo].[ETL_CASHFLOW_MAP_4] AS [me]))

            |--Index Spool(SEEK:([me].[MAXTIMEID]=[BAD].[dbo].[ETL_CASHFLOW_MAP_4].[MAXTIMEID] as [me].[MAXTIMEID] AND [me].[ACCOUNTPRE]=[BAD].[dbo].[ETL_CASHFLOW_MAP_4].[ACCOUNTPRE] as [me].[ACCOUNTPRE] AND [me].[COSTCENTER]=[BAD].[dbo].[ETL_CASHFLOW_MAP_

                 |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1018]=(0) THEN NULL ELSE [Expr1019] END))

                      |--Stream Aggregate(DEFINE:([Expr1018]=COUNT_BIG([BAD].[dbo].[ETL_CASHFLOW_SRC].[SIGNEDDATA] as [C].[SIGNEDDATA]), [Expr1019]=SUM([BAD].[dbo].[ETL_CASHFLOW_SRC].[SIGNEDDATA] as [C].[SIGNEDDATA])))

                           |--Table Scan(OBJECT:([BAD].[dbo].[ETL_CASHFLOW_SRC] AS [C]), WHERE:(CONVERT_IMPLICIT(nvarchar(20),[BAD].[dbo].[ETL_CASHFLOW_SRC].[ACCOUNT] as [C].[ACCOUNT],0)=[BAD].[dbo].[ETL_CASHFLOW_MAP_4].[ACCOUNTPRE] as [me].[ACCOUNTPRE] AN

Notice we are doing an Implicit Conversion here:

(CONVERT_IMPLICIT(nvarchar(20),[BAD].[dbo].[ETL_CASHFLOW_SRC].[ACCOUNT] as [C].[ACCOUNT],0)=[BAD].[dbo].[ETL_CASHFLOW_MAP_4].[ACCOUNTPRE] as [me].[ACCOUNTPRE]


The above will be extremely expensive operation in SQL Server 2012. The index scan is nothing but sequential scanning for each row, and SQL Server 2012 has to manually convert the index’s value to NVARCHAR, as seen in the index plan’s details around the predicate.  This was less of an issue in older versions of SQL Server, and is more sensitive in SQL Server 2012.

RESOLUTION:

To solve this problem, the solution is to make sure the underlying staging tables in the data map match data type.