Performance problem

[Former Member]

Hi HANA Experts,

I am using cc2.8xlarge machine on AWS with SAP HANA SPS5 Rev52v3.0.

I have to migrate a stored procedure from other database system to sap hane.

There is a job  table “_JobPool_RS” (RS for row store table) with more than 10000 rows for the job definitions. The procedure “JobPool_Run” has a while loop and get JobID from job table and runs the “DoJob” procedure. For the performance test in the procedure I have now only one select min(“JobID”) statement. Other statements are removed.

Now I have performance problem. The procedure “JobPool_Run” needs more than 30 sec for 10000 jobs (for job table with row store or column store). This is too slow. The procedure on other database system needs only 1 sec. On productive system the job table has more than 1 million rows.

Can you help me?

Best Regards.

set schema "MY_SCHEMA_2";

-----------------------------------------------------------

create row table "_JobPool_RS"

(

          "JobID" INT CS_INT NOT NULL,

          "JobStart" LONGDATE CS_LONGDATE,

          "JobEnd" LONGDATE CS_LONGDATE,

          "JobParamters" VARCHAR(100) CS_STRING

--          PRIMARY KEY ( "JobID" )

);

-- use PRIMARY KEY ("JobID") or INDEX "IX_JobPool_RS_JobID"

create index "IX_JobPool_RS_JobID" on "_JobPool_RS"("JobID");

-----------------------------------------------------------

-- insert 10000 rows into table "_JobPool_RS"

-----------------------------------------------------------

create procedure "JobPool_Run"

as

begin

          declare          vJobID integer;

          declare vJobParameters nvarchar(100);

          declare vTestCount integer := 0;

          while :vTestCount < 10000

          do

                    vTestCount := :vTestCount + 1;

                    select min("JobID") into vJobID          from "_JobPool_RS" where "JobStart" is null;

/*

                    select           "JobParameters" into vJobParameters

                    from           "_JobPool_RS"

                    where          "JobID" = :vJobID;

                    update "_JobPool_RS" set "JobStart" = now() where "JobID" = :vJobID;

                    call DoJob(:vJobID, :vJobParameters);

                    update "_JobPool_RS" set "JobEnd" = now() where "JobID" = :vJobID;

*/

          end while;

end;

-----------------------------------------------------------

-- Check

call "JobPool_Run";

[Former Member]

Thank you for your help. Unfortunately I have to redesign my procedure. But the question is still open, why a simple aggregation function min() from the in-memory database is so slow and the min() function from the non in-memory database is much faster? Why doesn’t the in-memory database use the index for min() function?

Regards

YH

[Former Member]

So I think I see how to unnest part of your loop. We now do a single select statement from the jobs table to pull both JobID and select. This removes the MIN and removes a ton of complexity from the loop.

Try the following:

-- Get an ordered list of jobs, by JobID in ascending order, where there is no start time

DECLARE CURSOR c_jobs FOR SELECT JobID, JobParameters from _JobPool_RS where JobStart is null ORDER by JobID;

FOR cur_job AS c_jobs DO

                    update "_JobPool_RS" set "JobStart" = now() where "JobID" = cur_job.JobID;

                    call DoJob(cur_job.JobID, cur_job.JobParameters);

                    update "_JobPool_RS" set "JobEnd" = now() where "JobID" = cur_job.JobID;

END FOR

Let me know how it goes.

John

[Former Member]

Hi,

You managed to do pretty much all of the worst things you could do in HANA in this stored procedure!

This isn't possible to answer because you haven't described what Do_Job does, but if you want to run it 1,000,000 times then it will suck in HANA. Instead, you have to flatten that out into one procedure which can parallelize.

In short - always avoid:

1) Row Store tables. Pretend the row store doesn't exist.

2) Cursors. That While loop is almost certainly not required and causes HANA to operate in single-thread mode.

3) Individual updates.

4) Calls to other procedures when you are in single thread mode.

John

[Former Member]

Hi,

Thank you for your tips!

But I have to port this stored from other database system to sap hana and I have to solve my problems step by step. This is not a new implementation.

1. What does the procedure DoJob is not important first. Forget this.
2. The jobs must be processed sequentially. Parallel job processing is not allowed. This is our business logic.

My first problem now is the SQL statement (select min(JobID) ….) . Why is it so slow? It needs 30 sec for select 10000 JobIDs => 50 min for 1 million, only for this simple SQL statement !!! My current system needs only 200 sec for select 1 million JobIDs.

set schema "MY_SCHEMA_2";

-----------------------------------------------------------

create row table "_JobPool_RS"

(

  "JobID" INT CS_INT NOT NULL,

  "JobStart" LONGDATE CS_LONGDATE,

  "JobEnd" LONGDATE CS_LONGDATE,

  "JobParamters" VARCHAR(100) CS_STRING

--  PRIMARY KEY ( "JobID" )

);

-- use PRIMARY KEY ("JobID") or INDEX "IX_JobPool_RS_JobID"

create index "IX_JobPool_RS_JobID" on "_JobPool_RS"("JobID");

-----------------------------------------------------------

-- insert 10000 rows into table "_JobPool_RS"

-----------------------------------------------------------

create procedure "JobPool_Run"

as

begin

  declare vJobID integer;

  declare vJobParameters nvarchar(100);

  declare vTestCount integer := 0;

  while :vTestCount < 10000

  do

      vTestCount := :vTestCount + 1;

      select min("JobID") into vJobID

      from   "_JobPool_RS"

      where  "JobStart" is null;

  /* Forget this !!!

      select "JobParameters" into vJobParameters

      from   "_JobPool_RS"

      where  "JobID" = :vJobID;

      update "_JobPool_RS" set "JobStart" = now()

      where "JobID" = :vJobID;

      call DoJob(:vJobID, :vJobParameters);

      update "_JobPool_RS" set "JobEnd" = now()

      where "JobID" = :vJobID;

  */

  end while;

end;

-----------------------------------------------------------

-- Check

call "JobPool_Run";

[henrique_pinto]

First of all, try to create your table as a column table instead.

Why are you using row table?

[Former Member]

I tested also with column table, same result.

[yeushengteo]

Hi,

How do you recongize the slow is caused by the MIN() function.

Aggregrate function is very fast. I am suspecting the performance is due to the section which you comment as "Forget this".

Regards.

YS

[Former Member]

Hi,

The SQL statements in comment block “Forget this” show only the logic in the original stored procedure. They are now in comment block. They do nothing. There is now only one SQL statement (select min(JobID)…) in the while loop. I think also that the aggregate function is very fast, but …

If it is possible, run my script on your HANA system. You will see how long this procedure needs.

Regards.

YH

[Former Member]

If the MIN() is causing a problem then it may be that it is running in the SQL engine rather than the OLAP engine. Try creating an Analytic view with aggregation type MIN() on JobID. Then do the SELECT against that. Your aggregation should run faster.

As I said before though your problem is the cursor and the individual inserts. This will never perform well on HANA and you need to unnest the SELECT/INSERT into a single SQL statement.

What is it you are trying to do with this procedure?

John

[yeushengteo]

Hi,

Hardware and memory differences will make a differences in this case? I tried the script and it takes roughly 8 secs to complete the 10000 iteration DB fetching the same record.

Maybe you can change the logic since you have to execute the job in sequential manner. Get all the records from table in a single DB fetch order by the job id in asending order. Loop the records and process it accordingly.

Just an idea.

Regards.

YS