Query Subject - processing time

[ry1633]

I've got a Query Subject in FM that is taking a long time to query from in Query Studio or Report Studio (an average of 4 or 5 minutes).   I have about 4 embedded calculations/additions of fields from other views.   Just trying to get a handle on what might be causing the extra time to pull a report from.    Could it be on at the database level or something at the Cognos level?   

[bdbits]

When you test the query in FM there is a tab where you can see the SQL. As a starting point, copy that into your database's query tool and see what the native response time is by comparison. If it is significantly shorter, then there is something going on in the query subject. If it is close to the same run time, then run an explain plan to narrow down the issue.

[ry1633]

how do I run an explain plan?   I've never had to do that before and don't know what that is.

[MFGF]

how do I run an explain plan?   I've never had to do that before and don't know what that is.

It's something you'd do in your database. What database are you using?

MF.

[Lynn]

The term "explain plan" refers to a method of obtaining a query execution plan so you can understand what steps the database optimizer has determined are optimal to retrieve the data you have requested via your SQL statement. As a result of investigation one might consider database changes such as indexing or partitioning. Alternatively one might consider revising the SQL statement to cause the optimizer to plan a different path and improve performance.

Different databases have different methods to do this. Wikipedia has a decent high level explanation, I think: https://en.wikipedia.org/wiki/Query_plan

In the Cognos world we are generated SQL rather than writing it manually but as bdbits pointed out, looking at a query execution plan can help understand what is going on at the database level that might account for problematic performance. If you're lucky you'll have access to a good DBA who is willing to collaborate on these types of questions and who would assist with generating and interpreting query plans.

[Michael75]

If you're lucky you'll have access to a good DBA . . .

And some of us haven't forgotten Lynn's views on DBAs :)

http://www.cognoise.com/index.php/topic,28179.msg90430.html#msg90430

[Lynn]

Oh dear, and there's another comment in there where I said "This is why the solution that the muppet pointed you to is just exactly what you need. It is elegant and simple but requires the DBA to brush the crumbs off his shirtfront and actually do some work by extending the calendar table to support this requirement."

But I must admit, there are some DBAs out there who are not utterly lazy and totally worthless.
;)