Improving this stored procedure

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 Development (2000)

Improving this stored procedure

Author

Topic

reenz
Starting Member

29 Posts

Posted - 2006-03-27 : 03:42:09

Currently i have this stored procedure that join 6 tables. It is meant to create a report : to retrieve records based on a particular acct no within a range of dates.

A account could have multiple smaller sub accounts and different status for the multiple accounts.

A part of the record is to retrieve the last station that generated the status for the different sub accounts hence the MAX(gen_datetime) status. scode, gen_stn are stations and status codes for the last checkpoint status. However, they are also slowing me down as the tables are big. Running this stored proc will take me like 3mins but this stored proc will be called by a asp.net web page hence i cant have the user waiting there for 3 mins

Is there any way to increase the performance of this stored proc to make it work under 30secs - 1min?

Any comments are appreciated.

CREATE PROCEDURE [dbo].[reportStandard]
(
@acct varchar(10),
@sDate varchar(20),
@eDate varchar(20)
)

AS
BEGIN
SET DATEFORMAT dmy
SELECT sd_acct,sd_awb,sd_tmpu,sd_dtpu,sd_loc_prod,sd_gbl_prod, sd_pcs,sd_wght,sd_dwght,sd_orig,sd_dest,sd_cnsg, sd_Id INTO #temp
from ship_detail
where [ship_detail].[sd_dtpu] >= @sDate AND
[ship_detail].[sd_dtpu] <= @eDate AND
[ship_detail].[sd_acct] = @acct

select t.sd_id, max(gen_datetime) as maxgd
into #temp2
from checkpoints cp2, #temp t
where cp2.sd_id = t.sd_id
group by t.sd_id

SELECT distinct sd_acct,sd_awb,sr_str,sd_dtpu,sd_tmpu,sd_loc_prod,sd_gbl_prod, sd_pcs,sd_wght,sd_dwght,sd_orig,sd_dest,sd_cnsg, cf.flight_date, cf.flight_no, cf.mawb
,del_datetime,signatory,scode, gen_stn,gen_datetime

from checkpoint_flight f, #temp t, checkpoints cp, checkpoint_flight cf, checkpoint_delivered cd,ship_ref sr, #temp2 t2
where gen_datetime = t2.maxgd and t.sd_id = f.sd_id and t.sd_id = cp.sd_id and t.sd_id = cf.sd_id and t.sd_id = cd.sd_id and t.sd_id = sr.sd_id
and t2.sd_id = t.sd_id
order by sd_awb

END
drop table #temp
drop table #temp2
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-27 : 03:56:16

Try this ?
Basically i just remove your temp table and create as derived table in the single query

SELECT 	distinct sd_acct, sd_awb, sr_str, sd_dtpu, sd_tmpu, sd_loc_prod, sd_gbl_prod, sd_pcs, sd_wght, sd_dwght, sd_orig, sd_dest, sd_cnsg, 
	cf.flight_date, cf.flight_no, cf.mawb, del_datetime, signatory, scode, gen_stn, gen_datetime
from 	(
		select 	t.sd_id, max(gen_datetime) as maxgd
		from 	checkpoints cp2 inner join ship_detail t
		on	cp2.sd_id	= t.sd_id
		where 	[ship_detail].[sd_dtpu] >= @sDate 
		AND	[ship_detail].[sd_dtpu] <= @eDate 
		AND	[ship_detail].[sd_acct] = @acct
		group by t.sd_id
	) t2
	inner join
	(
		select	sd_acct,sd_awb,sd_tmpu,sd_dtpu,sd_loc_prod,sd_gbl_prod, sd_pcs,sd_wght,sd_dwght,sd_orig,sd_dest,sd_cnsg, sd_Id
		from	ship_detail
		where 	[ship_detail].[sd_dtpu] >= @sDate 
		AND	[ship_detail].[sd_dtpu] <= @eDate 
		AND	[ship_detail].[sd_acct] = @acct
	) t
	on	t.sd_id 	= t2.sd_id
	inner join checkpoint_flight f
	on	f.sd_id 	= t.sd_id 
	inner join checkpoints cp
	on	cp.sd_id	= t.sd_id
	inner join checkpoint_flight cf
	on	cf.sd_id	= t.sd_id
	inner join checkpoint_delivered cd
	on	cd.sd_id	= t.sd_id
	inner join ship_ref sr
	on	sr.sd_id	= t.sd_id
order by sd_awb

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

reenz
Starting Member

29 Posts

Posted - 2006-03-27 : 04:30:45

thanks KH but it's not producing the correct results I want. =(
Last status information : scode, gen_stn, gen_datetime from checkpoints table actually depend on the max(gen_datetime). That is i need to search through the checkpoints table to find the scode (status),gen_stn(station) which has the maximum gen_datetime based on the different sub acct. Different sub account(sd_awb) from the same account (sd_acct) will have a different last status information. hence when i call the stored proc and pass in the sd_acct, it might return me multiple rows of different sub accts. Even the same sub acct might give me the multiple rows if all the rest of the fields are different. (aka different information)

Your current joining of tables return me the rows from joining the tables although gen_datetime was e max(gen_datetime) but all the scode and gen_stn records. So instead of 3 rows, i got 224 rows. =(

Your query took 1:18 sec if u are interested thou. thanks. i might work along that line n hopefully get a better response time with the correct results.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-27 : 04:32:49

sorry, must have accidentally remove it.
add this

where gen_datetime = t2.maxgd

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-27 : 04:34:56

also run this in Query Analyser with the execution plan turn on. Look at the execution plan and see where is the problem area

reenz
Starting Member

29 Posts

Posted - 2006-03-27 : 08:28:06

yup.. it's working correctly with the gen_datetime = t2.maxgd but its still taking 1:07 to run... Using asp.net to call this stored proc takes twice as much time =( =( =(

btw, what does the execution plan shows?
the bulk of the cost is located at
- checkpoint idx 16% checkpoints.idx as cp2
- ship_detail idx 1% [ship_detail].[sd_dtpu] >= @sDate ...
- Hash check team/inner join t.sd_id = cp2.sd_id 15%
- table scan checkpoints as cp2
- hash match root/inner join t.sd_id = cp.sd_id , cp.gen_date = [expr 1002]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources