Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014
    Posts
    1

    Post Unanswered: Performance issue in Insert Select(Sybase)

    Hi members,

    I have a sp with number of Insert-select which is increasing the execution time of the sp. The approximate no. of records in the tables is around 10 million. Please suggest me the better way to improve the performance of following query. Please let me know either insert-select will be faster or select * into t2 from t1 will be faster.

    insert #cr2_bondtemp
    select
    isnull(secId, 0) secId, ID_CUSIP, ID_SEDOL1, ID_ISIN, SECURITY_PRICE, null, BASIC_SPREAD, ISSUER, SECURITY_DES,
    upper(MARKET_SECTOR_DES) + 'BOND',
    case when CPN_TYP = 'DEFAULTED' then 'D'
    when RTG_MOODY = 'N.A.' then 'NA'
    else RTG_MOODY
    end,
    case when CPN_TYP = 'DEFAULTED' then 'D'
    when RTG_SP = 'N.A.' then 'NA'
    else RTG_SP
    end,
    null, TICKER, CPN,
    case when MATURITY is null and IS_PERPETUAL = 'Y' and NEXT_PUT_DATE is not null
    then NEXT_PUT_DATE
    when MATURITY is null and IS_PERPETUAL = 'Y' and NEXT_PUT_DATE is null and FINAL_MATURITY is not null
    then FINAL_MATURITY
    when MATURITY is null and IS_PERPETUAL = 'Y' and NEXT_PUT_DATE is null and FINAL_MATURITY is null
    then dateadd(yy, 50, getdate())
    else MATURITY
    end,
    CRNCY, COUNTRY_GUARANTOR, PRICING_SOURCE, null, AMT_OUTSTANDING, AMT_ISSUED, null, COUNRY_BUSINESS_RISK, ID_BB_UNIQUE, ID_BB_COMPANY, null, null, S_SECURITY_SQ_NO, LEVEL3,
    NEXT_PUT_DATE, null, CNTRY_ISSUE_ISO, TRADE_CRNCY, null, LEVEL2, LEVEL4, ISSUE_DT, CPN_FREQ, null, null, DAY_CNT, CPN_TYP, FIRST_CPN_DT, FLOATER, BASIC_SPREAD, null --Added for artf512786
    from Data_ora(index id_bb_unique_idx) where status = 1 and secId is null and ID_BB_UNIQUE is not null
    UNION
    select
    secId, ID_CUSIP, ID_SEDOL1, ID_ISIN, SECURITY_PRICE, null, BASIC_SPREAD, ISSUER, SECURITY_DES,
    upper(MARKET_SECTOR_DES) + 'BOND',
    case when CPN_TYP = 'DEFAULTED' then 'D'
    when RTG_MOODY = 'N.A.' then 'NA'
    else RTG_MOODY
    end,
    case when CPN_TYP = 'DEFAULTED' then 'D'
    when RTG_SP = 'N.A.' then 'NA'
    else RTG_SP
    end,
    null, TICKER, CPN,
    case when MATURITY is null and IS_PERPETUAL = 'Y' and NEXT_PUT_DATE is not null
    then NEXT_PUT_DATE
    when MATURITY is null and IS_PERPETUAL = 'Y' and NEXT_PUT_DATE is null and FINAL_MATURITY is not null
    then FINAL_MATURITY
    when MATURITY is null and IS_PERPETUAL = 'Y' and NEXT_PUT_DATE is null and FINAL_MATURITY is null
    then dateadd(yy, 50, getdate())
    else MATURITY
    end,
    CRNCY, COUNTRY_GUARANTOR, PRICING_SOURCE, null, AMT_OUTSTANDING, AMT_ISSUED, null,
    COUNRY_BUSINESS_RISK, ID_BB_UNIQUE, ID_BB_COMPANY, null, null, S_SECURITY_SQ_NO, LEVEL3, NEXT_PUT_DATE, null, CNTRY_ISSUE_ISO, TRADE_CRNCY, null, LEVEL2, LEVEL4,
    ISSUE_DT, CPN_FREQ, null, null, DAY_CNT, CPN_TYP, FIRST_CPN_DT, FLOATER, BASIC_SPREAD, null --Added for artf512786
    from Data_ora(index id_bb_unique_idx) where secId > 0 and ID_BB_UNIQUE is not null


    Thank you!

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Select into will be faster but then you might end up with lock contention on the system tables.
    Do you really need a temp table? Do you really need a union? It seems you force an index on ID_BB_UNIQUE. So the other columns doesn’t really matter for index selection? Maybe use where ID_BB_UNIQUE is not null and (status = 1 or secId > 0)

Tags for this Thread

Posting Permissions

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