"srikantj" wrote:

> Hi,
>
> FYI!
> The performance problem is because of having clause and huge number of
> records.
>
> My table has around 4 million records its taking more than 4 days to build
> the hierarchy. Anybody has suggestion to improve the performance by modifying
> the query or another workaround.
>
> DEtails are as follows:
> I am having two int columns in my table, Pid and Tid. There is many to many
> relationship between Pid and Tid.
>
> A Pid can become a parent of another Pid if the other Pid has ALL tid's that
> there in the parent Pid and the child Pid can have many more.
> Example data:
> Pid Tid
> 1 1
> 2 1
> 2 2
> 3 3
> 4 1
> 4 2
> 4 3
>
> From the above table, Pid =1 is parent of 1, 2, 4 has all of them has tid =1.
> Similarly, pid = 2 is parent of 2, 4 as both of them has tid 1,2.
> And Pid =3 is parent of 4 as both of them has 3.
>
> I have written a query to do this:
>
> SELECT a.Pid as Parent, b.Pid as Child
> FROM table1 a
> inner join table1 b
> on a.Tid = b.Tid
> group by a.Pid, b.Pid
> having Count(a.Pid) = Count(b.Pid)
>