August 21, 2008 at 9:16 am
Hi all,
I have a stored procedure that works fine in the Test/Development environment. I get the results back in around 30 seconds. But when I use the same procedure on a Production DB, I do not get any result back even after 30 minutes. Here is my procedure. I would appreciate if you can give me some advice.
Thanks
create proc dbo.ymsp_outbound_rpt_bc
@car_init char(4) = null,
@car_no char(6) = null,
@plant_id char(10) = null,
@yard_id char(10) = null,
@track_id char(10) = null,
@blocking_code char(10) = null,
@cust_no char(13) = null,
@ship_date_beg datetime = null,
@ship_date_end datetime = null
as
declare @C_car_init char(4),
@C_car_no char(6),
@C_arrv_date datetime,
@C_plant_id char(10),
@comm_code char(10),
@comm_name varchar(25),
@load_slip char(10),
@net_wt numeric(6,0),
@plant_name varchar(60)
create table #car_detail
( car_init char(4) NOT NULL,
car_no char(6) NOT NULL,
arrv_date datetime NOT NULL,
plant_id char(10) NULL,
yard_id char(10) NULL,
track_id char(10) NULL,
position numeric(3,0) NULL,
cust_name varchar(60) NULL,
order_no char(15) NULL,
block_code char(10) NULL,
sched_ship_date datetime NULL,
class_code char(10) NULL,
scale_weight numeric(6,0) NULL,
out_road char(4) NULL,
net_weight numeric(6,0) NULL,
load_slip char(10) NULL,
comm_name varchar(25) NULL,
plant_name varchar(60) NULL
)
begin
insert into #car_detail
( car_init, car_no, arrv_date, plant_id, yard_id, track_id, position,
cust_name, order_no, block_code, sched_ship_date, class_code,
scale_weight, out_road )
select a.car_init, a.car_no, a.arrv_date_time, a.plant_id, a.yard_id, a.track_id,
b.position, a.cust_name, a.order_no, a.out_block_code, a.sched_ship_date,
a.class_code, a.scale_weight, a.road
from ym_visit a, ym_spot b
where a.plant_id = b.plant_id
and a.yard_id = b.yard_id
and a.track_id = b.track_id
and a.spot_id = b.spot_id
and a.plant_status = 'I'
and a.car_status = 'SL'
--Self Eliminating parameteres
and (a.car_init = @car_init or @car_init is null)
and (a.car_no = @car_no or @car_no is null)
and (a.plant_id = @plant_id or @plant_id is null)
and (a.yard_id = @yard_id or @yard_id is null)
and (a.track_id = @track_id or @track_id is null)
and (a.out_block_code = @blocking_code or @blocking_code is null)
and (a.cust_no = @cust_no or @cust_no is null)
and (a.sched_ship_date >= @ship_date_beg or @ship_date_beg is null)
and (a.sched_ship_date <= @ship_date_end or @ship_date_end is null)
order by a.car_init, a.car_no, a.arrv_date_time
declare car_list_cursor cursor for
select car_init, car_no, arrv_date, plant_id
from #car_detail
open car_list_cursor
fetch car_list_cursor into @C_car_init, @C_car_no, @C_arrv_date, @C_plant_id
while @@fetch_status = 0
begin
if exists (select * from ym_visit_comm_load
where car_init = @C_car_init
and car_no = @C_car_no
and arrv_date_time = @C_arrv_date
and compart_no = 1)
begin
select @comm_code = comm_code,
@load_slip = load_slip,
@net_wt = calc_weight
from ym_visit_comm_load
where car_init = @C_car_init
and car_no = @C_car_no
and arrv_date_time = @C_arrv_date
and compart_no = 1
select @comm_name = ltrim(rtrim(substring(comm_name,1,25)))
from RT_COMMODITY_FACT
where comm_code = @comm_code
end
select @plant_name = plant_name
from ym_plant
where plant_id = @C_plant_id
update #car_detail
set net_weight = @net_wt,
load_slip = @load_slip,
comm_name = @comm_name,
plant_name = @plant_name
where car_init = @C_car_init
and car_no = @C_car_no
and arrv_date = @C_arrv_date
fetch next from car_list_cursor into @C_car_init, @C_car_no, @C_arrv_date, @C_plant_id
end
close car_list_cursor
deallocate car_list_cursor
end
select * from #car_detail
drop table #car_detail
go
August 21, 2008 at 12:28 pm
Usually, DEV/QA has much less data than PROD does. So, your sproc runs faster in DEV/QA.
I saw you used a cursor in the sproc. Could you get rid of it? (It seems to me that the cursor can be removed.....)
August 23, 2008 at 4:03 am
Hey,
The first thing you should do when troubleshooting such issues is look at the execution plan, in actual fact when investigating any query performance related issue you should start with the execution plan.
You can do this by setting SET SHOWPLAN or select include actual execution plan in Management studio. Once you have the execution plan, find the operator which has the greatest cost and thats your problem area.
Note: If prod data is equal to dev/test then try updating prods statistics:
Execute: UPDATA STATISTICS on each object involved in the stored proc.
Good Luck 😀
August 23, 2008 at 6:59 am
The solution seems overly complex when a "LEFT OUTER JOIN" appears to have the same result.
select ym_visit.car_init, ym_visit.car_no, ym_visit.arrv_date_time, ym_visit.plant_id
, ym_visit.yard_id, ym_visit.track_id, ym_spot.position
, ym_visit.cust_name, ym_visit.order_no, ym_visit.out_block_code
, ym_visit.sched_ship_date
, ym_visit.class_code, ym_visit.scale_weight, ym_visit.road
, ym_visit_comm_load.comm_code
, ym_visit_comm_load.load_slip
, ym_visit_comm_load.calc_weight
, ym_visit_comm_load.net_weight
, ltrim(rtrim(substring(RT_COMMODITY_FACT.comm_name,1,25)))
fromym_visit
JOINym_spot b
on ym_visit.plant_id = ym_spot.plant_id
and ym_visit.yard_id = ym_spot.yard_id
and ym_visit.track_id = ym_spot.track_id
and ym_visit.spot_id = ym_spot.spot_id
LEFT OUTER JOIN ym_visit_comm_load
on ym_visit_comm_load.car_init = ym_visit.car_init
and ym_visit_comm_load.car_no= ym_visit.car_no
and ym_visit_comm_load.arrv_date_time = ym_visit.arrv_date_time
and ym_visit_comm_load.compart_no = 1
JOINRT_COMMODITY_FACT
on RT_COMMODITY_FACT.comm_code = ym_visit_comm_load.comm_code
WHEREym_visit.plant_status = 'I'
andym_visit.car_status = 'SL'
and(ym_visit.car_init = @car_init or @car_init is null)
and(ym_visit.car_no = @car_no or @car_no is null)
and(ym_visit.plant_id = @plant_id or @plant_id is null)
and(ym_visit.yard_id = @yard_id or @yard_id is null)
and(ym_visit.track_id = @track_id or @track_id is null)
and(ym_visit.out_block_code = @blocking_code or @blocking_code is null)
and(ym_visit.cust_no = @cust_no or @cust_no is null)
and(ym_visit.sched_ship_date >= @ship_date_beg or @ship_date_beg is null)
and(ym_visit.sched_ship_date <= @ship_date_end or @ship_date_end is null)
order by ym_visit.car_init
,ym_visit.car_no
,ym_visit.arrv_date_time
SQL = Scarcely Qualifies as a Language
August 23, 2008 at 8:37 am
I modified my first select statement. The problem was the way I was using the Self Eliminating parameteres.
Instaed of using this clause
(a.car_init = @car_init or @car_init is null)
I used the
a.car_init = IsNull(@car_init,a.car_init)
and it solved the issue. Apparently the OR clause was the problem.
Thanks for all the input.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply