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.

 

[mobajwa]

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]

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:

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]

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