Solved

Optimize a stored procedure

Posted on 2006-11-08
5
210 Views
Last Modified: 2008-03-17
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
Comment
Question by:zafridi
5 Comments
 
LVL 29

Assisted Solution

by:QPR
QPR earned 61 total points
ID: 17900822
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
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 67 total points
ID: 17900837
Does each select return a disctinct row set? If so, union all would be faster than simply union.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 61 total points
ID: 17901978
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
 
LVL 4

Assisted Solution

by:satish_nagdev
satish_nagdev earned 61 total points
ID: 17904381
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
 

Author Comment

by:zafridi
ID: 17915091
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now