Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    18

    Unanswered: SP behaving strangely

    Hi All,
    I have a DB of 100Gigs.
    I have a SP which I came to knw frm developer that it is taking some more than 20 mins. Though when I just run the code frm the SP and replace the variables in the query with the values which I use in the executing the SP it completes in 2 mins.

    I have recomiled the SP, drop and recreated the same.
    I have updated the statistics of entire database checked whether the biggest table is having any defragmentation.

    Any clues on this weird behaviour.
    I have SQL Server 2000 with SP3.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    18

    Sp

    CREATE PROCEDURE holdings_summary_jay
    @addr_id char(8),
    @proc_date varchar(25),
    @sec_no char(12),
    @wi char(1),
    @sec_xtend varchar(10),
    @orderby varchar(100)



    AS
    --DECLARE @addr_id char(8)
    --DECLARE @proc_date as varchar(25)
    --DECLARE @sec_no as char(12)
    --DECLARE @wi as char(1)
    --DECLARE @sec_xtend as varchar(10)
    --DECLARE @orderby as varchar(100)



    --SELECT @addr_id = 'SHAWFIX'
    --SELECT @proc_date = '11/1/2002'
    --SELECT @sec_no = '5387599'
    --SELECT @sec_xtend = ' '
    --SELECT @wi = ' '
    --SELECT @orderby = 'br_acct'
    --drop table #tbl_fi_tot_mkt_val
    --drop table #tbl_sector_tot_mkt_val
    set NOCOUNT ON
    set transaction isolation level read uncommitted


    create table #tbl_fi_tot_mkt_val
    (
    br_acct char(8),
    fi_tot_mkt_val decimal(23,8)
    )

    insert into #tbl_fi_tot_mkt_val
    select
    adr.br_acct,
    sum(mkt_val)
    from
    addr_acct as adr
    left outer join name_addr as naa on ( adr.br_acct = naa.br_acct and adr.proc_date = naa.proc_date )
    left outer join holdings as hld on ( naa.br_acct = hld.br_acct and naa.proc_date = hld.proc_date )
    left outer join assets as ast on( hld.sec_no = ast.sec_no and hld.wi = ast.wi and hld.proc_date = ast.proc_date )
    left outer join sec_types as st on ( ast.sec_type = st.sec_type )
    where
    adr.addr_id = @addr_id and adr.proc_date = @proc_date AND
    naa.test_acct = 'N' and naa.comp_flag = 'P' AND
    hld.sec_xtend = @sec_xtend AND hld.row_type = 'S' AND st.primary_type in ( 'B', 'M')
    group by
    adr.br_acct



    create table #tbl_sector_tot_mkt_val
    (
    br_acct char(8),
    sector_tot_mkt_val decimal(23,8)
    )

    insert into #tbl_sector_tot_mkt_val
    select
    adr.br_acct,
    sum(mkt_val)

    from
    addr_acct as adr
    left outer join name_addr as naa on ( adr.br_acct = naa.br_acct and adr.proc_date = naa.proc_date and naa.test_acct = 'N' and naa.comp_flag = 'P')
    left outer join holdings as hld on ( naa.br_acct = hld.br_acct and naa.proc_date = hld.proc_date )
    left outer join assets as ast on( hld.sec_no = ast.sec_no and hld.wi = ast.wi and hld.proc_date = ast.proc_date )

    where
    adr.addr_id = @addr_id and adr.proc_date = @proc_date AND
    hld.sec_xtend = @sec_xtend AND hld.row_type = 'S' AND
    ast.sec_type in ( select sec_type from assets where sec_no = @sec_no and wi = @wi)
    group by adr.br_acct


    SELECT
    substring(asl.br_acct,1,3)+ '-' + substring(asl.br_acct,4,8) + '-' + asl.type_check as br_acct,
    naa.target_name as target_name,
    ts_indicator = CASE
    WHEN hld.sec_xtend = 'TS' OR hld.sec_xtend = 'TL' THEN 'TS'
    ELSE ' '
    END,
    hld.long_short_flag,
    hld.units as current_face,
    hld.orig_face_val,
    hld.accr_int,
    pct_fi_tot_mkt_val=CASE
    WHEN tbl_a.fi_tot_mkt_val = 0.0 Or tbl_a.fi_tot_mkt_val is null THEN 0.0
    ELSE 100*(hld.mkt_val / tbl_a.fi_tot_mkt_val)
    END,
    pct_sector_tot_mkt_val=CASE
    WHEN tbl_b.sector_tot_mkt_val = 0.0 Or tbl_b.sector_tot_mkt_val is null THEN 0.0
    ELSE 100*(hld.mkt_val / tbl_b.sector_tot_mkt_val)
    END,
    hld.mkt_val as mkt_val,
    hld.percent_tot_equity as percent_of_total,
    asl.seq_no,
    base_curr_id,
    ast.cur_dur as [current_duration],
    (ast.cur_dur * hld.percent_tot_equity)/100.0 as [duration_contribution],
    client_type_desc=CASE
    WHEN ct.client_type is not null THEN client_type_desc
    ELSE 'N/A'
    end,
    account_type=CASE
    WHEN buy_code = '5' THEN 'COD'
    WHEN naa.acct_type2 is null THEN 'Cash'
    WHEN naa.acct_type2 is not null THEN 'Margin'
    ELSE 'N/A'
    END,
    domestic_flag=CASE
    WHEN ctry.geo_type is null THEN 'N/A'
    WHEN ctry.geo_type ='D' THEN 'Domestic'
    ELSE 'Foreign'
    END,
    geo_desc=CASE
    WHEN ctry.geoalc is not null THEN rtrim(ctry.geo_desc)
    ELSE 'N/A'
    END,
    insider_desc=CASE
    WHEN ins.insider = 'N' or ins.insider is null THEN ' '
    WHEN ins.insider is not null THEN rtrim(ins.insider_desc)
    ELSE ' '
    END,
    inv_flag=CASE
    WHEN naa.inv_adv = 'Y' OR naa.inv_adv = 'N' OR naa.inv_adv = 'C' OR naa.inv_adv = 'P' OR naa.inv_adv = 'F' THEN naa.inv_adv
    ELSE ' N/A'
    END,
    disc_flag=CASE
    WHEN naa.disc = 'Y' THEN 'Yes'
    WHEN naa.disc = 'N' THEN 'No'
    ELSE 'N/A '
    END,
    tax=CASE
    WHEN naa.tax = 'Y' THEN 'Yes'
    WHEN naa.tax = 'N' THEN 'No'
    ELSE 'N/A '
    END,
    family_acct_no=CASE
    WHEN naa.family_acct_no is null THEN ' '
    ELSE naa.family_acct_no
    END
    FROM asl join name_addr as naa on ( asl.br_acct = naa.br_acct AND asl.proc_date = naa.proc_date)
    left outer join #tbl_sector_tot_mkt_val as tbl_b on ( naa.br_acct = tbl_b.br_acct )
    left outer join #tbl_fi_tot_mkt_val as tbl_a on ( naa.br_acct = tbl_a.br_acct )
    left outer join holdings as hld on ( naa.br_acct = hld.br_acct AND naa.proc_date = hld.proc_date )
    left outer join vw_assets as ast on( hld.sec_no = ast.sec_no AND hld.wi = ast.wi AND hld.proc_date = ast.proc_date )
    left outer join country as ctry on (naa.geo_code =ctry.geoalc)
    left outer join client_type as ct on (naa.client_type = ct.client_type)
    left outer join insider_desc as ins on ( naa.principal_insider_code = ins.insider)
    WHERE
    asl.addr_id = @addr_id AND asl.proc_date = @proc_date AND
    naa.comp_flag = 'P' AND naa.test_acct = 'N' AND
    hld.sec_no = @sec_no AND hld.wi = @wi AND hld.sec_xtend = @sec_xtend AND hld.row_type = 'S'
    ORDER BY
    asl.seq_no


    running only the code completes in 1min 50 secs while executing the SP with the same parameters goes for a toss

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What I would do would be to use SQL Profiler and run a trace, the run each method....it will tell you everything you want...

    And I don't know if you want to modify this, but, you're going to the well 3 times.....and Read Uncommitted?

    For Finacial reporting?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •