MSSQL Query optimize : I have a query with very poor performance.
In the production base there are tens of millions rows of data.


    SELECT (SELECT MIN(CASE WHEN tab1.col1 = 0 THEN 0 ELSE 1 END) 
            FROM tab1 
		WHERE tab1.col2 = tab2.col2
          AND (tab2.col1 <> 1 OR (SELECT COUNT(*) FROM tab1 AS p1 WHERE tab1.col4 = p1.col4) = 0)
          AND (tab1.col3 = 1 OR tab2.col1 = 0 AND ISNULL((SELECT SUM(tab3.col1) FROM tab3 WHERE tab3.col1 = tab2.col2),0) < tab1.col5)
		) col_alias
    FROM tab2



I have already tried to resolve performance problem with indexes, JOINs and CTE. But since there is usage of aggregate functions and correlated subqueries, I have not dome anything which improve the performance.

Have anybody any ideas how I can do it?