Update takes forever, Looking for something to speed it up!!!

[GKramer]

Hi,

Can someone help me to speed up an UPDATE on a 800 column table???

One of my colleagues asked me to create a stored proc ordering some answer-results in a right way. (Each session can contain one ore more tests, and for each test I have to sort the answers (1 to 5) in a column, the max amount of questions in one test is 800. This results in a table with 800 columns!)
Flow; Inserting a row containing the “session” and “module” (test)
Updating each column on this row.
I use the following tables TEST02 as source (sessions, modules, answers) and TEST03_REP as result table (800 columns).
I use the following Proc to execute the dynamic update;
.
CREATE OR REPLACE PROCEDURE EXEC_SQL(P_STATEMENT IN VARCHAR2)
IS
V_CURSOR INTEGER;
V_ROWS INTEGER;
BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(V_CURSOR,P_STATEMENT,DBMS_SQL.NATIVE);
V_ROWS := DBMS_SQL.EXECUTE(V_CURSOR);
DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
END;
.
The Proc I work with (which concerns me about speed) is the following
.
create or replace procedure vultest02_rep as
--
cursor c_een is
select distinct( session_id||module_id ) as ident,
module_id,
session_id
from test02
where session_id in (302,304,305) ;
--
r_c_een c_een%rowtype;
--
cursor c_twee is
select 'ANSW'||item as item,
answer
from test02
where session_id||module_id = to_char(r_c_een.ident)
order by Item ;
--
r_c_twee c_twee%rowtype;
--
v_sql_string varchar(3000);
--
begin
open c_een;
fetch c_een into r_c_een;
While c_een%found loop
insert into test03_rep ( ses, mod ) values ( to_char(r_c_een.session_id),to_char(r_c_een.module_id) ) ;
commit;
--
open c_twee;
fetch c_twee into r_c_twee;
While c_twee%found loop
--
v_sql_string := 'update test03_rep set '||r_c_twee.item||' = '||r_c_twee.answer||' where ses = '||r_c_een.session_id||' and mod = '||r_c_een.module_id ;
exec_sql ( v_sql_string );
commit;
fetch c_twee into r_c_twee;
end loop;
close c_twee;
commit;
--
fetch c_een into r_c_een;
end loop;
close c_een;
end;

WHO can help me out SPEEDING it up.
Regards Gkramer.

[abhaysk]

Commit At the end...instead of commiting when every update is executed for every Nth column.

[GKramer]

Abbaysk,
Thanks for the reply but after removing the commit on the second cursor ( c_twee ) it sill takes about 8 minutes to handle 9 rows !!!! and I have to to about 1500 (!).
any other suggestions???
GKramer

[abhaysk]

I suspect if the table on which Update is issued is too big...and has many indexes and possibly too much fragmented and so mny other reasons...

First of all having too many columns is a bad design ( i wud say ).


Well wat i can think of is only 1 possibality...have a temp table

take the records u want to update, update it and then reinsert into permanent table.

[DaPi]

If I read it correctly you are inserting a row and then updating the same row X times . . . not very nice, but maybe you have to.

Try (I haven't !):
INSERT INTO ......... RETURNING ROWID INTO variable;
then
UPDATE ........... WHERE ROWID = variable;

[adb_03]

Do you really need an order by in the cursor definition? That will help prevent some sorts.

[chrisrlong]

Okay, so this may take me a little bit, but I just had to say that this is absolutely awful PL/SQL code. It breaks every rule there is.

Why are you concatenating columns to query on?

Why are you using DBMS_SQL for simple SQL?

Why are you nesting the cursors?

Why are you manually fetching in the cursors?

Why would you name a column Session_ID?

Why would you name the columns SESSION_ID and MODULE_ID in one table and SES and MOD in another?

Why would you update the same row multiple times?

Why would you commit within a cursor???

Why are you taking a normalized table and updating into a de-normalized one? This better be for a reporting database.

Cripes!

Now, before I can craft a proper solution, some questions need to be answered. The previous ones were actually rhetorical - I don't really want any answers. But for the next ones I do:

- What does this give you: SESSION_ID IN (302,304,305) ;
- Will multiple records be returned?
- What is the usage/definition of this table?

- With the UPDATE in the loops, are we updating multiple or only one row with each update? The same row? Is SESSION_ID, MODULE_ID the PK?

- Chris

[chrisrlong]

Well here, if all this is only being done for a single record, which may well be an incorrect assumption, this will be much easier for you:

 CREATE OR REPLACE PROCEDURE VULTEST02_REP 
AS
   l_SQL       VARCHAR2(32767);
   l_COLUMNS   VARCHAR2(32767);
   l_VALUES    VARCHAR2(32767);

   CURSOR
      l_Cur
   IS
      SELECT
         'ANSW'||ITEM AS ITEM,
         ANSWER
      FROM 
         TEST02
      WHERE
         SESSION_ID IN (302,304,305) ;

BEGIN
      
   l_COLUMNS   :=   '
            SES   ,
            MOD   '

   l_VALUES      :=   '
         SESSION_ID   ,
         MODULE_ID   ';

   FOR l_SingleRecord IN l_Cur
   LOOP
      l_COLUMNS   :=   l_COLUMNS   ||      ',
            ANSW'||l_SingleRecord.ITEM   ||'   ';

      l_VALUES      :=   l_VALUES   ||   ',
         l_SingleRecord.ANSWER   ';
   END LOOP;

   l_SQL   :=   '
      INSERT INTO
         TEST03_REP 
            (   '||
            l_COLUMNS   ||   '
            )
      VALUES
         (   ||   '
         l_VALUES   ||   '
         )   ';
            
   EXECUTE IMMEDIATE
      l_SQL;

   COMMIT;

END;

- Chris

[DaPi]

Chris, you are remarkably polite, remarkably patient and remarkably generous. I can only achieve the first by keeping my mouth shut and am too lazy for the other two.

[chrisrlong]

Dapi, you gotta be one nasty S.O.B. to consider *me* 'remarkably polite'

- Chris