asked on

massive inner query- your feedback please

Hi,

Can you suggest/ review the below code if it is optimal or if there are any recommendations that will benefit.

i will inserted in the comments the # of records in each table for the query.

EDP_ITEM_FACT & EDP_ITEM_PERM are almost same table (fields wise.. but differ a little..EDP_ITEM_FACT has more data in it than EDP_ITEM_PERM ).. these are the 2 tables in main focus here....

Both EDP_ITEM_FACT & EDP_ITEM_PERM related to WAREHOUSE table based on WAREHOUSE_KEY, hence i joined only one of them.
Both EDP_ITEM_FACT & EDP_ITEM_PERM relate to LOCATION table, based on LOCATION KEY, but the FKs are different.
ITEM_HIST is the history table and it is huge. I joined only one of the tables, since that should suffice through ITEM_KEY.
Both EDP_ITEM_FACT & EDP_ITEM_PERM relate to EDP_PICK through PICK_KEY (i joined both tables to this EDP_PICK)
Both EDP_ITEM_FACT & EDP_ITEM_PERM relate to DOT table through DOT_KEY, but the FKs are different- so joined both tables to DOT table.
Only EDP_ITEM_PERM has a necessary relation with EDP_BUSI_PERM.

the main aim is to see where there is a minute difference between records in
EDP_ITEM_FACT & EDP_ITEM_PERM
for the same ITEM KEY. (as seen in the last condition - the OR statement within the bracket)

The expected results (# of records returned by the SELECT) will/expected to be small (generally lower 3 digit #)

I would appreciate any feedback on the efficiency of the query as you see it. thanks.

25112

ASKER

the query is

SELECT
c.WAREHOUSE_CODE,
c.WAREHOUSE_NAME,
f.ITEM_ID,
e.LOCATION_ID,
e.LOCATION_NAME,
h.PICK_CODE,
h.PICK_DESC,
h.DOHTEM_CODE,
h.DOHTEM_DESC,
d.LOCATION_ID,
d.LOCATION_NAME,
g.PICK_CODE,
g.PICK_DESC,
g.DOHTEM_CODE,
g.DOHTEM_DESC,
f.EXERFM,
f.EXERLM

FROM

dbo.EDP_ITEM_FACT a /* EDP_ITEM_FACT has approx 1/2 Mil records */
JOIN dbo.EDP_ITEM_PERM b on a.ITEM_KEY=b.ITEM_KEY /* EDP_ITEM_PERM has approx 100K records */
JOIN dbo.WAREHOUSE c ON c.WAREHOUSE_KEY=b.WAREHOUSE_KEY /* WAREHOUSE has 990 records */
JOIN dbo.LOCATION d ON a.PICK_LOCATION_KEY = d.LOCATION_KEY /* LOCATION has approx 4k records */
JOIN dbo.LOCATION e ON b.ITEM_LOCATION_KEY = e.LOCATION_KEY
JOIN dbo.ITEM_HIST f on a.ITEM_KEY = f.ITEM_KEY /* ITEM_HIST has 62 Mil records */
JOIN dbo.EDP_PICK g on a.PICK_KEY = g.PICK_KEY/* EDP_PICK has approx 1000 records */
JOIN dbo.EDP_PICK h on b.PICK_KEY = h.PICK_KEY
JOIN dbo.DOT i on a.SSRSDT_DOT_KEY = i.DOT_KEY /* DOT has approx 6000 records */
JOIN dbo.DOT j on b.PERM_WAGE_DOT_KEY = j.DOT_KEY
JOIN dbo.EDP_BUSI_PERM k on b.BUSI_PERM_KEY = k.BUSI_PERM_KEY /* EDP_BUSI_PERM has 2008 records */

WHERE
a.REF_DATE = '2012-05-31' and
f.REF_DATE_HIST = '2012-05-30' and
g.DOHTEM_CODE <> 'A6' and
h.DOHTEM_CODE <> 'A6' and
f.MFG_CODE <> 'HP' and
(d.LOCATION_ID<>e.LOCATION_ID or g.PICK_CODE <>h.PICK_CODE or g.DOHTEM_CODE <> h.DOHTEM_CODE)/*This line is the heart of the code */

ORDER BY
c.WAREHOUSE_NAME,
e.LOCATION_ID,
h.PICK_DESC,
h.DOHTEM_DESC,
f.EXERFM,
f.EXERLM

SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

magarity

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

25112

ASKER

David, do you mean one non clustered index in the below?: (thank you- i am changing the alias to more meaningful; yes duplication to some extent because of warehouse, but in this case DOHTEM_CODE, LOCATION_ID, PICK_CODE vary and this query is mainly to see which ones differ)

EDP_ITEM_FACT.ITEM_KEY
EDP_ITEM_FACT.PICK_KEY
EDP_ITEM_FACT.PICK_LOCATION_KEY
EDP_ITEM_FACT.SSRSDT_DOT_KEY

EDP_ITEM_PERM.ITEM_KEY
EDP_ITEM_PERM.PICK_KEY
EDP_ITEM_PERM.WAREHOUSE_KEY
EDP_ITEM_PERM.ITEM_LOCATION_KEY
EDP_ITEM_PERM.PERM_WAGE_DOT_KEY
EDP_ITEM_PERM.BUSI_PERM_KEY

WAREHOUSE.WAREHOUSE_KEY

LOCATION.LOCATION_KEY

ITEM_HIST.ITEM_KEY

EDP_PICK.PICK_KEY

DOT.DOT_KEY

EDP_BUSI_PERM.BUSI_PERM_KEY

25112

ASKER

magarity,
the result set is generally 100-140.. so it is OK and expected.

i did not understand the below.. can you pl clarify:
"
Also, a lot of the record counts are unequal so make sure none of them ought to be left joins instead.
"

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

25112

ASKER

thanks.