Query take Good 10Min - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Query take Good 10Min

Author

Topic

cosy
Starting Member

22 Posts

Posted - 2009-01-29 : 18:35:01

HI All,

This query take 10 min and lock up few users and everyone is going to non responding so please some one advice or anything wrong in this view?

-------------------------------------------------------------

SELECT

im.part_no,
pa.moms_no,
im.sku_no,
im.description,
pa.category,
im.category as itemgroup,
pa.brand_studio,
pa.status,
pa.classification_no,
pa.genre,
im.entered_date,
pa.rights_acq_date,
pa.cat_first_rel as release_date,
isnull(pp.price_a,0) as 'Whsale',
isnull(pa.RRP,0)as 'Retail',
isnull(RetailSales.sales,0) as 'TotalSalesRetail',
isnull(WholesaleSales.sales,0) as 'TotalSalesWholesale',
isnull(TotSales.sales,0) as 'TotalSales',
case when qoh1.instock is null then 0 else qoh1.instock end QtyOnHand,
case when qoh1.instock is null then (0 - isnull(qa1.avail,0)) else (qoh1.instock - isnull(qa1.avail,0)) end QtyAvail,
case when pur1.orders is null then 0 else pur1.orders end purtotal

FROM
inv_master im

Left Join
pa_inv_attributes pa
on
im.part_no = pa.part_no

left join part_price as pp
on
im.part_no = pp.part_no

left join
(select ol.part_no,
sum(shipped - cr_shipped) as sales
from ord_list ol, orders od,pa_inv_attributes pa
where ol.part_no = pa.part_no and pa.category = 'D' and od.date_shipped > (getdate()-365) and od.order_no = ol.order_no and od.ext = ol.order_ext and ol.location like 'A%' and
od.user_code = 'RETAIL' group by ol.part_no) RetailSales
on im.part_no = RetailSales.part_no

left join
(select ol.part_no,
sum(shipped - cr_shipped) as sales
from ord_list ol, orders od, pa_inv_attributes pa
where ol.part_no = pa.part_no and pa.category = 'D' and od.date_shipped > (getdate()-365) and od.order_no = ol.order_no and od.ext = ol.order_ext and ol.location like 'A%' and
od.user_code <> 'RETAIL' group by ol.part_no) WholesaleSales
on im.part_no = WholesaleSales.part_no

left join
(select ol.part_no,
sum(shipped - cr_shipped) as sales
from ord_list ol, orders od,pa_inv_attributes pa
where ol.part_no = pa.part_no and pa.category = 'D' and od.date_shipped > (getdate()-365) and od.order_no = ol.order_no and od.ext = ol.order_ext and ol.location like 'A%'
group by ol.part_no) TotSales
on im.part_no = TotSales.part_no

left join
(select lbs.part_no,
sum(lbs.qty) as instock
from lot_bin_stock lbs, tdc_bin_master tbm
where lbs.bin_no = tbm.bin_no and lbs.location = tbm.location
and tbm.location not in ('ADA', 'MSR', 'SSR', 'MDA', 'SDA', 'AFL')and
usage_type_code not in ('quarantine', 'prodin', 'stlbin')
group by lbs.part_no) qoh1
on im.part_no = qoh1.part_no and im.void = 'N'

left join
(select invs.part_no,
sum(invs.commit_ed) as avail
from inv_sales invs
group by invs.part_no) qa1
on im.part_no = qa1.part_no and im.void = 'N'

left join
(select pl.part_no,
sum(qty_ordered - qty_received) as orders
from pur_list pl
where pl.status = 'o' or pl.status = 'q'
group by pl.part_no) pur1
on im.part_no = pur1.part_no and im.void = 'N'

where pa.category = 'D'
and pa.status <> 'DIS'
and im.void = 'N'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 18:40:24

Check Execution plan and indexes on joined column and Where clause and Aggregates. How many rows is this query fetching?

cosy
Starting Member

22 Posts

Posted - 2009-01-29 : 22:05:46

HI,

I'm not sure about "Check Execution plan and indexes on joined column and Where clause and Aggregates." how do i check?

This returned 2636rows

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 09:28:50

quote:
Originally posted by cosy

HI,

I'm not sure about "Check Execution plan and indexes on joined column and Where clause and Aggregates." how do i check?

This returned 2636rows

Press Actual Execution plan tab Query Analyzer and post snapshots of it over here so we can analyze.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources