Issue with performance of joining three reports
Hello All,
We have created one report based on one View. In that report we are joining 3 tables based upon the conditions. In the UI when we are searching giving the search field values, it is going for timeout session. We have indexed the columns in the tables for which we are doing search.
In the report by default timeout is 30 secs. It is also not recommend to increase the timeout here as it will be performance issue.
Could anyone suggest any approch to resolve this issue.
Thanks
Saikat
PEG
GB
HCL Technologies BV
NL
Hi,
All the search fields are indexed in the tables, regarding data count it has 0.6 million. We are able to get the SQL query from the tracer.
We have also run the SQL query directly in DB and it is not slow. And yes this the same SQL which we want to run.
Thanks
Saikat
Hi Saikat,
It's a little difficult to advise why the report is running slowly at the moment.
Reports (or the underlying SQL, if this is the bottleneck) may run slowly for a number of reasons, bad execution plan, missing indexes, outdated statistics, or it's simply trying to return too much data.
Are you able to run this with tracer and see what SQL this is issuing?
If so, can you run the same SQL directly against the database? And again, if so, is this performing slowly there as well?
Can you also verify that the SQL generated is the SQL that is expected (it doesn't do something which would cause a cartesian product by, say, referencing a table via an alias, but selecting fields from that table without the alias).