Skip to main content
Solved

Slow read-only custom field

S
Sidekick (Customer)
Forum|alt.badge.img+5

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

Best answer by Tomas Ruderfelt

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

View original

Technical Outlaws
Hero (Employee)
Forum|alt.badge.img+11

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

 

S
Sidekick (Customer)
Forum|alt.badge.img+5
  • STXHILLSCA
  • Sidekick (Customer)
  • April 18, 2023

Yes, the C_Related_SQ is already indexed.

 

S
Sidekick (Customer)
Forum|alt.badge.img+5
  • STXHILLSCA
  • Sidekick (Customer)
  • April 25, 2023

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
Superhero (Employee)
Forum|alt.badge.img+20

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
Superhero (Employee)
Forum|alt.badge.img+20

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
Sidekick (Customer)
Forum|alt.badge.img+5
  • STXHILLSCA
  • Sidekick (Customer)
  • April 26, 2023

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
Sidekick (Customer)
Forum|alt.badge.img+5
  • STXHILLSCA
  • Sidekick (Customer)
  • April 26, 2023

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
Superhero (Employee)
Forum|alt.badge.img+20

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
Sidekick (Customer)
Forum|alt.badge.img+5
  • STXHILLSCA
  • Sidekick (Customer)
  • April 26, 2023

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 settingsAccessibility statement

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings