Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

massive inner query- your feedback please

Posted on 2013-11-09
7
Medium Priority
?
224 Views
Last Modified: 2013-11-13
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.
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 5

Author Comment

by:25112
ID: 39636523
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
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1336 total points
ID: 39636595
Hi,

I don't' see the point of table alias i - k.

Generally table aliases are a little more relevant than a - m.

Generally, these columns should have some form of indexing a.item_key, b.item_key, a.pick_location_key, b.item_location_key, a.pick_key, b_pick_key.

Is this what you wanted to know?

Regards
  David

I'm guessing that this is a star schema, but am wondering about the difference and apparent duplication between edp_item_fact and edp_item_perm
0
 
LVL 13

Assisted Solution

by:magarity
magarity earned 664 total points
ID: 39637243
The only potential trouble spot is item hist. You've specified a single date in the where clause but is there only one history record per item per day? If not, this will cause multiples and potentially make the working set quite large. You may need a subquery with an aggregate function to get one per day but I can't tell from here.
Also, a lot of the record counts are unequal so make sure none of them ought to be left joins instead.
Otherwise dtodd is correct that this is a pretty straightforward case of just indexing the various key fields.
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 5

Author Comment

by:25112
ID: 39640482
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
0
 
LVL 5

Author Comment

by:25112
ID: 39640485
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.
"
0
 
LVL 35

Accepted Solution

by:
David Todd earned 1336 total points
ID: 39640622
Hi,

I don't think that item_key is appropriate for the clustered index. Generally to avoid duplication, or rather, allow de-duplication, need to have a separate ID. But given the joins on item_key etc, these fields imho should be indexed.

Regards
  David
0
 
LVL 5

Author Comment

by:25112
ID: 39644798
thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question