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.
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).