select a.item_cd, b.item_nm, b.basic_unit, b.item_acct,
(select isnull(sum(case when a2.debit_credit_flag = 'D' then a2.qty
End),0) -
isnull(sum(case when a2.debit_credit_flag = 'C' then a2.qty
End),0)
from i_goods_movement_header as a1, i_goods_movement_detail as
a2
where a1.item_document_no = a2.item_document_no
and a2.delete_flag = 'N'
and a2.plant_cd = '2907'
and a2.item_cd = a.item_cd
and convert(char(10), a1.document_dt, 126) < '2005-07-01') as
Stock,
(select isnull(sum(b2.qty), 0)
from i_goods_movement_header as b1(nolock),
i_goods_movement_detail as b2(nolock)
where b1.item_document_no = b2.item_document_no
and b2.delete_flag = 'N'
and b2.item_cd = a.item_cd
and b2.debit_credit_flag = 'D'
and b2.plant_cd = '2907'
and convert(char(10), b1.document_dt,126) >= '2005-07-01'
and convert(char(10), b1.document_dt, 126) <= '2005-07-12') as
In_Qty, (select isnull(sum(c2.qty), 0)
from i_goods_movement_header as c1(nolock),
i_goods_movement_detail as c2(nolock)
where c1.item_document_no = c2.item_document_no
and c2.delete_flag = 'N'
and c2.item_cd = a.item_cd
and c2.debit_credit_flag = 'C'
and c2.plant_cd = '2907'
and convert(char(10), c1.document_dt,126) >= '2005-07-01'
and convert(char(10), c1.document_dt, 126) <= '2005-07-12') as
Out_Qty,
isnull(from_factor ,1) as UNIT, (select top 1
isnull(item_price,1)
from s_item_sales_price w
here item_cd = a.item_cd
and sales_unit = 'BOX'
and valid_from_dt <= '2005-07-12' order by valid_from_dt desc)
as BOX,
(select top 1 isnull(item_price,1)
from s_item_sales_price where item_cd = a.item_cd
and sales_unit = 'EA'
and valid_from_dt <= '2005-07-12' order by valid_from_dt desc)
as EA
from b_item_by_plant as a(nolock), b_item as b(nolock),
b_translation_of_unit_for_item as c(nolock)
where a.item_cd = b.item_cd
and a.item_cd *= c.item_cd
and c.from_unit = 'EA'
and c.To_unit = 'Box
' and a.valid_flg = 'Y'
and plant_cd = '2907'
and (b.item_acct = '10' or b.item_acct = '50') order by
a.item_cd
꽤 길죠? 이것을 돌리면 2-30분 정도 걸립니다.
최대한 빨리 처리할려구 하는데 어떻게 하면 속도향상을 얻을수
있을까요?
많은 조언 부탁드립니다.
|