Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003

    Unanswered: Stored Procedure Performance

    SQL Stored procedures seem to give very poor performance on my system. For example, if I write something very simple such as:

    DECLARE i integer;
    DECLARE v_booking_reference character(10);

    SET v_booking_reference = 'XYZ';
    SET i = 0;
    while i <= 100000
    SET v_booking_reference = RTRIM(v_booking_reference);
    SET i = i + 1;
    end while;

    it takes nearly 3 minutes to execute. There is no SQL in this - it is just manipulation of variables. Similar things written in C or the Oracle PL/SQL language execute in milli-seconds.

    Is there something wrong with the set-up of the machine ? Do I have to compile the procedures to get them to work efficiently or something ? Or are SQL procedures just very slow ? The DB2 version is 7.1.

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    I do not know what your problem is.

    I created a SP with your code, verbatim, into SPB.
    I ran the SP (in SPB) and it completed in a few seconds on
    a couple of successive runs. I also ran it from CLP and
    it takes the same amount of time.

    My environment:
    DB2 V 7.2 FP 7 Linux

    SQL procedures are not necessarily slow, I have several complicated ones that run very fast.

    Sorry I could not be of much help.


  3. #3
    Join Date
    Jul 2001
    What are you trying to do using this procedure? On our m/c , It is taking less than 1 min. May be some setting is wrong

  4. #4
    Join Date
    Feb 2003
    The procedure was just a fragment from an SP which is taking about 12 hours to run in total. One of the things it was doing was the rtrim on data it had fetched from a cursor. I decided to isolate this bit of the code just see how long it took to run. I expected a few seconds and was amazed at how slow even that seemed to be.

    Your replies do indicate that there must be something wrong with the set-up of my system, so I'll try to follow it up with IBM.

Posting Permissions

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