performance issue

Question

WITH MaxD AS (select t1.* from D as t1
	inner join (
				SELECT ID, CN, TB, MAX(BD) as MaxBD 
				FROM (
					SELECT ID, CN, TB, BD FROM [D]
					UNION ALL
					SELECT ID, CN, TB, BD FROM [M] 
				) AS t2
				GROUP BY ID, CN, TB)t3
				on t1.ID = t3.ID
				and t1.cn = t3.cn
				and t1.tb = t3.tb
				and t1.BD = t3.MaxBD
)
select * from (
			(SELECT '2' AS CI, t3.*
			FROM [M] t3
			LEFT JOIN MaxD t4 ON t4.ID = t3.ID AND t4.CN = t3.CN AND t4.TB = t3.TB 
			WHERE t4.ID IS NULL
			UNION ALL
			SELECT  t4.*
			FROM MaxD t4
			right JOIN [M] t3 ON t4.ID = t3.ID AND t4.CN = t3.CN AND t4.TB = t3.TB )
			) I
where  I.CI != '1';

I have optimized the logic to the above one. But still i am facing performance issues.

Table M and Table D contains close to 150 million records in each table and each table has column store index.each table has 364(M table) and 365(D table) columns.

I should not create indexes on any other columns. what is the best solution to tune the above query. To get the count for the above query it is taking 30 secs. if we can reduce down to atleast 15 secs that would be great.

Please help me on the above issue , thanks in advance.

Answer 1

I can't say that I understand the query, and that makes it difficult to suggest improvements. The second UNION ALL with a LEFT JOIN and a RIGHT JOIN with the same tables looks mysterious to me.

But as long as you are referring to the MaxD CTE more than once, it's probably a good idea to save the result of the CTE ot a temp table.

A CTE is basically a query-scoped macro, and the parse expands the definition of the CTE to every place where it occurs. That is, it may be computed multiple times, unless the opimtizer is able to do something smart.