Formatted search low performance on marketing documents - SBO 9.1 PL10 HANA

[former_member330700]

Hello,

I have this scenario:

1. Add 4 UDF - as Price type on marketing document lines.

2. Create formatted search like this:

IF $[$38.1] != '' THEN

    IF $[$38.43] = '-1' THEN

        SELECT 1 FROM DUMMY;

    ELSE

        SELECT 2 FROM DUMMY;

    END IF;

END IF;

3. Using AR Invoice setup formatted search on 4 fields created in 1 step with auto refresh on Item No.

4. Reopen Document

5. Choose Item from list

On step 4 i 5 a have strange behavior.

Document just open 2-3 times slower than normal.

Populating values when Item No. is choosen also is slower 2-3 times.

This behavior was tested on 2 diferent SBO 9.1 PL 10 HANA enviroments.

Anyone have discovered similar behavior?

Generally even if i create query like SELECT 1 FROM DUMMY; document just open slow and choose Items also slows.

Regards

Przemek

[act5stefan]

Dear Przemek & Johan ,

did you solve the problem in the end? We have the same with current Release (9.3PL12)

Best Regards

Stefan

[former_member1270348]

I have noticed a huge difference when specifying more than one trigger field vs only one trigger field for formatted search,

I had a form with 70 formatted searches, when all set to have a single trigger field, form takes 8 seconds to open, when specifying multiple trigger fields, even two or three per formatted search, form takes 2 minutes to open!

when specifying one trigger field, its saved in CSHS.FieldID

when sppecifying multiple trigger fields, CSHS.FieldID becomes NULL and fields are saved in SHS1 table,

there is a huge performance gap between the two cases

why is that?

[Johan_H]

Hi Przemek,

This system behavior is as old as B1 itself. It is mostly due to the Refresh Regularly setting. This setting will run such queries for each single line in the document, every time you open the document, but also for all lines in the document every time you add a new line or make any change to the document.

The system of running a separate query for each line is very slow. It is therefore advisable to not use the Refresh Regularly setting in an FMS if not absolutely necessary.

If the resulting value of an FMS is meant for other purposes than showing the data on screen, such as for example printing, it is better to move such logic to the printing template.

Regards,

Johan

[former_member330700]

Hi Johan,

Thank You for an answer but to be more clear below You can find my setup:

In the example there is just simple query that takes no time on database side. Database is new with coppied UDT and UDF.

Generally as i wrote before document opens/choose ItemCode from list  2-3 times slower so it just ruining any advantage of using HANA as a database platform.

Regards,

Przemek

[Johan_H]

Hi Przemek,

Like I said, it is mostly the Refresh Regularly setting. As you are now unfortunately experiencing, it is a slow system in general anyway.

The fact that it is slow on HANA as well, is because HANA is only fast with read operations. An FMS with a query causes one read (the query) and one write (writing the query result to the db) operation. By the way, I thought HANA was not even involved in processing normal transactions, only in reports and searches?

One other option you have is to look through the SAP Business One Idea Place, for ideas on improving system performance, and voting for those.

Until SAP decides to invest in performance improvement, over new functionality, it is probably best to avoid FMS on line level as much as possible.

Regards,

Johan

[former_member330700]

Hi Johan,

Since 2006 never seen such delays in FMS so i belive it can be fixed in future.

We are migrating to HANA one of our customer which use Invoice and Payment document as let's say "Customer Checkout" functionality that's why in this process time is money.

On the document there are like 10 FMS on line side(both standard and UDF)

1.Document opens in 6 sec!

2.Adding single line takes 4 sec!

Such delays are just not accetable.

I'll wait until patch 11 since there might be some performance fixes.

Second option is to move FMS to UI but as we know we cannot use DBDataSorce so performance can be even slower.

BTW. I checked Hana trace and it seems that every FMS is fired 3 times - do not know if it's standard behavior - but for me it's suspicious.

Trace in attachment, search SELECT 1 FROM DUMMY

Regards,

Przemek

hana_trace_FMS.txt.zip