how to optimize the sql based report (not fm model) running time please

[aswani]

Hello,

I have a report based on SQL, Not on FM model, it has about 10 sql statements, but it is very slow, report ran for 1 hr then it shows authentication page etc...,

so my question what optimizations could be done please help me oput any open ideas,

i do have some from my end

1) select only required columns
2) i included order by clause does it require?
3) (NOLOCK)  at each table


please share any suggested ideas

thanks in advance
milan

[blom0344]

1) select only required columns

Hmm, not bad for a start, why fetch unwanted columns in the first place?

2) i included order by clause does it require?

It depends. In most cases ordering the query output directly is not needed. You can do this in the report layout. The performance penalty for an order by in a query can be smashing as well.

3) (NOLOCK)  at each table

Probably not the culprit. you need to have a thorough look at the SQL formulated/generated (perhaps with the friendly DBA) , cause a bit of tuning db-side may go a long way.
Once again..  Don't use straight SQL with Report Studio. Make an effort to use proper models. At least for production purposes. Bless you  ;)

[aswani]

Hi Blom,

Thank you for your input

could you please give some more suggestions where else can i make changes to make this report get data into it

Thanks again and thanks in advance
milan

[blom0344]

I'm not clearvoyant   ;)   and I have no insight into the report, the queries etc.  Start with testing and perhaps tuning the queries. Check available memory and what happens when you run the report.


I would also suggest taking some time to fill in the details when posting issues. There seems to be an ever increasing number of forummembers who expect miracles  :o  from the most generally phrased topics