SQL Stored procedures seem to give very poor performance on my system. For example, if I write something very simple such as:
CREATE PROCEDURE TEST_TRIM()
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;
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.
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.