Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: Long running query

    Please suggest me on performance tuning of below query of "INSERT INTO rpt_mar_agr_feed" -- This is taking more than 40mins:

    select getdate()
    go
    SET FORCEPLAN ON
    go
    SELECT DISTINCT
    sai.agreement_id,
    agreement_type
    INTO #rpt_agr_type
    FROM std_agr_instruments sai,
    std_agr_types sat
    WHERE sai.agreement_id = sat.agreement_id
    AND sai.authorized = 1
    AND sat.authorized = 1
    AND (
    sai.instrument_group = 900003000 -- All FX Products
    OR sai.instrument IN (2000000, 4000000) -- FX and FX Options
    OR sai.sub_instrument IN
    (
    SELECT sub_instrument
    FROM std_sub_instruments
    WHERE instrument IN (2000000, 4000000)
    )
    )
    go
    select getdate()
    go

    CREATE INDEX #rpt_agr_type_ndx ON #rpt_agr_type(agreement_id,agreement_type)
    go
    select getdate()
    go

    INSERT INTO rpt_mar_agr_feed
    (
    agreement_id
    , citicorp_unit_gfcid
    , gfcid
    , sub_instrument
    )
    SELECT DISTINCT
    sai.agreement_id,
    sai.citicorp_unit_gfcid,
    sai.gfcid,
    sai.sub_instrument
    FROM agreement_map sai,
    #rpt_agr_type rfxcs
    WHERE sai.agreement_id = rfxcs.agreement_id
    AND sai.application_type = "CREDIT"
    AND rfxcs.agreement_type = "MARGIN"

    go
    select getdate()
    go

    --- select count(1) from #rpt_agr_type -- 27000 Rows took only 3mins.
    --- select count(1) from agreement_map -- 36 million rows Rows
    --- Index available on : agreement_id of agreement_map

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'd need more info to do this right (table DDL with indexes, timing figures etc) but this is my first wild stab in the dark:
    Code:
    INSERT INTO rpt_mar_agr_feed 
           ( agreement_id, citicorp_unit_gfcid, gfcid, sub_instrument )
    SELECT distinct am.agreement_id, am.citicorp_unit_gfcid, 
           am.gfcid, am.sub_instrument
    FROM   std_agr_instruments sai,
           std_agr_types sat,
           agreement_map am
    WHERE  sai.agreement_id = sat.agreement_id
           AND sai.authorized = 1
           AND sat.authorized = 1
           AND (
               sai.instrument_group = 900003000 -- All FX Products
               OR sai.instrument IN (2000000, 4000000) -- FX and FX Options
               OR exists( 
                   SELECT 1
                   FROM   std_sub_instruments si
                   WHERE  si.sub_instrument = sai.sub_instrument
                          and si. instrument IN (2000000, 4000000) )
           )
           and am.agreement_id = sai.agreement_id
           AND am.application_type = "CREDIT"
           AND sai.agreement_type = "MARGIN"
    Mike

  3. #3
    Join Date
    Jul 2009
    Posts
    7
    Thanks Mike.
    Insert into #rpt_agr_type took only 3mins. -- No. of rows : 50 thousand

    point1:
    Agreement_map contain 3 indexes: -- NO. of rows : 30million
    1)gfcid, citicorp_unit_gfcid, sub_instrument
    2)gfcid, agreement_id
    3) agreement_id

    Point2) -- created index online like below
    CREATE INDEX rfxcs1 ON #rpt_agr_type(agreement_id,agreement_type)


    I am not sure why below query takes long time:
    INSERT INTO rpt_mar_agr_feed
    (
    agreement_id
    , citicorp_unit_gfcid
    , gfcid
    , sub_instrument
    )
    SELECT DISTINCT
    sai.agreement_id,
    sai.citicorp_unit_gfcid,
    sai.gfcid,
    sai.sub_instrument
    FROM agreement_map sai,
    #rpt_agr_type rfxcs
    WHERE sai.agreement_id = rfxcs.agreement_id
    AND application_type = "CREDIT"
    AND rfxcs.agreement_type = "MARGIN"

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Did my query work at all (obviously I can't test it on my end)?
    You can just run it as a select statement and see if it produces the same rows as your original query.
    If it takes longer than 10 min then kill it.
    If it did - was it faster. If it didn't - was there an error message?
    Can you try it without the distinct statement - does it produce the same amount of rows and is it faster?

    You gave details on the agreement_map table but not the std_agr_instruments and std_agr_types tables.

    Is the distinct necessary? this will certainly slow things down.

  5. #5
    Join Date
    Jul 2009
    Posts
    7
    Here Attached full procedure:
    -----------------------------

    DECLARE @error_text varchar(45)
    ,@cmtm float
    ,@this_proc varchar(30)

    SELECT @call = ISNULL(@call,"Y")
    ,@this_proc = "mar_summary_ext " + CONVERT(VARCHAR,@report_id) + "," + ISNULL(@call,"Y") + ":"


    /* Delete previous cob details */
    TRUNCATE TABLE rpt_mar_summary
    TRUNCATE TABLE rpt_set_risk_netting
    TRUNCATE TABLE rpt_mar_agr_feed

    DROP INDEX #rpt_agr_type.rfxcs1

    SELECT @error_text = @this_proc + "INS rpt_mar_summary"
    INSERT rpt_mar_summary
    (agreement_id
    ,customer_id
    ,customer_desc
    ,margin_type
    ,call_frequency
    ,next_value_dt)
    SELECT a.agreement_id
    ,a.customer_id
    ,a.customer_desc
    ,a.margin_direction
    ,a.coll_call_frequency
    ,convert(char(12),a.next_margin_call_dt,106)
    FROM rpt_mar_agreements a
    WHERE a.agreement_status = 'ACTIVE'
    AND a.report_id = @report_id
    AND (
    -- Projected Margin Call Agreement details
    (@call = 'Y'
    AND EXISTS
    (SELECT 1
    FROM mar_call_summary s
    WHERE s.agreement_id = a.agreement_id
    AND ( s.cpty_margin_call > 0
    OR s.citi_margin_call > 0
    OR s.cpty_var_margin_var != 0
    OR s.citi_var_margin_var != 0)
    )
    )

    OR -- NO Margin Call Agreement details - Agreement within limits
    (@call NOT IN ('Y','A')
    AND NOT EXISTS
    (SELECT 1
    FROM mar_call_summary s
    WHERE s.agreement_id = a.agreement_id
    AND ( s.cpty_margin_call > 0
    OR s.citi_margin_call > 0
    OR s.cpty_var_margin_var != 0
    OR s.citi_var_margin_var != 0)
    )
    )

    OR -- "All" required by margin_prime_broker.ksh's extract; no "exists/not exists" test as all records wanted
    @call = 'A'
    )
    ORDER BY customer_desc, agreement_id
    EXEC lib_error_filter @@error, null, @error_text, @@rowcount


    SELECT @error_text = @this_proc + "UPD amounts"
    UPDATE rpt_mar_summary
    SET base_ccy = c.agrmt_base_ccy,
    cp_call_amount = convert(numeric,c.cpty_min_call_amount),
    cp_noncall_amount = convert(numeric,c.cpty_min_noncall_amount),
    cbk_call_amount = convert(numeric,c.citi_min_call_amount),
    cbk_noncall_amount = convert(numeric,c.citi_min_noncall_amount)
    FROM std_agr_gen_collateral c,
    rpt_mar_summary r
    WHERE c.agreement_id = r.agreement_id
    and c.authorized = 1
    EXEC lib_error_filter @@error, null, @error_text, @@rowcount


    SELECT @error_text = @this_proc + "UPD base_ccy"
    UPDATE rpt_mar_summary
    SET base_ccy = 'USD'
    WHERE base_ccy = NULL
    EXEC lib_error_filter @@error, null, @error_text, @@rowcount


    SELECT @error_text = @this_proc + "UPD init+call"
    UPDATE rpt_mar_summary
    SET cp_initial = i.cpty_init_margin,
    cbk_initial = i.citi_init_margin,
    cp_collateral_held = s.cpty_im_coll + s.cpty_vm_coll,
    cbk_collateral_held = s.citi_im_coll + s.citi_vm_coll,
    cp_collateral_var = s.cpty_init_margin_var + s.cpty_var_margin_var,
    cbk_collateral_var = s.citi_init_margin_var + s.citi_var_margin_var,
    cp_threshold = convert(numeric,v.cpty_threshold_amount),
    cbk_threshold = convert(numeric,v.citi_threshold_amount),
    cp_proj_margin = s.cpty_margin_call,
    cbk_proj_margin = s.citi_margin_call
    FROM rpt_mar_summary r,
    mar_call_summary s,
    mar_im_summary i,
    mar_vm_summary v
    WHERE s.agreement_id = r.agreement_id
    AND s.agreement_id *= i.agreement_id
    AND s.agreement_id *= v.agreement_id
    EXEC lib_error_filter @@error, null, @error_text, @@rowcount


    IF @call = 'Y'
    BEGIN
    SELECT @error_text = @this_proc + "UPD cp_proj_margin"
    UPDATE rpt_mar_summary
    SET cp_proj_margin = s.cpty_var_margin_var
    FROM rpt_mar_summary r,
    mar_call_summary s
    WHERE s.agreement_id = r.agreement_id
    AND s.cpty_margin_call = 0
    EXEC lib_error_filter @@error, null, @error_text, @@rowcount

    SELECT @error_text = @this_proc + "UPD cbk_proj_margin"
    UPDATE rpt_mar_summary
    SET cbk_proj_margin = s.citi_var_margin_var
    FROM rpt_mar_summary r,
    mar_call_summary s
    WHERE s.agreement_id = r.agreement_id
    AND s.citi_margin_call = 0
    EXEC lib_error_filter @@error, null, @error_text, @@rowcount

    END


    /* Use the cmtm from transaction details table */
    SELECT @error_text = @this_proc + "UPD trans_cmtm"
    UPDATE rpt_mar_summary
    SET trans_cmtm = (SELECT sum(d.cmtm)
    FROM mar_rpt_txn_details d
    WHERE d.agreement_id = r.agreement_id
    GROUP BY d.agreement_id)
    FROM rpt_mar_summary r
    EXEC lib_error_filter @@error, null, @error_text, @@rowcount


    SELECT @error_text = @this_proc + "UPD fx rates"
    UPDATE rpt_mar_summary
    SET cp_initial = convert(numeric,cp_initial / fx_rate),
    cbk_initial = convert(numeric,cbk_initial / fx_rate),
    cp_collateral_held = convert(numeric,cp_collateral_held / fx_rate),
    cbk_collateral_held = convert(numeric,cbk_collateral_held / fx_rate),
    cp_collateral_var = convert(numeric,cp_collateral_var / fx_rate),
    cbk_collateral_var = convert(numeric,cbk_collateral_var / fx_rate),
    cp_proj_margin = convert(numeric,cp_proj_margin / fx_rate),
    cbk_proj_margin = convert(numeric,cbk_proj_margin / fx_rate),
    trans_cmtm = convert(numeric,trans_cmtm / fx_rate)
    FROM rpt_mar_summary r,
    fx_rates f
    WHERE f.ccy = r.base_ccy
    AND r.base_ccy != 'USD'
    EXEC lib_error_filter @@error, null, @error_text, @@rowcount

    IF @report_id = 1
    BEGIN
    TRUNCATE TABLE rpt_mar_pb_summary

    INSERT INTO rpt_mar_pb_summary
    (
    gfcid_description
    ,agreement_id
    ,gfcid
    ,margin_type
    ,base_ccy
    ,call_frequency
    ,next_value_date
    ,cp_transaction_mtm
    ,cp_initial_required
    ,cp_collateral_held
    ,cp_collateral_variance
    ,cp_threshold
    ,cp_minimum_call_val
    ,cp_minimum_call_nonval
    ,citibank_initial_required
    ,citibank_collateral_held
    ,citibank_collateral_variance
    ,citibank_threshold
    ,citibank_minimum_call_val
    ,citibank_minimum_call_nonval
    ,cp_projected_margin_call
    ,citibank_projected_margin_call
    )
    SELECT
    customer_desc
    ,agreement_id
    ,customer_id
    ,margin_type
    ,base_ccy
    ,call_frequency
    ,CONVERT(CHAR(8), CONVERT(DATETIME,next_value_dt),01)
    ,trans_cmtm
    ,cp_initial
    ,cp_collateral_held
    ,cp_collateral_var
    ,cp_threshold
    ,cp_call_amount
    ,cp_noncall_amount
    ,cbk_initial
    ,cbk_collateral_held
    ,cbk_collateral_var
    ,cbk_threshold
    ,cbk_call_amount
    ,cbk_noncall_amount
    ,cp_proj_margin
    ,cbk_proj_margin
    FROM rpt_mar_summary
    END

    BEGIN


    SELECT DISTINCT
    sai.agreement_id,
    agreement_type
    INTO #rpt_agr_type
    FROM std_agr_instruments sai,
    std_agr_types sat
    WHERE sai.agreement_id = sat.agreement_id
    AND sai.authorized = 1
    AND sat.authorized = 1
    AND (
    sai.instrument_group = 900003000 -- All FX Products
    OR sai.instrument IN (2000000, 4000000) -- FX and FX Options
    OR sai.sub_instrument IN
    (
    SELECT sub_instrument
    FROM std_sub_instruments
    WHERE instrument IN (2000000, 4000000)
    )
    )

    CREATE INDEX rfxcs1 ON #rpt_agr_type(agreement_id,agreement_type)

    INSERT rpt_set_risk_netting
    (
    agreement_id
    , citicorp_unit_gfcid
    , gfcid
    )
    SELECT DISTINCT
    sai.agreement_id,
    sai.citicorp_unit_gfcid,
    sai.gfcid
    FROM agreement_map sai,
    #rpt_agr_type rfxcs
    WHERE sai.agreement_id = rfxcs.agreement_id
    AND application_type = "CREDIT"
    AND rfxcs.agreement_type = "NET"
    AND sr_netting = "Y"

    INSERT INTO rpt_mar_agr_feed
    (
    agreement_id
    , citicorp_unit_gfcid
    , gfcid
    , sub_instrument
    )
    SELECT DISTINCT
    sai.agreement_id,
    sai.citicorp_unit_gfcid,
    sai.gfcid,
    sai.sub_instrument
    FROM agreement_map sai,
    #rpt_agr_type rfxcs
    WHERE sai.agreement_id = rfxcs.agreement_id
    AND application_type = "CREDIT"
    AND rfxcs.agreement_type = "MARGIN"

    EXEC lib_error_filter @@error, null, @error_text, @@rowcount
    END

    GO








    ---------------- Questions:
    INSERT INTO rpt_mar_agr_feed
    (
    agreement_id
    , citicorp_unit_gfcid
    , gfcid
    , sub_instrument
    )
    SELECT DISTINCT
    sai.agreement_id,
    sai.citicorp_unit_gfcid,
    sai.gfcid,
    sai.sub_instrument
    FROM agreement_map sai,
    #rpt_agr_type rfxcs
    WHERE sai.agreement_id = rfxcs.agreement_id
    AND application_type = "CREDIT"
    AND rfxcs.agreement_type = "MARGIN"

    Why Above procedure take more than 40,mins?

  6. #6
    Join Date
    Jul 2009
    Posts
    7
    -- Mike your modified query is perfect. that shows correct result, but we need to run simiilar query twice so broken into 2 parts..

    Why below query takes more time:
    1) Is it not using online qry.

    ---------------- Questions:
    INSERT INTO rpt_mar_agr_feed
    (
    agreement_id
    , citicorp_unit_gfcid
    , gfcid
    , sub_instrument
    )
    SELECT DISTINCT
    sai.agreement_id,
    sai.citicorp_unit_gfcid,
    sai.gfcid,
    sai.sub_instrument
    FROM agreement_map sai,
    #rpt_agr_type rfxcs
    WHERE sai.agreement_id = rfxcs.agreement_id
    AND application_type = "CREDIT"
    AND rfxcs.agreement_type = "MARGIN"

    Why Above procedure take more than 40,mins?

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    INSERT INTO rpt_mar_agr_feed
    (
    agreement_id
    , citicorp_unit_gfcid
    , gfcid
    , sub_instrument
    )
    SELECT DISTINCT
    sai.agreement_id,
    sai.citicorp_unit_gfcid,
    sai.gfcid,
    sai.sub_instrument
    FROM agreement_map sai,
    #rpt_agr_type rfxcs
    WHERE sai.agreement_id = rfxcs.agreement_id
    AND application_type = "CREDIT"
    AND rfxcs.agreement_type = "MARGIN"

    Why Above procedure take more than 40,mins?
    At a guess I'd say the following:
    • the distinct requires it to order 30m rows before inserting them into the table. If there are no duplicates then this is pointless.
    • Using the #temp table inside the proc means the proc won't know about the index you've created while it builds up the query plan.
    • You're splitting your query into two parts - building a temp table then using that temp table in another query - this is wasted effort here.
    Was my query much faster or just a bit? How did removing the distinct affect the time?
    Last edited by mike_bike_kite; 07-27-09 at 11:59.

  8. #8
    Join Date
    Jul 2009
    Posts
    7
    Thanks mike for suggestions.

    YOur query results many duplicate values . it takes more than 10mins.

    Here :
    std_agr_instruments -- 10,000 rows
    std_agr_types -- 8,000 rows
    agreement_map -- 36 million rows.

    I belive the broken temp table #rpt_agr_type should have perment table and then will create index.. that might be faster.. i will try this tomorrow

    Please advise me your suggestions.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    That approach will work fine. It's possible to use temp tables but it's much more difficult using the indexes within a stored procedure. Good luck.

    Mike

  10. #10
    Join Date
    Jul 2009
    Posts
    7
    Thanks Mike. it reduced to 6mins after creating permnent table.
    Could we use hints like "SET FORCEPLAN ON".. or any other to reduce bit more.

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The optimiser is usually pretty good at picking the best plan for a given request. Often the problem is we're simply requesting the wrong thing. Using "SET FORCEPLAN ON" can be OK if you're very strong at performance tuning (ie better than the optimiser) but personally I rarely use it and then only as a last resort because it creates all sorts of maintenance headaches.

    I'd try and find out why you have duplicates in your query (you are using distinct). If the duplicates can be avoided then you won't need the distinct which will hopefully save time in trying to order 30m rows. We also didn't see the create table and index statements so it might be possible to speed things up further there.

    I personally would leave any data in the work table after you have used it and then delete it just before adding the new data. This will mean that statistics can be used with real data.

  12. #12
    Join Date
    Aug 2009
    Posts
    2
    The best that go together.


    gratuit pour mobile sonnerie portable mp3 - Sonnerie portable MP3 est en effet un pouvoir de l'innovation technologique. Êtes-vous familier avec cette sonnerie portable?gratuit pour mobile sonnerie portable mp3

  13. #13
    Join Date
    Jul 2009
    Posts
    7

    Long running query.

    Query:
    set showplan on
    set noexec off
    SET FORCEPLAN ON

    INSERT rpt_mar_agr_feed
    SELECT sai.agreement_id
    ,sai.citicorp_unit_gfcid
    ,sai.gfcid
    ,sai.sub_instrument
    FROM #rpt_agr_type rfxcs
    ,agreement_map sai
    WHERE sai.agreement_id = rfxcs.agreement_id
    AND sai.agreement_type = rfxcs.agreement_type
    AND application_type = "CREDIT"
    AND rfxcs.agreement_type = "MARGIN"

    Queryplan:

    STEP 1
    The type of query is INSERT.
    The update mode is direct.

    FROM TABLE
    #rpt_agr_type
    rfxcs
    Nested iteration.
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

    FROM TABLE
    agreement_map
    sai
    Nested iteration.
    Index : ndx_322554745
    Forward scan.
    Positioning by key.
    Keys are:
    agreement_id ASC
    Using I/O Size 16 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    rpt_mar_agr_feed
    Using I/O Size 2 Kbytes for data pages.


    The logs shows:
    860 credit_dbo Controls acco GetMpbSum margin_prime_br 11338 itg_credit runnable INSERT mar_summary_extract 38 293

    Here log says "runnable" what is meaning of this.. is it waiting for something ..

Posting Permissions

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