Performance tuning and Statistics

[RRCR]

Hi,

My reports are taking much longer time around 1 hr and sometimes more than that. When I asked my DBA to create secondary indexes on the fields that I am using in my joins he asked me on what is the basis and what are statistics for recommending indexes. I do not know how to gather the statistics for reports. This is first project I am working on.

How can I collect the stats for reports?

Please suggest me if there are any ways to improve the performance of reports in Microstrategy. I searched in Microstrategy support site but didn't find any thing useful.

The database I am using is Teradata.

Thanks for your hekp!!

 

[mstrNate]

Hi,

There are a few ways to improve performance. First though, were these reports running faster than an hour previously? Also, what version of MicroStrategy are you running? And where are you running those reports? Web or desktop? And is the slowdown only on one report, or affecting all, in web or desktop?

From the MicroStrategy perspective, you should probably check that the VLDB settings are in line with what is recommended by MicroStrategy (you can find some docs on their tech help site), and if not then figure out why they aren’t. Also, you can check the project configuration settings in MicroStrategy and make sure they are optimized. You can check the web preferences, found when you go into web on the main page for a project. There are a few other docs in the help site for web server and intelligence server performance tuning.

With regards to Teradata, I think one thing you can do is learn how to read the explain plans for the sql, or ask your dba to go thru some of the explain plans for the sql that you’re generating from the reports. I can’t remember if Teradata calls them explain plans or not, Oracle does. It shows you the path the database optimizer is planning to take to answer your query(ies), and as such can be helpful when reviewing the code. It can give you ideas, to perhaps tune the sql being generated if possible, or the dba can add a secondary index like you were asking if needed. The other thing you can do is get rid of Teradata and replace it with Oracle. No, just kidding. I like Teradata.

Hope that helps you start,
Nate

 

[FLB]

Give the SQL to the dba, he will tell you (after running an explain plan or whatever) which piece takes too much time and how to make it go faster.

FLB.

 

[NaughtyVicar]

Doesn't sound like your DBA is very helpful...isn't database performance tuning his job??

Just my 2 cents...

 

[entaroadun]

Naughty...

You're going to have to roll up your sleeves and get into the plans and the DB structure. In my (albeit limited) experience, DBAs are too far from the real business requirements to do any real tuning.

There are lots of books on Teradata tuning... Get a subscription to O'Reilly Safari and take some out.

 

[spod]

Depending on the SQL being generated for the report I'd check it's VLDB settings (Data/vldb properties on the report).

The Default Intermediate table type for Teradata is to use Derived Tables, test this report by changing to use True Temporary tables.

In the past I've managed to get a non returning report with an explain plan of 27 hours to return in under a minute by changing this setting.

 

[fukudahs]

Hi RRCR,

If you already tried to use explain plan, figure out which table is makeing the "TABLE ACCESS FULL" and analysed its "cost"... and everything is ok... welll...

Analyse your fact table and study the feasibility to create a aggregated table.

 

[OV]

Microstrategy has a tech note TN5800-72X-0184 that has some tuning guidelines.
In my experince, aggregate tables are the best way to improve performance.

good luck, OV