search

msupply-foundation / open-msupply

Open mSupply represents our most recent advancement in the Logistics Management Information System (LMIS), expanding on more than two decades of development inherited from the well-established legacy of the original mSupply.
https://msupply.foundation/open-msupply/
Other
23 stars 14 forks source link

Use `CROSS JOIN` in consumption views #4462

Open lache-melvin opened 4 months ago

lache-melvin commented 4 months ago

In consumption/replenishment/adjustment views, add CROSS JOIN:

 (SELECT item.id AS item_id, store.id AS store_id FROM item CROSS JOIN store) as items_and_stores

Think it might be better to be more explicit that this is a CROSS JOIN? That syntax to be supported in both postgres and SQLite. This scares me a little, if we have a 40,000 stores and 1000 items this join would consider 40,000,000 rows!

I think this is kind of the only want to make sure we have 0 consumption showing for any items and stores with no transactions. I assume that's what we need?

We could potentially limit the stores/items to ones that are visible on the site or something? Would potentially impact central server too.

In theory this view shouldn't be queried in its entirety but thought it was worth pointing out.

Is this is a performance risk for future?

_Originally posted by @jmbrunskill in https://github.com/msupply-foundation/open-msupply/pull/4453#discussion_r1692279039_

lache-melvin commented 4 months ago

If we're looking at these views, might be cool to look into how we handle the different invoice types, see James' idea: https://github.com/msupply-foundation/open-msupply/pull/4453#discussion_r1692506886

Chris-Petty commented 2 months ago

Refinement: yes later, should be low impact for foreseeable future