select from n x 8 relationship
-
Dear all,
I have 3 tables C, K and CtoK. C and K have a primary key (id). CtoK associates id from C with 8 possible id from K. CtoK = (idc int ,idw int, index int) where index >= 0 and index < 8. c has also a datetime column 'creationdate'.
I want to retrieve every records from C within a particular date range. This is easy. But with each returned record I also want every corresponding id from K.
I have a solution with a temporary table. It works, but it's slow. c is huge.
I thought about this :
select C.id, k0.id, k1.id
from c
left outer join ( select idk, idc from CtoK where index = 0 ) k0 on k0.idc = c.id )
...
left outer join ( select idk, idc from CtoK where index = 7 ) k7 on k7.idc = c.id )
where c.creationdate <= @begin_date and c.creationdate < @end_date
This works but I wonder if it is a good way to do that ?
If the maximum value for index is changed, the code has to be rewritten. Is there a way to avoid that ? I thought about concataning the kx.id into a string but could not achieve that...
Thank you for your help.
This is maybe a common problem, so I apologize if it has already been posted several to this forum.
Patrick
Patrick Duflot
-
Since the join is expensive, I'd do it once in a derived table, then pivot the results to translate each index row into a column:
Select dt.ID,
Max( (Case Index When 0 Then 1 Else 0 End) * IdK ) As id_k0,
Max( (Case Index When 1 Then 1 Else 0 End) * IdK ) As id_k1,
Max( (Case Index When 2 Then 1 Else 0 End) * IdK ) As id_k2,
<fill in the rest>
Max( (Case Index When 7 Then 1 Else 0 End) * IdK ) As id_k7
From
(
Select C.Id, CtoK.IdK, Index
From C
Inner Join CtoK
On (C.Id = CtoK.IdC)
Where c.creationdate <= @begin_date and c.creationdate < @end_date
) dt
Group By dt.Id
-
Thank you very much for your reply, it really speeds up the query
Another question comes to my mind. If I need to return more fields from c or from other joined tables, should I add them to the group by clause or use another derived table ?
In extenso :
Select dt.ID,
Max( (Case Index When 0 Then 1 Else 0 End) * IdK ) As id_k0,
Max( (Case Index When 1 Then 1 Else 0 End) * IdK ) As id_k1,
Max( (Case Index When 2 Then 1 Else 0 End) * IdK ) As id_k2,
<fill in the rest>
Max( (Case Index When 7 Then 1 Else 0 End) * IdK ) As id_k7
<other fields>
From
(
Select C.Id, CtoK.IdK, Index, <other fields>
From C
left outer Join CtoK
On (C.Id = CtoK.IdC)
inner join <other source tables>
Where c.creationdate <= @begin_date and c.creationdate < @end_date
) dt
Group By dt.Id, <other fields>
or
Select dt2.ID, dt2.id_k0,... id_k7, <other fields>
from (
Select dt.ID,
Max( (Case Index When 0 Then 1 Else 0 End) * IdK ) As id_k0,
Max( (Case Index When 1 Then 1 Else 0 End) * IdK ) As id_k1,
Max( (Case Index When 2 Then 1 Else 0 End) * IdK ) As id_k2,
<fill in the rest>
Max( (Case Index When 7 Then 1 Else 0 End) * IdK ) As id_k7
From
(
Select C.Id, CtoK.IdK, Index
From C
Inner Join CtoK
On (C.Id = CtoK.IdC)
Where c.creationdate <= @begin_date and c.creationdate < @end_date
) dt
Group By dt.Id
) dt2
inner join <other source tables>
I don't see a great difference on my test system, but i am afraid that on production system, solution 1 would be too memory consuming, because of the 8 duplication of every aggregated fields in the dt table. Am I right ?
Once again your help is really appreciated
Patrick Duflot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply