Slow Reports in Cognos. 1

[mobajwa]

We have a report under Cognos which uses a stored procedure.
The stored procedure that this report actually calls runs in about 2.5 minutes. The report on Cognos runs for about 10-15 minutes. We need to figure out why the performance is so poor.

 

[DoubleD]

Start by comparing the Magic SQL to the Database SQL. Make sure as much processing as possible is happening on the database. There's probably a calculation or filter that's causing the data to come back to the desktop, then process.

I am what I am based on the decisions I have made.

DoubleD

 

[mobajwa]

I am a newbie. Could you please tell me what exactly i would be looking at . whats Magic SQL ? where exactly would the filter be , if we have any ?

 

[DoubleD]

mobajwa,
This statement is not meant to be offensive but;
If you're not familiar with where a filter is in a Cognos report, you probably aren't the right person to be tuning the performance.

Given that Caveat, please answer these questions so I can help you as much as possible:
What database are you running against (Oracle, Sybase, DB2, Access)?
What version of Cognos do you have?
Is there a Database Analyst at your company who can help?

These questions will give me a better feel for what direction I should give you.

I am what I am based on the decisions I have made.

DoubleD

 

[mobajwa]

( LOL . no offense taken . Im just an intern trying to learn things ! )

I believe we use MS SQL 2000. I searched around and found the following from the cognos website

------------
Document Details Return to Search Results
Document#: 106259
Title:
Performance trouble shooting

Created:
Jan 23, 2002

Applies To:
Impromptu Web Reports - 6.0



--------------------------------------------------------------------------------

Description:

Reports are slow to return What factors can contribute to the slow down? How can you determine the source of the slowdown?

Solution:

Create a time line to account for the total time to run the report.

1. Execute the report through impserver.exe noting the start time.
2. How long is it before the database receives the query? note the time (query send time)
3. How long does the query take to run? (query execution time)
4. How long does it take the database to return the data to impserver (query return time)
5. After the report has been displayed in impserver, save it as the report type that is being outputted and note the duration (report generation time).

query send time + query execution time + query return time + report generation time = total

Query send and return time point to network bottleneck possibility.
Query execution points to the database or the generated sql.
Report generation may indicate a local machine bottleneck.


---------


now i ran the impserver,and when i run the query it just shows me the name of the query in impserver and the time it started. Any ideas on how exactly to find the so called query send time and query return time ?



as far as the version is concerned i believw it is Cognos series 7 version 7.1.15.0

 

[DoubleD]

Query send and return time point to network bottleneck possibility.

This is almost never the case when a report is performing poorly. If this was the case, ALL reports would perform poorly.

Query execution points to the database or the generated sql.
Report generation may indicate a local machine bottleneck.

I consider these the two most common problems with poor report performance, but they tend to be interrelated.

1. Are you processing as much SQL as possible on the database?
How to find out:
Open your report in Impromptu Admin (Must be Impromptu ADMIN)
Go to Report...Query... and click on the Profile tab.
Click the SQL radial button
The SQL shown is the SQL being passed to the database
Hold CTRL+SHIFT+ALT and click the EDIT button
You should have a window open that is headlined "Magic Query". This is the SQL Impromptu actually has to perform.
Compare the difference between the SQL statements.
Look for anything in the Magic SQL that could be replaced with a database specific function that would force it to run on the database. In three years of troubleshooting Cognos, this was the most common performance issue by far.

2. Local machine bottleneck is caused by one of two problems. The first would be our previous point regarding processing as little SQL as possible on the desktop. The second is whether you have enough horsepower for the report you're trying to run.
Check into the following:
What type of processor do you have? (486 shudder, Pentium 1-4, Athlon, etc.)
How much RAM does the PC have?

Lastly, if you have a database analyst available, check into what fields are indexed in the database. Can you have an index added if necessary?


This is just a start. If you need help troubleshooting your SQL, paste the SQL and Magic SQL in this message and I'll try to help you find efficiencies.

I am what I am based on the decisions I have made.

DoubleD

 

[mobajwa]

Thanx for the reply.First about the horsepower, We have enough , i dont know the exact specifics, but we actually have more than enough horsepower.

Now to the Magic SQL

i logged in to Impromptu Admin, opened the report and followed all ur steps.


here is the problem ..
when i Click the SQL radial button, there is no SQL CODE SHOWN !!!!
i dont know how to send u a screenshot or something. but the top left of the window says average query time 2min 19 seconds, result row set 18 rows.
when i do click on radio SQL and click on edit , it says

Cannot edit SQl, this report can not be translated into a single database only query.

Whats next . thanx again for being a great mentor !

 

[DoubleD]

Ouch, sounds like you've got a pretty ugly report on your hands. Well, let's start with the following:

1. Post the SQL for the report and I'll see if there are any glaring issues.

2. Open the report, then do File...SaveAs... and save it as an IQD file. The SQL in the IQD file is pretty close to the Magic SQL. Post the SQL from the IQD also.

3. Is the catalog using HotFiles? Hotfiles are really nothing more than flatfiles, so using them puts all the processing on the desktop. Ask the person who created the catalog. If they are not available, close your report, but leave the catalog open. Then go to Catalog...Tables... Are there any objects in the right half of the window under Hotfiles, or that have a flaming object next to them.

I am what I am based on the decisions I have made.

DoubleD

 

[mobajwa]

THIS IS THE IQD FILE

------------------------------------------

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ONYX
DATASOURCENAME,C:\Summary.imr
TITLE,Summary
BEGIN SQL
Not available.
END SQL
COLUMN,0,seqnum
COLUMN,1,ListType
COLUMN,2,ReportDate
COLUMN,3,MarketSec
COLUMN,4,CompanyType
COLUMN,5,CompanySubType
COLUMN,6,CustomerCnt
COLUMN,7,total ( CustomerCnt ) for
COLUMN,8,total ( CustomerCnt ) for
----------------------------------------------------


about 3. I went to see under HOTFILES and firstly i dont see hotfiles on the right of the window that opens up.

I see database tables on the left and catalog tables on the right.there is 2 radials one is Database and other is Hotfiles. Hotfiles is not selected and Database is selected.

I dont see any database tables, but i see 3 catalog tables.
whats next ???

 

[DoubleD]

Well, I guess this will be a treat. Looks like we have no way of looking at the actual SQL that will process.

1. Please post the SQL as shown in the Profile tab.

2. Are there any Calculations in your report that seem overly complex?

3. Go to Report...Query... then click on the Filter tab. Retype the filter information into this post and I'll look at that for clues as well.

I am what I am based on the decisions I have made.

DoubleD

 

[drlex]

DoubleD
This is probably going to show up my inexperience with stored procedures, but surely if the report is using a stored procedure on the database, it's just passing parameters to the DB and hence there's no SQL detail other than the execute call? If so, isn't the time difference down to report formatting (sub-lists/frames etc?)

apologies for crashing the dialogue,
lex

soi la, soi carré

 

[mobajwa]

**** ERROR ****
Cannot execute query.
Processing requirements exceed the current Client/Server limits.

This is what i got when i tried to run the report in impromptu admin.

Where can i change these limits ?

 

[mobajwa]

hehe.. yeah its a treat alright !!!!

1. Please post the SQL as shown in the Profile tab.

Like i said before, there is no SQL code showing there !!!!!


2. Are there any Calculations in your report that seem overly complex?

nopes..

3. Go to Report...Query... then click on the Filter tab. Retype the filter information into this post and I'll look at that for clues as well.

the filter just has the three dots in there

...


I guess that means there is no filter.. right ??

 

[DoubleD]

So, are you doing any calculations, filtering or formatting in your report?
Or is your Oracle Stored Procedure doing all the work?
If that's the case, then it probably comes down to how long it's taking your PC to receive the info and format it.
Is this report supposed to return a large amount of data?

At the start of this post, you said it takes 10-15 minutes to run the Impromptu report, now you state that it does not run. Were you ever able to run it from your PC, or did it start failing recently?

drlex,
You've got a good point. I just assumed they were doing more with the data than just using a stored procedure. That's what I get for assuming.

I am what I am based on the decisions I have made.

DoubleD

 

[mobajwa]

The MS SQL sptored procedure is doing all the work !

now the report is running in about 7-8 minutes from my pc. i changed the option to flexible processing.. under REPORT > QUERY > CLIENT/SERVER from impromptu admin and then came back to my pc and ran the report.

the report does not returna large amount of data.I run the report of.

any more ideas. i appreciate the help.

 

[DoubleD]

Are you using native drivers or ODBC to connect to MS SQL?

I am what I am based on the decisions I have made.

DoubleD

 

[mobajwa]

ODBC

 

[DoubleD]

I'm not sure about MS Sql, but I know Oracle performs terrible through ODBC compared to native drivers.

I am what I am based on the decisions I have made.

DoubleD

 

[mobajwa]

Ok Mr D: Hers the update
We ran the trace and apparently this part of the code is the one that takes about 4 minutes to run when we run the report of cognos.The code seems to be very simple and i dont think it really shud take 4 minutes to run at all !
Can u see whats going on

--
INSERT #Test
(
iOwnerId
)

SELECT iOwnerId
FROM #Temp01 a
WHERE EXISTS ( SELECT b.*
FROM #Temp01 b
WHERE a.iOwnerId = b.iOwnerId
AND vchListType = 'StartList')
AND NOT EXISTS ( SELECT c.*
FROM #Temp01 c
WHERE a.iOwnerId = c.iOwnerId
AND vchListType = 'EndList')



Could aliasing the same temptable be a reason at all ?
Remmember one thing, the code works fine when we run the stored procedure in query analyzer, its only when we run the report ( which uses the stored procedure ) in cognos , that it starts to take the extra time to run.

 

[DoubleD]

Are fields iOwnerId and vchListType both indexed?

Also shouldn't this be changed to:

INSERT #Test (iOwnerId)

SELECT iOwnerId
FROM #Temp01 a
WHERE a.vchListType = 'StartList'
AND NOT EXISTS (SELECT c.iOwnerId, c.vchListType
FROM #Temp01 c
WHERE a.iOwnerId = c.iOwnerId
AND vchListType = 'EndList')

Why would you want to grab all the fields from the table? And why not filter on vchListType?

I am what I am based on the decisions I have made.

DoubleD