July 7, 2004 at 2:01 am
Hi!
I have an SP, that runs for 30 minutes.
EXEC dbo.REPORT_SHIPPING$QueueWithTransit_ne
This is the SP code:
-----------------------------------------------------------------------------
CREATE procedure dbo.REPORT_SHIPPING$QueueWithTransit_ne
-- @good_location char(10) = NULL,
-- @summa_sk tinyint
as
DECLARE @good_location char(10)
DECLARE @summa_sk tinyint
SET @summa_sk =1
SET @good_location =NULL
create table #QUE(
id int identity,
customer int,
sale_channel char(10) null,
sales_person int null,
ORDNUMBER char(22),
order_date datetime,
pay_guar_date datetime null,
spay_term int,
item int,
ftype tinyint,
good_stream char(10),
exp_ship_date datetime null,
unit_price money,
qty int,
payed tinyint,
stock int,
transit int,
qty_all int null,
qty_to_ship int null,
qty_from_transit int null,
region_person int null,
money_amount money null,
money_amount_SC money null
)
insert #QUE(
customer, sale_channel, sales_person,
ORDNUMBER, order_date, pay_guar_date, spay_term,
item, ftype, good_stream, exp_ship_date, unit_price,
qty, payed, stock, transit, qty_all, region_person, money_amount, money_amount_SC)
select
o.customer, o.sale_channel, o.sales_person,
o.ORDNUMBER, o.order_date, o.pay_guar_date, o.spay_term,
q.item, q.ftype, q.good_stream, l.exp_ship_date, l.unit_price,
q.qty, q.payed, v.stock, isnull(v.transit, 0) + isnull(v.po, 0), 0, e.region_person, o.money_amount, o.money_amount_SC
from
SH_LOT q (nolock)
join SORDER o (nolock)
on q.sorder = o.sorder
join SO_LOT l (nolock)
on q.so_lot = l.so_lot
left join INVENTORY v -- is a view that takes data from another view
on q.item = v.item and q.good_stream = v.good_stream
join CUSTOMER cus (nolock)
on cus.customer = o.customer
join ENTITY e (nolock)
on e.entity = cus.entity
where
q.good_stream = @good_location or @good_location is null
order by
q.item,
q.good_stream,
case when o.req_ship_date is not null then o.req_ship_date else o.order_date end,
case when o.req_ship_date is null then 0 else 1 end,
o.sorder
drop table #QUE
GO
-----------------------------------------------------------------------
If I run the query from this SP it takes only 5 seconds to complete:
DECLARE @good_location char(10)-- = NULL,
DECLARE @summa_sk tinyint
SET @summa_sk =1
SET @good_location =NULL
create table #QUE(
id int identity,
customer int,
sale_channel char(10) null,
sales_person int null,
ORDNUMBER char(22),
order_date datetime,
pay_guar_date datetime null,
spay_term int,
item int,
ftype tinyint,
good_stream char(10),
exp_ship_date datetime null,
unit_price money,
qty int,
payed tinyint,
stock int,
transit int,
qty_all int null,
qty_to_ship int null,
qty_from_transit int null,
region_person int null,
money_amount money null,
money_amount_SC money null
)
insert #QUE(
customer, sale_channel, sales_person,
ORDNUMBER, order_date, pay_guar_date, spay_term,
item, ftype, good_stream, exp_ship_date, unit_price,
qty, payed, stock, transit, qty_all, region_person, money_amount, money_amount_SC)
select
o.customer, o.sale_channel, o.sales_person,
o.ORDNUMBER, o.order_date, o.pay_guar_date, o.spay_term,
q.item, q.ftype, q.good_stream, l.exp_ship_date, l.unit_price,
q.qty, q.payed, v.stock, isnull(v.transit, 0) + isnull(v.po, 0), 0, e.region_person, o.money_amount, o.money_amount_SC
from
SH_LOT q (nolock)
join SORDER o (nolock)
on q.sorder = o.sorder
join SO_LOT l (nolock)
on q.so_lot = l.so_lot
left join INVENTORY v
on q.item = v.item and q.good_stream = v.good_stream
join CUSTOMER cus (nolock)
on cus.customer = o.customer
join ENTITY e (nolock)
on e.entity = cus.entity
where
q.good_stream = @good_location or @good_location is null
order by
q.item,
q.good_stream,
case when o.req_ship_date is not null then o.req_ship_date else o.order_date end,
case when o.req_ship_date is null then 0 else 1 end,
o.sorder
drop table #QUE
GO
When the INVENTORY view took data from a table both the SP and the query worked fast. After I changed the INVENTORY view to take data from another view, the SP began to run very slow.
What could be the matter?
Thanks.
July 12, 2004 at 8:00 am
This was removed by the editor as SPAM
July 13, 2004 at 10:04 am
Well, I haven't had a good look at your scripts, but may I suggest the following:
Firstly you should get the execution plan of the fast queries and then compare it with the poorly performing one. You can get the query plan by using the SQL Profiler or by pressing ctrl + k in query analyzer before you run the procedures.
Secondly ensure that you have the relevant statistics on the tables. Query analyzer will warn you if there are missing statistics.
I believe that its possible to create indexes on you views, perhaps the index isn't being used and you are getting large sorts in the tempdb.
Anyway- try out the above and let us know if you come right.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply