raj.prabhu001 (9/28/2015)
Hi guys please guide me how can i improve the performance of the below query by making changes to it.Select Col1
Col2
..... col n
FROM
(
Select col1
col2
... col n
FROM ( Select Col1
.. col n
FROM VIEW1
)a
LEFT JOIN
( Select Col1
.. col n
FROM VIEW1
)b
on a.id=b.id and a.date=b.date
Group by col1
... coln
)x
Group by col1 ...coln
Without being given anything else to go on, here's my assessment.
The GROUP BY would appear to be the giveaway on this. It appears that it's being used like a DISTINCT to remove duplicates for the results. That likely means than you have created an accidental CROSS JOIN in the form of a many-to-many between god-knows-what-happens-in-the-views due to insubstantial criteria and that, of course, is a major performance problem.
--Jeff Moden
Change is inevitable... Change for the better is not.