Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

mobajwa (MIS) (OP)
17 Feb 05 11:11
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.
Helpful Member!  DoubleD (TechnicalUser)
17 Feb 05 11:14
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 (MIS) (OP)
17 Feb 05 11:54
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 (TechnicalUser)
17 Feb 05 12:00
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 (MIS) (OP)
17 Feb 05 13:15
( 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 (TechnicalUser)
17 Feb 05 14:37

Quote:

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.

Quote:

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 (MIS) (OP)
18 Feb 05 12:30
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 (TechnicalUser)
18 Feb 05 12:43
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 (MIS) (OP)
18 Feb 05 13:18
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 (TechnicalUser)
18 Feb 05 13:31
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 (TechnicalUser)
18 Feb 05 13:33
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 (MIS) (OP)
18 Feb 05 13:50
**** 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 (MIS) (OP)
18 Feb 05 13:54
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 (TechnicalUser)
18 Feb 05 14:18
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 (MIS) (OP)
18 Feb 05 15:05
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 (TechnicalUser)
18 Feb 05 15:19
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 (MIS) (OP)
21 Feb 05 12:01
ODBC
DoubleD (TechnicalUser)
21 Feb 05 12:14
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 (MIS) (OP)
23 Feb 05 13:04
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 (TechnicalUser)
23 Feb 05 14:40
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

mobajwa (MIS) (OP)
24 Feb 05 10:56
thanx.

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

what would this do ? someone else suggested putting justa  1 in the place.


DoubleD (TechnicalUser)
24 Feb 05 11:04
Removing the extra Select should speed things up as it's one less query that needs to run.

I don't know that the Select 1 would work since you're saying the OwnerId doesn't exist in (1). I haven't worked with the EXISTS function, but I assume it's like an IN statement. If not, I would change your SQL to:

CODE

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

Actually, now that I think of it, this SQL should be much faster since you're also excluding another join.

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

DoubleD

mobajwa (MIS) (OP)
24 Feb 05 14:03
thanx.. but u know what .. there is a difference of th number of records returned when i take the

WHERE  a.iOwnerId = c.iOwnerId


out , so i guess we have ompitimiized it as much as possible. Thanx a lot for the help.

Will post a new thread with my new problem tommorow. see u there :)

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close