Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Optimize a stored procedure

Posted on 2006-11-08
5
Medium Priority
?
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 29

Assisted Solution

by:QPR
QPR earned 183 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 201 total points
ID: 17900837
Does each select return a disctinct row set? If so, union all would be faster than simply union.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 183 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 183 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

610 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