Solved

How to optimize a slow running SQL type Quick Report?

  • 24 April 2023
  • 7 replies
  • 241 views
ashen_malaka_ranasinghe
Rank
Userlevel 4
Badge +10

Refer to following “Quick Report” query which is taking 10+ minutes or even timing out on some occasions. Any ideas on increasing performance of this SQL query?

	SELECT DISTINCT sp.activeind,
	        sp.catalog_desc,
	        sp.catalog_no,
	        sp.cf$_last_modified,
	        sp.contract,
	        sp.gtin_no,
	        sp.List_price,
	       sp.List_price_incl_tax,
	        sp.sales_price_group_id,
	        sp.sales_unit_meas,
	       spc.cf$_last_modified AS char_lastmodified,
	       spl.last_updated AS SalesPrice_LastUpdated
	FROM Sales_part_cfv sp
	LEFT JOIN Sales_Part_Characteristic_cfv spc ON sp.contract = spc.contract
	                                           AND sp.catalog_no = spc.catalog_no
	LEFT JOIN SALES_PRICE_LIST_PART_PRICE spl ON sp.catalog_no = spl.catalog_no
	WHERE sp.contract = '201'
	  AND (sp.cf$_last_modified >= '&datestring'
	       OR spc.cf$_last_modified >= '&datestring'
	       OR spl.last_updated >= TO_DATE('&datestring2', 'yyyy-MM-dd'))

 

icon

Best answer by Tomas Ruderfelt 5 May 2023, 09:21

View original
Find me on LinkedIn as Ashen Malaka: https://www.linkedin.com/in/ashen-malaka-ranasinghe/

7 replies

ashen_malaka_ranasinghe
Rank
Userlevel 4
Badge +10

 The question is - why is this query reaching the timeout limit?

Find me on LinkedIn as Ashen Malaka: https://www.linkedin.com/in/ashen-malaka-ranasinghe/
ashen_malaka_ranasinghe
Rank
Userlevel 4
Badge +10

@Rusiru Dharmadasa, highly appreciate your inputs on this issue.

Find me on LinkedIn as Ashen Malaka: https://www.linkedin.com/in/ashen-malaka-ranasinghe/
T
Rank
Userlevel 7
Badge +19

Use SALES_PRICE_LIST_PART instead of SALES_PRICE_LIST_PART_PRICE.

Then you have the cf$_last_modified fields, how are they defined? If they are not persistent you are searching on a PLSQL function which should be avoided.

One functional thought, you have not joined in Sales_Price_List_Site to see if the price list is actually connected to the site 201, or is 201 always added to all pricelists?

 

If the purpose of the QR is to show changed prices etc. maybe they shall re-think the solution. What if they created a custom LU for logging and added events to fill in data + remove data?

Or why not split up the QR to make it simpler and faster? Now you show changes from 3 LU’s in one. Do they really need one list? Maybe they can combine this in Excel?

 

ashen_malaka_ranasinghe
Rank
Userlevel 4
Badge +10

Use SALES_PRICE_LIST_PART instead of SALES_PRICE_LIST_PART_PRICE.

Then you have the cf$_last_modified fields, how are they defined? If they are not persistent you are searching on a PLSQL function which should be avoided.

One functional thought, you have not joined in Sales_Price_List_Site to see if the price list is actually connected to the site 201, or is 201 always added to all pricelists?

 

If the purpose of the QR is to show changed prices etc. maybe they shall re-think the solution. What if they created a custom LU for logging and added events to fill in data + remove data?

Or why not split up the QR to make it simpler and faster? Now you show changes from 3 LU’s in one. Do they really need one list? Maybe they can combine this in Excel?

 

The goal here is to determine when a price has been updated on a part so they can trigger an export out to an external system via boomi interface. 

They have defined a custom field for date modified since this was not present at the API level. 

Find me on LinkedIn as Ashen Malaka: https://www.linkedin.com/in/ashen-malaka-ranasinghe/
ashen_malaka_ranasinghe
Rank
Userlevel 4
Badge +10

Hi @Tomas Ruderfelt,

Refer to the following attachments for cf$_last_modified custom attribute definition.

 

Find me on LinkedIn as Ashen Malaka: https://www.linkedin.com/in/ashen-malaka-ranasinghe/
T
Rank
Userlevel 7
Badge +19

Ok, if it is a read only field this will not get good performance if you search for it. If there are lots of data involved I would suggest creating records into a custom LU instead via custom events. Then the external system reads the projection for the custom LU. Make sure to index the date.

And have some logic to clear out logs when they are not needed.

I have had other customers doing similar things as customization to get good performance.

ashen_malaka_ranasinghe
Rank
Userlevel 4
Badge +10

Ok, if it is a read only field this will not get good performance if you search for it. If there are lots of data involved I would suggest creating records into a custom LU instead via custom events. Then the external system reads the projection for the custom LU. Make sure to index the date.

And have some logic to clear out logs when they are not needed.

I have had other customers doing similar things as customization to get good performance.

Thank you for the information. I just informed this information to customer.

Find me on LinkedIn as Ashen Malaka: https://www.linkedin.com/in/ashen-malaka-ranasinghe/

Reply


Terms & ConditionsCookie settings