Sql script code taking minutes to execute

[Former Member]

Hi,

My Requirement is to retrive data from table , loop through it and adjust the columns based on business logic and output it in sql calculation view  .

I wrote the Sql Script in 2 different versions both are taking minutes ,which i don't understand because there are only 15000 records in the table .

The statement that is Taking more time is 

var_out = select :pernr as PERNR,:cpamt as CPAMT,:curre as CURRE from DUMMY UNION ALL select * from :var_out;

Here is the Code

Version 1 -- using cursors 

/********* Begin Procedure Script ************/

BEGIN

declare pernr NVARCHAR(8) := ''; 

declare CPAMT DECIMAL(13,0) := 0;

declare CURRE NVARCHAR(5) := '';

declare counter int := 1;

DECLARE cursor c_p0759 for

   select p.pernr, p.subty as citem ,t.CREVI ,t.cplan,p.begda,p.seqnr,

            p.cstat ,p.carea,p.cpamt,p.curre,p.xhour,p.cpnum,p.stkun

            from "HCDCLNT010"."PA0759" as p inner join "HCDCLNT010"."T71ADM09" as t

            on p.subty = t.CITEM ;

for p0759_curr_row as c_p0759 do

  pernr := p0759_curr_row.PERNR ;

  cpamt := p0759_curr_row.cpamt ;

  curre := p0759_curr_row.curre ;

  var_out = select :pernr as PERNR,:cpamt as CPAMT,:curre as CURRE from DUMMY UNION ALL select * from :var_out;

  end for;

END /********* End Procedure Script ************/

version 2 - with out cursor using While

/********* Begin Procedure Script ************/

BEGIN

  DECLARE p759_count BIGINT ;

  DECLARE rowcount BIGINT := 1 ;

comp_records = select ROW_NUMBER() over(order by (SELECT 1 from dummy)) as rownumber,p.pernr, p.subty as citem ,t.CREVI ,t.cplan,p.begda,p.seqnr,

            p.cstat ,p.carea,p.cpamt,p.curre,p.xhour,p.cpnum,p.stkun

            from "HCDCLNT010"."PA0759" as p inner join "HCDCLNT010"."T71ADM09" as t

            on p.subty = t.CITEM ;

p758_records = select pernr from "HCDCLNT010"."PA0758";

select count(*) into p759_count from :comp_records;

WHILE rowcount < :p759_count

DO

p759_row = select * from :comp_records where rownumber = :rowcount ;

var_out = select pernr,cpamt,curre from :p759_row UNION ALL select * from :var_out ;

END WHILE;

END /********* End Procedure Script ************/

Thanks,

sree

[lbreddemann]

I don't get why you want to use a cursor here.

Why do you want to process the data slowly?

And as you do: why ask to performance tune this?

Please heed to the recommendation/warning/guidance of not using cursors whenever possible.

[Former Member]

Hi,

Even my goal is not to use the Cursor and achieve the  requirement with best performance . So i am looking for Ways of doing this requirement ,any ideas you can suggest Please .

[lbreddemann]

Why don't you start by explaining, what you actually want to achieve?

From what I see is you

• define a SQL query (cursor)
• loop over the result set of this query, taking three column values and ignore the rest
• union these three column values with the total result set so far

Practically you do a projection on top of a SQL statement.

Why don't you just do

select p.pernr, p.cpamt, p.curre

            from "HCDCLNT010"."PA0759" as p

            inner join "HCDCLNT010"."T71ADM09" as t

                      on p.subty = t.CITEM;

instead?

[Former Member]

Hi,

Sorry if i would have Confused you  . In the For Loop there is a business logic which needs to be written (Not Shown ) ,that was the reason of Extracting those Fields in the Select Query.Using those Fields i have to write the business Logic .

for p0759_curr_row as c_p0759 do

  ********Business Logic to manipulate the Fields  . **************

  var_out = select :pernr as PERNR,:cpamt as CPAMT,:curre as CURRE from DUMMY UNION ALLselect * from :var_out;

  end for;

Is there any other way of Achieving this without degrading the Performance

Thanks,

sree

[lbreddemann]

Ok, what you are currently saying is:

"I have this piece of code, that I designed in a way that requires a cursor loop. I don't show the code, but I want tips how to make it faster."

That doesn't work.

In order to get faster performance on the database level, it's required to rewrite this code part you call "business logic" so that it doesn't work on separate rows, but on the whole set of your selection.

The current approach is inherently slow due to looping over single records (requires early materialization and multiple data copies) and due to the approach of appending your results to the output structure (again, data is copied over and over here).