Solved

Slow read-only custom field

  • 18 April 2023
  • 9 replies
  • 134 views
S
Rank
Badge +3

We have a persitent custom field on the Service Contract, where we enter the number of the related Sales Quotation. This works fine.

Now on the Sales Quotation Line I would like to fetch the Service Contract ID in a read-only custom field. The select is as simple as it can get.

 

It technically works but it is very (!) slow, especially if there is no contract to be found.

Here the speed comparison in PL/SQL Developer: Running the select for a quotation with 67 rows takes 21 seconds (!), if this one custom field is part of the select statement. Without the cf, it takes 0.2 sec.

 

How can I optimize the speed?

Thank you for your input! 

Catherine

icon

Best answer by Tomas Ruderfelt 26 April 2023, 08:30

View original

9 replies

Technical Outlaws
Rank
Userlevel 5
Badge +11

Have you tried creating an index for the persistent field CF$_C_RELATED_SQ? 
 

 

S
Rank
Badge +3

Yes, the C_Related_SQ is already indexed.

 

S
Rank
Badge +3

Any other suggestions out ther in the community on how to speed up my custom field? Any help would be greatly appreciated! I am open to try anything that comes to your mind!

Thanks!

T
Rank
Userlevel 7
Badge +19

Can you double check that you have an index on the table SC_SERVICE_CONTRACT_CFT and column CF$_C_RELATED_SQ?

You seems to be logged in as appowner so you should be able to view the table SC_SERVICE_CONTRACT_CFT and see if it has any index.

If that exists, can you show the definition of the field also?

T
Rank
Userlevel 7
Badge +19

Another thought. Is the field C_RELATED_SQ a reference?

If so you shall use CF$_C_RELATED_SQ_DB instead and join it to OBJKEY instead.

That requires that you join the query with ORDER_QUOTATION view also to get correct OBJKEY

S
Rank
Badge +3

Hi Thomas

Here the description of the table SC_SERVICE_CONTRACT_CFT. There are two indexes.

And here what IFS shows:

 

What exactly do you mean with definition of the field? The CF in IFS itself?

 

Does this information help with the finding of a possible solution? Thank you in advance for your input!

S
Rank
Badge +3

Hi Thomas,

I have just seen your second comment. 

Yes, it is a reference. I will change the query to CF$_C_RELATED_SQ_DB and let you know the result later today!

Thanks!

 

T
Rank
Userlevel 7
Badge +19

Great, something like this then in the field:

SELECT s.contract_id
FROM sc_service_contract_cfv s, order_quotation o
WHERE s.cf$_c_related_sq_db = o.objkey
AND o.quotation_no = :QUOTATION_NO

S
Rank
Badge +3

Thank you very much Tomas!

The reference to the _DB field with the objkey was the solution! Speed is now within the expected range of 0.1 seconds for a bigger quote! 

Reply


Terms & ConditionsCookie settings