SQL Server - Identical query on near identical database takes 20 seconds on sql2012 but does not complete after 50+ hours on sql2014
Why is there such a difference in performance...


Check out this part of the estimated plan for the slow query (second server):



This estimates that 21 rows will come out of the join to RatesByCategory (which is after _Results323 has been joined to AttributesTable twice, and DataStreamDirection).  If we look at the equivalent part of the actual plan for the fast query:



There will actually be ~16,000,000 rows coming out of that join.  These is bad news, because those millions of rows flow right into a wall of Nested Loops joins, which is likely where that query slows to a crawl.

You were on the right track looking at estimates and trying to update stats, but this actually seems to be because of a row goal set by the semi-joins later in the execution plan.

Here's where the semi-join lives, and notice that everything "under it" has an estimate of 1 row:




  ...and what can I do to fix this?


Try adding this hint to the end of the query:

OPTION (QUERYTRACEON 4138)


This will disable the row goal and should you give you a different plan (possibly similar to the one on the lower compatibility level).

Since you said this query is generated, your best bet for solving this might be to create a plan guide so that you can add that hint without having to change the application that produces the query.




  If i try running the query on the second, but changing the compatibility level to 120 with trace 2312, the query finishes in 40 minutes.


Changing compat level to 120 would have enabled the "new" cardinality estimator, resulting in a different query plan - likely one that was less affected by the row goal (and thus why you were able to see it finish in 40 minutes, where the compat 110 query ran for 50 hours without finishing).