# Query optimization for the Link SAT with Dependent Child

**URL:** <https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301>\
**Category:** Data Vault 2.0\
**Tags:** dependent-child, link\
**Created:** [June 6, 2022, 9:47pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301 "2022-06-06T21:47:40Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![saqib](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/saqib/32/45_2.png) [@saqib](https://forum.data-community.org/u/saqib)\
**Post date:** [June 6, 2022, 9:47pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/1 "2022-06-06T21:47:40Z")

</div>

Hello all,

We have a LINK SAT which has multi-field Dependent Child. These multiple fields grouped, define a single LINE ITEM.

Querying this LINK SAT to get the latest record for each line item is extremely slow as we have to partition by

```auto
  qualify row_number() over(
    partition by
      hashkey
     , prime_dep_child_no
     , sub_dep_child_no
     , base_dep_child_no
    order by
      load_date desc
  ) = 1;

```

What is a good way to optimize this query?

Thanks,  
Saqib

---

<div class="post-metadata">

**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\
**Post date:** [June 6, 2022, 11:34pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/2 "2022-06-06T23:34:38Z")

</div>

The more you add as Dep-Keys the slower it will be!

Judging by your code you’re looking for a single record for hk + all the dep-keys… should just the hk suffice? I mean you can make it two step, get the latest record by hk, then get the dep-records you want? no?

---

<div class="post-metadata">

**Author:** ![saqib](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/saqib/32/45_2.png) [@saqib](https://forum.data-community.org/u/saqib)\
**Post date:** [June 6, 2022, 11:52pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/3 "2022-06-06T23:52:17Z")

</div>

@patrickcuba we need the latest record for all the line items…

---

<div class="post-metadata">

**Author:** ![Nicruzer](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/nicruzer/32/38_2.png) [@Nicruzer](https://forum.data-community.org/u/Nicruzer)\
**Post date:** [June 7, 2022, 5:06am UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/4 "2022-06-07T05:06:10Z")

</div>

If this is a Link Sat, then you are storing the dependent keys in the link itself, correct? From there, you are concatenating and hashing all the keys (including dependents) as the link\_hk. Thus, you are storing the link\_hk + ldts in the satellite, correct? This would eliminate the need for using the row\_number function, since you’ll have the composite hash and load date together in the Link Sat.

Am I way off base in my understanding of the situation?

---

<div class="post-metadata">

**Author:** ![AHenning](https://avatars.discourse-cdn.com/v4/letter/a/96bed5/32.png) [@AHenning](https://forum.data-community.org/u/AHenning)\
**Post date:** [June 7, 2022, 9:35am UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/5 "2022-06-07T09:35:48Z")

</div>

Hello Saqib!  
Without knowing on what kind of environment you are running on my suggestion might not be faster. But try to extract a subset in a inner query and then join back to the table.  
ex of inner query;  
select max(loaddate), hashkey, prime\_dep\_child\_no, sub\_dep\_child\_no, base\_dep\_child\_no  
group by hashkey, prime\_dep\_child\_no, sub\_dep\_child\_no, base\_dep\_child\_no

full query:  
select x,y,z  
from satellite s  
inner join (  
select max(loaddate) as ld, hashkey, prime\_dep\_child\_no, sub\_dep\_child\_no, base\_dep\_child\_no  
group by hashkey, prime\_dep\_child\_no, sub\_dep\_child\_no, base\_dep\_child\_no  
) lastrow on lastrow.ld = s.load\_date and lastrow.hashkey = s.hashkey and lastrow.prime\_dep\_child\_no = s.prime\_dep\_child\_no and lastrow.sub\_dep\_child\_no = s.sub\_dep\_child\_no

Without the use of a window function you are able to reduce the risk table spooling (MS SQL Server).

Another note is that if the childfields would reside in a Link then the complexity of extracting data would be less complex (and faster).

Another note2: It is strange (my opinion) that you choose to have the same grain for order and orderlines or is it something that I missed here?

Hope this was usefull for you!

---

<div class="post-metadata">

**Author:** ![saqib](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/saqib/32/45_2.png) [@saqib](https://forum.data-community.org/u/saqib)\
**Post date:** [June 7, 2022, 12:43pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/6 "2022-06-07T12:43:06Z")

</div>

Thanks @Nicruzer and @AHenning . We have the dependent child(ren) as part of the `LINKHASHKEY`. So I should probably be using the LINKHASHKEY to partition by, rather than one of the Business HK participating in the LINK. Let me try that out.

Thanks again.

---

<div class="post-metadata">

**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\
**Post date:** [June 7, 2022, 10:23pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/7 "2022-06-07T22:23:11Z")

</div>

Dep-key in used to create the link-hash-key???

---

<div class="post-metadata">

**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\
**Post date:** [June 7, 2022, 10:23pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/8 "2022-06-07T22:23:32Z")

</div>

the use of QUALIFY statement means it’s not SQL Server

---

<div class="post-metadata">

**Author:** ![saqib](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/saqib/32/45_2.png) [@saqib](https://forum.data-community.org/u/saqib)\
**Post date:** [June 7, 2022, 10:35pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/9 "2022-06-07T22:35:53Z")

</div>

> [@patrickcuba](#):
>
> Dep-key in used to create the link-hash-key???

yup. is that not correct?

---

<div class="post-metadata">

**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\
**Post date:** [June 7, 2022, 10:53pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/10 "2022-06-07T22:53:07Z")

</div>

dep-keys are degenerate dimensions that better qualify a relationship.  
If you include them in the link-hash-key generation they no longer represent the participating business entities and the link-sat itself will be defined at a deeper grain that the relationship depicted in the link.

You will effectively create a link record for every combination of participating hub-hash-keys + dep-keys and the link ceases to be a unique list of relationships

---

<div class="post-metadata">

**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\
**Post date:** [June 7, 2022, 10:56pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/11 "2022-06-07T22:56:52Z")

</div>

it’s part of the reason why you would choose to either have the Dep-Key in the Link vs in the Sat…

in the link — these attributes are applicable to the relationship forever and never change  
in the sat – these attributes change and we track those in the sat+dep-key design

That way, in choosing one or the other with the desired purpose, your model will scale

---

<div class="post-metadata">

**Author:** ![saqib](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/saqib/32/45_2.png) [@saqib](https://forum.data-community.org/u/saqib)\
**Post date:** [June 7, 2022, 10:59pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/12 "2022-06-07T22:59:46Z")

</div>

@patrickcuba , see here:

> [@Modeling a dependent child](https://forum.data-community.org/t/modeling-a-dependent-child/197/10):
>
> Saqib, you are correct. This appears to be a dependent child and is used to maintain the unit of work of the transaction represented in the link. You are correct in modeling it as such. Just make sure that it is included in your link hash string. The caveat here is that manufacturer is not a business concept, that it meets the litmus test of a dependent child key (manufacturer id) (i.e., cannot stand alone and have meaning without the context of the relationship). If this is truly the situ…

per @cmeyersohn , the dependent child(ren) should be part of the LINK HASHKEY generation.

---

<div class="post-metadata">

**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\
**Post date:** [June 7, 2022, 11:20pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/13 "2022-06-07T23:20:04Z")

</div>

yea i know!

🙂

up to you

---

<div class="post-metadata">

**Author:** ![RhysHanscombe](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/rhyshanscombe/32/394_2.png) [@RhysHanscombe](https://forum.data-community.org/u/RhysHanscombe)\
**Post date:** [December 1, 2025, 2:28pm UTC](https://forum.data-community.org/t/query-optimization-for-the-link-sat-with-dependent-child/301/14 "2025-12-01T14:28:40Z")

</div>

Closed on December 1, 2025, 2:28pm UTC


