• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Optimize a stored procedure

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
0
zafridi
Asked:
zafridi
4 Solutions
 
QPRCommented:
The adding of a where clause(s) would help if possible.
That's a lot of unions! I don't know the relationships between tables but is there no way to use inner joins?
0
 
NightmanCTOCommented:
Does each select return a disctinct row set? If so, union all would be faster than simply union.
0
 
Scott PletcherSenior DBACommented:
I **strongly suspect** this could be made much more efficient by examining the view definitions and determining a better overall approach to the query.  However, that will take a thorough review that would include *all* the view definitions.
0
 
satish_nagdevCommented:
hi,
is there large amount of data that you are inserting into @account if thats the case then you should use #temp table instead of @table variables as performance while using  table variable in case of large amount of data is not better compared to #temp tables.

regards,
satish.
0
 
zafridiAuthor Commented:
its not a lot of data. its just that its being used for a conolidated report where its getting all the sums of the reports. Thats why i used table variable. but yeah tats a good idea to use union all instead of union caz i know each row is gona be distinct anyway..thanz nightman..but i ran it with both union and union all..wasnt much difference but i wud leave it with union all. anyway thank u all for ur input.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now