optimized a stored procedure - SQL Server Forums

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)

optimized a stored procedure

Author

Topic

Teachme
Starting Member

45 Posts

Posted - 2006-11-08 : 14:16:35

I have the following stored procedure which is used in a crystal report but its very slow. Is there a way to make it run faster or make a view out of it. thanks

declare @account table
(account_parent varchar(12)
,optional_class_nm varchar (30)
,report_end_dt varchar(8))
declare @t1 table
(category varchar(5)
,ledger_num int
,[description] varchar (75)
,account_parent varchar(12)
,market_val_cur_yr decimal(17,2)
,interest decimal(17,2)
,paid_acrud_int decimal(17,2)
,commissions_paid decimal(17,2)
,unrealized_gain decimal(17,2)
,unrealized_loss decimal(17,2)
,realized_gain decimal(17,2)
,realized_loss decimal(17,2)
,invest_inc_recd decimal(17,2))

insert into @account (account_parent, optional_class_nm, report_end_dt)
select a.account_parent, vo.optional_class_nm,a.report_end_dt
from tbl_hist_perac_account_info as a
inner join vw_account_opt_class as vo
on a.opt_code = vo.optional_class_cd

insert into @t1
(category
,ledger_num
,[description]
,account_parent
,market_val_cur_yr
,interest
,paid_acrud_int
,commissions_paid
,unrealized_gain
,unrealized_loss
,realized_gain
,realized_loss
,invest_inc_recd)

select
s6t.category
,s6t.ledger_num
,s6t.[description]
,v1s.account_parent
,v1s.tot_book_val_cur_yr
,v1s.tot_interest_due
,0.00 as paid_acrud_int
,0.00 as commissions_paid
,0.00 as urealized_gain
,0.00 as unrealized_loss
,0.00 as realized_gain
,0.00 as realized_loss
,v1s.tot_interest_due
from vw_schedule_1_sum as v1s
join tbl_perac_schedule_6 as s6t
on v1s.ledger_num = s6t.ledger_num

union

select
s6t.category
,s6t.ledger_num
,s6t.[description]
,v2s.account_parent
,v2s.tot_book_val
,v2s.tot_due_and_accr
,0 as paid_acrud_int
,0 as commissions_paid
,0 as urealized_gain
,0 as unrealized_loss
,0 as realized_gain
,0 as realized_loss
,v2s.tot_interest_recd
from vw_schedule_2_sum as v2s
join tbl_perac_schedule_6 as s6t
on v2s.ledger_num = s6t.ledger_num

union

select s6t.category
,s6t.ledger_num
,s6t.[description]
,v3as.account_parent
,v3as.tot_book_val
,v3as.tot_due_and_accrd
,v3bs.tot_pd_accrd_interest
,v3bs.tot_com_paid
,v3as.tot_unrealized_gain
,v3as.tot_unrealized_loss
,v3cs.tot_realized_gain
,v3cs.tot_realized_loss
,invest_inc_recd = (v3as.tot_interest_recd + v3cs.tot_interest_recd)

from vw_schedule_3a_sum as v3as
join tbl_perac_schedule_6 as s6t
on v3as.ledger_num = s6t.ledger_num
join vw_schedule_3b_sum as v3bs
on v3as.account_parent = v3bs.account_parent
and v3as.ledger_num = v3bs.ledger_num
join vw_schedule_3c_sum as v3cs
on v3bs.account_parent = v3cs.account_parent
and v3as.ledger_num = v3cs.ledger_num

union

select
s6t.category
,s6t.ledger_num
,s6t.[description]
,v4as.account_parent
,v4as.tot_market_val_cur
,0 as interest
,0 as paid_acrud_int
,v4bs.tot_commissions_paid
,v4as.tot_unrealized_gain
,v4as.tot_unrealized_loss
,v4cs.tot_realized_gain
,v4cs.tot_realized_loss
,invest_inc_recd =(v4as.tot_dividends + v4cs.tot_dividends)
from vw_schedule_4a_sum as v4as
join tbl_perac_schedule_6 as s6t
on v4as.ledger_num = s6t.ledger_num
join vw_schedule_4b_sum as v4bs
on v4as.account_parent = v4bs.account_parent
and v4as.ledger_num = v4bs.ledger_num
join vw_schedule_4c_sum as v4cs
on v4bs.account_parent = v4cs.account_parent
and v4bs.ledger_num = v4cs.ledger_num

union

select
s6t.category
,s6t.ledger_num
,s6t.[description]
,vs5sum.account_parent
,vs5sum.tot_end_market_val
,0.00 as tot_interest_due
,0.00 as paid_acrud_int
,0.00 as commissions_paid
,vs5sum.tot_unrealized_gain
,vs5sum.tot_unrealized_loss
,vs5sum.tot_realized_gain
,vs5sum.tot_realized_loss
,vs5sum.tot_recd_invest_inc

from vw_schedule_5_sum as vs5sum
join tbl_perac_schedule_6 as s6t
on vs5sum.ledger_num = s6t.ledger_num

select
acct.optional_class_nm
,acct.account_parent
,t1.category
,t1.ledger_num
,t1.[description]
,t1.market_val_cur_yr
,t1.interest
,t1.paid_acrud_int
,t1.commissions_paid
,t1.unrealized_gain
,t1.unrealized_loss
,t1.realized_gain
,t1.realized_loss
,t1.invest_inc_recd
,acct.report_end_dt

from @account as acct
join @t1 as t1
on acct.account_parent = t1.account_parent

group by acct.optional_class_nm
,acct.account_parent
,t1.category
,t1.ledger_num
,t1.[description]
,t1.market_val_cur_yr
,t1.interest
,t1.paid_acrud_int
,t1.commissions_paid
,t1.unrealized_gain
,t1.unrealized_loss
,t1.realized_gain
,t1.realized_loss
,t1.invest_inc_recd
,acct.report_end_dt

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-08 : 16:13:46

You don't need the temporary tables, just write it as one query. Summarized like this

select 
acct.optional_class_nm
,acct.account_parent
,t1.category
,t1.ledger_num
,t1.[description] 
,t1.market_val_cur_yr
,t1.interest
,t1.paid_acrud_int 
,t1.commissions_paid 
,t1.unrealized_gain 
,t1.unrealized_loss
,t1.realized_gain 
,t1.realized_loss 
,t1.invest_inc_recd 
,acct.report_end_dt

from (the query you use to insert into @account) as acct 
join (the query with all the unions that you use to insert into @t1) as t1
on acct.account_parent = t1.account_parent

group by acct.optional_class_nm
,acct.account_parent
,t1.category 
,t1.ledger_num
,t1.[description] 
,t1.market_val_cur_yr
,t1.interest
,t1.paid_acrud_int 
,t1.commissions_paid 
,t1.unrealized_gain 
,t1.unrealized_loss
,t1.realized_gain 
,t1.realized_loss 
,t1.invest_inc_recd 
,acct.report_end_dt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources