Solved

Optimize a stored procedure

Posted on 2006-11-08
5
216 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:
Scott Pletcher 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

829 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