Skip to main content

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

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

 

Yes, the C_Related_SQ is already indexed.

 

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!

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?

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

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!

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!

 

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

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