Report definition is giving 504 Gateway Time-out error. It's a drill-down report. We have increased the Maximum elapsed time in seconds to 60. The report definition is working fine in the dev and test environment while in production we are getting 504 error. This report definition has joins with 4 Index classes. As a part of the solution, We tried to clear the DB cache but that too didn't work. Any suggestions or solutions?
@AnkurT60 HTTP 504 Gateway Error (most likely) relates to your proxy server: between your browser and the Pega Server.
You have several angles to explore (not mutually exclusive - and in fact it is a optimization task at the highest level).
1. Ensure you are only fetching the rows you need to.
2. Ensure you are only fetching the number of columns you need do.
3. Find out what your Proxy Server timeout is set to.
4. Time (manually, directly against the DB) how long your SQL query takes to run against the DB. (run this a few times, and take an average).
5. The prediction/ sanity-check here: the time above will be a significant portion of the same value that your Proxy Server is set to.
The remaining time will be a combination of how long it takes the server to convert/render the results and how long it takes to transfer over the network.
6. Ensure this SQL is running as quickly as possible: for instance run an EXPLAIN PLAN against the SQL - and ensure you have the right indexes built and that all the indexes are being used. (Work with your DBA).
7. Ensure all the Properties are exposed - that is are real database columns (which are faster to fetch from than the BLOB).
8. If you can get the time down for the SQL so that it fits comfortably within the Proxy Server timeout - you are done.
9. If not then you will need to re-consider point 1 and 2? Are you able to limit the data in some way (by dates ranges etc?)
10. If you really need all the data you are fetching in a single go and you have got the SQL running as quickly as possible - and that time is still not fitting the proxy server timeout - consider altering that timeout.