Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Unanswered: Temptable performance slow after ASE12.5 - ASE15.5 migration

    I'm stuck...

    Last week I migrated a datawarehouse from an ASE12.5 server to a new ASE15.5 server. I did all the tuning and configuring as far as I can tell, but one particular procedure is very, very slow.
    On the old server it runs in approxmately 30 minutes, on the new erver it doesn't even finish in 24+ hours.
    The procedure uses temporary tables to aggregate data.As far as I have been able to troubleshoot, one of the update-statements on one of the temptables is taking way too long. What I have seen is that the tempdb was slowly filling up with .02 mb per second. No idea why, but I suspect this is the step that is causing the big delay.

    What configuration parameters do I need to check when it comes to temptables? What performance and tuning should I re-check and/or change?
    I'm getting a bit desperate...
    I'm not crazy, I'm an aeroplane!

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,

    Force recompile that SP.
    Check the plan for that proc. Post it here pls.

    Also if is Datawarehouse, consider set the optimization goal to 'allrows_dss'.
    Check the statements you have with order by. ( probably the index is not ideal )
    When were statistics updated for the indexes of the tables being used? (How big are the tables? Post here too the sp_spaceused for them)

    ...etc.

    Lets see what info you provide.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Martijnvs View Post
    I did all the tuning and configuring as far as I can tell
    Including update index statistics on all tables after migration?
    Quote Originally Posted by Martijnvs View Post
    one of the update-statements on one of the temptables is taking way too long
    Is the update doing a join to other tables that might be doing a table scan?
    Quote Originally Posted by Martijnvs View Post
    What I have seen is that the tempdb was slowly filling up with .02 mb per second. No idea why, but I suspect this is the step that is causing the big delay.
    Maybe not enough space on the page for new data, try creating tempdb table big enough to hold the aditional data e.g. create char(20) not null instead of varchar(20) null.

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Thank you for trying to help me out, Caterrunas, Pdreyer.

    I have contacted the developer of the procedure, and he is looking into it. Apparently other clients that use similar procedures also have had issues with the switch from ASE12.5 to ASE15.5.
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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