Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2013
    Posts
    6

    Unanswered: SQL performance varies wildly

    Hi,

    I have a batch program that runs daily that contains a piece of SQL which is usually extremely performant. Most days it takes milliseconds but occasionally it takes 20 seconds - this is causing the program to go from taking 5 mins to run to 3 hours. Here's evidence of the time it takes from our application in both situations.

    08:00:14:420399 PID=017143 IfOdbcStatement(14/7529984/0): --- Call SQLParamOptions result: 0 SELECT UNIQUEREF FROM ( SELECT U
    08:00:14:505068 PID=017143 IfOdbcStatement(14/7529984/0): --- Call SQLExecute result: 0 SELECT UNIQUEREF FROM ( SELECT U



    08:04:53:933486 PID=014843 IfOdbcStatement(14/7529984/0): --- Call SQLParamOptions result: 0 SELECT UNIQUEREF FROM ( SELECT U
    08:05:12:070284 PID=014843 IfOdbcStatement(14/7529984/0): --- Call SQLExecute result: 0 SELECT UNIQUEREF FROM ( SELECT U


    Any ideas what might be causing this? One of the tables is an MQT that is recreated (drop,create,refresh) daily, 3 hours before the job runs - I suspect it but have no evidence for it. Is there anything I can glean from using snapshots that I can schedule for the time the job runs?

    Using db2 v.10

    Thanks

  2. #2
    Join Date
    Aug 2013
    Posts
    6

    SQL performance is great but sometimes sucks.

    Hi,

    I have a batch program that runs daily that contains a piece of SQL which is usually extremely performant. Most days it takes milliseconds but occasionally it takes 20 seconds - this is causing the program to go from taking 5 mins to run to 3 hours. Here's evidence of the time it takes from our application in both situations.

    08:00:14:420399 PID=017143 IfOdbcStatement(14/7529984/0): --- Call SQLParamOptions result: 0 SELECT UNIQUEREF FROM ( SELECT U
    08:00:14:505068 PID=017143 IfOdbcStatement(14/7529984/0): --- Call SQLExecute result: 0 SELECT UNIQUEREF FROM ( SELECT U



    08:04:53:933486 PID=014843 IfOdbcStatement(14/7529984/0): --- Call SQLParamOptions result: 0 SELECT UNIQUEREF FROM ( SELECT U
    08:05:12:070284 PID=014843 IfOdbcStatement(14/7529984/0): --- Call SQLExecute result: 0 SELECT UNIQUEREF FROM ( SELECT U


    Any ideas what might be causing this? One of the tables is an MQT that is recreated (drop,create,refresh) daily, 3 hours before the job runs - I suspect it but have no evidence for it. Is there anything I can glean from using snapshots that I can schedule for the time the job runs?

    Using db2 v.10

    Thanks

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Can't say with the limited info provided here. Is the SQL written directly against the MQT or is it against the base table and most of the time you are making use of the MQT? I suspect your SQL is being run dynamically, but can't say for sure. Did it need to be rebound after the drop/recreate?
    Dave

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by legendb View Post
    Hi,
    Most days it takes milliseconds but occasionally it takes 20 seconds
    It might wait for a lock
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Aug 2013
    Posts
    6
    Hi Thanks, for the replies.

    The SQL joins the MQT with 3 other tables as part of the sql - the 3 tables are also part of the SQL that is used as the basis of the MQT. It's refreshed twice per day. Once before the job runs and once after it.

    I don't think it's waiting for a lock as it executes repeatedly over and over again a few hundred times per batch run.

    Thanks

  6. #6
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1
    if its the query itself having inconsistent elapsed times, then I would suspect statistics please keep the statistics constant don't update them daily unless you know the data characteristics have changed significantly.

    Also - is your query dynamic (compiled every time) - in such cases I have varied behavior for given where clause values and how the access plan turns out to be.

    If its parametrized (compile once) then you should see consistent run-times (slow or fast)
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

  7. #7
    Join Date
    Aug 2013
    Posts
    6
    The MQT keeps totals and counters for comparing amounts that change daily and the job uses the MQT to see which amounts don't match hence it needs to refresh every day. I'm not sure I can go in to much more detail as it's a customers production system.

    The SQL is defined with bound parameters in the C++ source code, is that what you mean by parametrized?

  8. #8
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    You can use activity monitor and EXPLAIN_FROM_ACTIVITY to see the execution plan used for particular run. You can compare execution plan from slow run day with execution plan from normal run day and find out what is causing slow down. If possible, setup single execution of query outside normal batch process and capture plan details daily for that execution. If activity monitor is used for whole batch process, it might slow down batch process.

    Refer to following link for details on using EXPLAIN_FROM_ACTIVITY procedure.

    http://www.ibm.com/developerworks/da...6db2queryperf/

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
  •