Solved

Optimize a stored procedure

Posted on 2006-11-08
5
213 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

776 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