Report running forever

[snagaboina]

Hi Everyone,
I have a report studio report that is scheduled through control M and sometimes it is running forever until cancel it manually or restart the server.
Same case even through event studio schedule. its not even getting killed even after exceeding the execution limit. Usually report takes 2 minutes to run.
Can someone please suggest what might be the reason.

OS: Windows
Version 10.2.2
Report format: HTML
Delivery method email

[dougp]

What database server?
Do you have generated SQL?  Does it contain code like this?

Code Select Expand

WHERE (t1.col1 = t2.col1 or (t1.col1 is null and t2.col1 is null))
  and (t1.col2 = t2.col2 or (t1.col2 is null and t2.col2 is null))
...

[snagaboina]

its SQL Server. I have similar logic.


((#Prompt('PDate','date','current_date')# > cast(getdate(),date)) and (cast([Sales].[Post Date].[CalendarDate],date) = _add_days (cast(getdate(),date) ,-1)))
or ((#Prompt('PDate','date','current_date')# < cast(getdate(),date)) and (cast([Sales].[Post Date].[CalendarDate],date) = cast([AdjustedDate],date)))
or ((#Prompt('PDate','date','current_date')# = cast(getdate(),date)) and (cast([Sales].[Post Date].[CalendarDate],date) = cast([AdjustedDate1],date)))

[Andrei I]

Could be different reasons.
1) Check how long all required SQL queries are executed.

2) Does it have Master-Details? Then for every master row the report might execute multiple SQL queries. And if a Master list is long the report might run thousands of SQL queries

3) Check if Cognos engine does a lot of processing rather than pushing most of it into DB

4) Check if Cognos processes are busy at the time of report execution


SQL Server Profiler can help

[snagaboina]

1) Check how long all required SQL queries are executed.
Entire report takes 2 mins.

2) Does it have Master-Details? Then for every master row the report might execute multiple SQL queries. And if a Master list is long the report might run thousands of SQL queries
No Master detail. But has cross joins.

3) Check if Cognos engine does a lot of processing rather than pushing most of it into DB
process set to default. its simple report and does not have any major calculations other then simple case statements. Also we do not see any query running in DB
4) Check if Cognos processes are busy at the time of report execution
report runs at 5:30 AM. no other reports runs at this time.

I believe this report is not even running even though shows under current activities. We have execution limit set for 2 hours. All long running reports get cancelled except this one.

[snagaboina]

Any Other suggestions ?

[snagaboina]

Today I put schedule for this job on hold and ran manually from cognos. even this way its is running forever. I am going to change the report and see will that fix the issue.

[dougp]

Does the generated SQL contain code that looks like what I posted before?
What does CPU load and total available hard drive space (both on the Cognos server) look like when the report runs so long?

[snagaboina]

Not similar to one you posted. Attached the SQL.