We have below query which is working perfectly fine:-
;WITH CTEA AS
LEFT OUTER JOIN
TABLEA.COLUMN1 = TABLEB.COLUMN2
CTEA -------------- record count of CTEA is 2 million records
SELECT * ----------------------- records count of CTEC is 30-40K records, execution time 2 mins
But we have to do below query instead of "SELECT * FROM CTEC":-
CTEA.COLUMN0 = CTEC.COLUMN0
AND CTEA.COLUMN1 = CTEC.COLUMN1
AND CTEA.COLUMN2 = CTEC.COLUMN2
AND CTEA.COLUMN3 = CTEC.COLUMN3
AND CTEA.COLUMN4 = CTEC.COLUMN4
But above is not giving us any result for more than 30 mins, finally we have to kill the query, we tried it is not doing any cartesian product, so can anybody help us to troubleshoot where I'm doing mistake and what is wrong?
Please feel free more questions for doubts, Also my 5 columns where I'm doing join operation are VARCHAR columns.
Any Help ? Also let me know if their any other way to write above query instead of JOIN, main purpose is to reduce the execution time.
View Complete Post