quick report slow

It can be several reasons for the report to be slow. The SQL statement might not be optimized to fetch the data quick. It can also be the amount of rows it fetches that are large.

If you are able to share the SQL statement we might be able to guess what the reason is.

Hi @Norahmf 

Is there any possibility to share your SQL?
otherwise, difficult to suggest a general opinion. 

Dears  Tomas Ruderfelt ,Kelum Pradeep Kumara ,

Thanks for reply.

Please find SQL in attachment.

Thanks.

Hello! Looking at the code there is a mixture of joins and subqueries. You could maybe get a tech resource to try and optimize the code.

Does this need to be executed multiple times per day or can it be limited to once or twice? If it can be limited have you though about creating this as an IAL and scheduling it when the database isn't being fully utilised?

What is the purpose of the report?

Right now it shows all non-cancelled customer order lines with supply code PR or IO. Why show everything without any more conditions?

Here are some hints about how to find out what is slow in the report:

Try the SQL without all sub-selects in the SELECT part.

Try first the SQL as it is but remove all fetching of data in the SELECT part, except order number:

SELECT co.order_no

FROM … etc.

If it is slow only selecting co.order_no then try to limit the amount of data with more conditions or enterable parameters.

If not add parts of the things you have now in the SELECT part and see which part that is slow and optimize that.

What is the purpose of the report?

Right now it shows all non-cancelled customer order lines with supply code PR or IO. Why show everything without any more conditions?

they need tracking Report with many details that's why.

Does this need to be executed multiple times per day or can it be limited to once or twice? If it can be limited have you though about creating this as an IAL and scheduling it when the database isn't being fully utilised?

if I put it in IAL object and scheduling it to update daily it will be solve slow issue?

The scheduling and execution of the IAL will execute the logic within the SQL statement - the execution of the IAL will be the process that takes the time you are seeing now by running the quick report. Once the IAL has been updated you can then query the data within the IAL via a quick report rather than execute your current quick report.

Just a not though if the IAL is scheduled once a day you wont get any updated transactions that occur during the day, these would appear after the next run.