Stored Procedure Performance

Question

Post reply

Stored Procedure Performance

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 1

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.....)

andrew.miller Grasshopper Points: 23 More actions · Answer 2

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 😀

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

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

Yasir Masood SSChasing Mays Points: 601 More actions · Answer 4

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.