2

Query looks something like this:

SELECT 
    A.Id, 
    COUNT(DISTINCT (CASE WHEN (C.TypeId in (54, 57, 58, 59) OR (ISNULL(B.count1, 0) + ISNULL(B.count3, 0) + ISNULL(B.count2, 0) > 0)) 
                           THEN D.AdrsId 
                           ELSE NULL 
                    END)) AS C1), 
   C2, C3.................................................C42 
FROM 
    A123 A 
INNER JOIN 
    E123 E on A.Id = A.ID 
INNER JOIN 
    B123 B on B.Id = A.Id AND B.IsDeleted = 0 
INNER JOIN 
    C123 C on C.Id = B.Id AND C.Isdeleted = 0 
LEFT OUTER JOIN 
    D123 D ON B.someId = D.someId AND D.IsDeleted = 0 AND D.xId > 0 
GROUP BY 
    A.Id

C2, C3, ..., C42 are columns with COUNT(DISTINCT) operation like C1.

Compared query execution plans in 2012 and 2014, the difference is in distinct sort cost.

komalnbahetwar
  • 21
  • 6

2 Answers2

1

Could you check the compatibility level of your database in SQL 2014 ? And confirm it's SQL Server 2014 (120). 2014 have a new cost estimator, if the compatibility level is not 120 it will use legacy one and this could lead to bad query plan.

http://www.brentozar.com/blitz/old-compatibility-level/

Hope this help.

Sauget Charles-Henri
  • 441
  • 4
  • 17
0

You seem to have an extreme number of distinct operations. SQL Server is not particular good at dealing with COUNT DISTINCT.

Consider writing the temporary data that is the source for the distinct to a temp table. Then, create a CLUSTERED COLUMNSTORE index on it. Then, run one distinct operation at a time on that temp table. This will likely result in very fast batch mode hash aggregations.

usr
  • 168,620
  • 35
  • 240
  • 369
  • Then why it is running in 2012 without any issues even though it has 42 count distinct columns? – komalnbahetwar Jul 07 '15 at 12:38
  • Is it running faster in 2012? You have not said that before. How much faster? – usr Jul 07 '15 at 12:43
  • Yes it is taking 0 seconds to run the query in 2012. Thats y i have mentioned the stmnt "Compared query execution plans in 2012 and 2014, the difference is in distinct sort cost." – komalnbahetwar Jul 07 '15 at 12:46
  • In 2014 it is taking more than 30 mins – komalnbahetwar Jul 07 '15 at 12:47
  • 1
    Then we should find a plan difference in structure. Those 4 vs. 5% costs certainly do not explain a difference of 1000x! They probably are just different because something else in the plan has changed the total cost. Post the full plan somewhere. – usr Jul 07 '15 at 12:49
  • http://tinypic.com/usermedia.php?uo=bbi8MiGk22NROmFFAIpj04h4l5k2TGxc#.VZvTcflViko Does this help any way? coz I am unable to capture the huge query execution plan in an image. – komalnbahetwar Jul 07 '15 at 13:26
  • Thanks for the solution, above solution worked in sql server 2014. It is working fine now which was timing out before. – komalnbahetwar Jul 08 '15 at 06:38