Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    136

    Unanswered: insert into session table is very slow in SP only

    Hi,
    We are doing insert into session table on based select query.

    There are only two statements in the SP :
    1. Declare global TEMPORARY table session.xxx..... (creating session table)
    2 Insert into this table with select statement(insert into table ...select ...).

    SP takes 15 minutes to execute while when I run both statements on command prompt sequentially, takes only 50 seconds.

    Separately query also takes only 50 seconds to execute.

    I rebind packages but no improvement.

    Please provide your comments on this issue.

    Thanks.
    Regards
    Pawan Kumar

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is not enough information to help you. Please supply your DB2 version and OS. Also include the source for the Stored Procedure and the statements you are executing. Please provide the access plans for both.

    Andy

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Pawan,
    Even though you didn't give us enough detail to help you, I am going out on a limb with a guess.
    Does the statement in your stored proc have parameters and the query you run in command prompt has literals?

  4. #4
    Join Date
    Mar 2008
    Posts
    136
    Thanks for reply. I can not share code of stored procedure and sql.

    DB2 9.7 FP 7 and Linux.

    Yes we are passing two parameters in stored procedure and same values are being used in sql when run in command prompt.

    Any more guesses to be happen this issue?
    Regards
    Pawan Kumar

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You are getting a different access path between the procedure and the command prompt running of the query. Explain the query with and without parameter markers and you will be able to see the difference. It may be as simple as collecting statistics on the tables in the select clause. Or you may have to play with the SQL a bit.
    There should be a way for you to share your SQL with us. change column names, mask it some way. I am sure you are not running anything that quite a few of us haven't done already

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by ARWinner View Post
    There is not enough information to help you. Please supply your DB2 version and OS.
    and please specify how you defined your user- and system temp tablespaces (probably SMS) and info about the disks and filesystem used.
    Just this week there was a posting about db2-objects defined on ext3 disks..... not a lucky combination.

    http://www-01.ibm.com/support/docvie...CSSEPGG&mync=R
    Last edited by dr_te_z; 12-18-13 at 02:18. Reason: added link
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  7. #7
    Join Date
    Mar 2008
    Posts
    136
    when I used 'prepare' statement then it worked. now executing in less than minute.
    I took insert statement into a string and prepare statement that string and execute in sp..

    I need to dig why it became fast after making prepare...anyone has idea???

    Thanks All.
    Regards
    Pawan Kumar

Posting Permissions

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