Performance with IWR 7 on Windows 2000 ?

[dwh2]

Hi,

We have just deployed IWR 7 and we begin our tests.

The bad news is that impserver.exe uses 100 % cpu load and the overall performances are very poor !

Is anybody have the same experience ? (and a solution ...)

Thanks a lot. Bests regards.

Alain

 

[griffindm]

When designing reports for IWR, it's good to think of the process as if it were a network printer. Some processes are fine to run to a shared printer. Others will cause disruption to other users if run there. Always keep in mind the performance of the report under the Impromptu client before committing to run it on IWR. Use every trick at your disposal to speed the report processing up, including summary (aggregate-only) reporting, use of indices, summary reporting tables, database-only functions, etc. If after all this you still have a long-running resource hog, I wouldn't recommend it for use on IWR, any more than running a 1,000 page report to a shared printer. Windows 2000 has no particular issues on IWR performance that I am aware of as long as you give it enough RAM and temp disk working area.

Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[dwh2]

Hi Dave,

Thanks for your answer. I try to describe my problem more precisely :
- When any Impromptu report starts to execute, it takes 100% of the processor time
- Normally, it should wait the database answer, but it doesn't, it simply uses the processor time

We think, we have a configuration problem. But, we dont know where to look at.

Bests regards.

Alain

 

[aauricch]

Hello,
I had the same problem with cognos series7 MR1.
This is a bug!
By installing the IWR version 7.24 the problem disappaer.

 

[footyref]

This is in reply to griffindm's post. We are running IWR 7.1 MR1 on a Proliant ML570 G2 with 8 GB RAM and 4 1.5 GHz processors and are constanly getting pegged out on processor with IWRs. We are running Windows 2000 and you state that there are no issues with Windows 2000, so I am puzzled. I am wondering if we need to make some configuration changes or look at how the reports are designed. We MUST publish these reports as county and state users from all over Ohio must be able to access them. Some of them are drill through reports and some are standalone. Cognos seems to be at a loss about why this is happening, at least the level of support I have received so far.

 

[griffindm]

footyref,

Can you post the SQL from the report, and indicate whether there are any significant processing (i.e. local functions, local filtering, etc) that are not reflected in the SQL? A good measure of this is how long does the SQL take to return data from a SQL interpreter compared to the run time under the Impromptu client. Also, what is the size of the primary fact table, and is it indexed on the columns used in the detail filter?

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[RickShad]

Here is the SQL used in one of the drill-through reports referenced by footyref above. The report is sorted in Cognos by Cnty Cd, Caseload Nbr, and Case Nbr and grouped similarly. There are also total case counts calculated by report, county, and caseload.

The primary fact table is approximately 5 million rows and is indexed on some but not all of the columns used in the detail filter.

select T1."CNTY_CD", T1."CASELOAD_NBR", T1."CASE_NBR", SUBSTR(T2."CNTY_CD", 1 , 1), SUBSTR(T2."CNTY_CD", 2 , 1) || ' - ' || T2."CNTY_DESC", T2."CNTY_CD" || ' - ' || T2."CNTY_DESC", T3."PARTICPNT_NBR", T3."PARTICIPANT_TYP_CD", T3."ACTIVE_IND", T1."INTAKE_IND", T1."LOCATE_IND", T1."PATRNTY_IND", T1."SUPPORT_IND", T1."ENFORCE_IND", T1."CASE_TYP", T1."CASE_STATUS", T4."BOW_IND", T4."ACCESS_MED_INS_IND", T4."SSA_DISABLED_IND", SUBSTR(T1."CASELOAD_NBR", 3 , 4), T4."BNKRTCY_FILED_DTE", T5."CURR_MTH_IND", T5."LAST_DY_MTH", T4."DISCHRG_BNKRCY_DTE", T4."FIRST_NAME", T4."LAST_NAME", T4."SSN_NBR", T4."GNDR_TYP_CD", T4."BIRTH_DTE", 180000 - (1 - CAST(18 AS FLOAT) / ABS(18)) / 2, T5."CHLD_SPT_PART_NBR"
from "WFDW01DP"."FACT_CHLD_SPT_CASE" T1, "WFDW01DP"."DM_COUNTY" T2, "WFDW01DP"."FACT_CS_PARTICPNT" T3, "WFDW01DP"."DM_CHLD_SPT_PERSON" T4, "WFDW01DP"."DM_MONTH" T5
where T1."CNTY_CD" = T2."CNTY_CD" and T1."CASE_NBR" = T3."CASE_NBR" and T1."PART_NBR" = T3."PART_NBR" and T3."PARTICPNT_NBR" = T4."PARTICPNT_NBR" and T1."PART_NBR" = T5."CHLD_SPT_PART_NBR" and T5."CURR_MTH_IND" = 'Y' FOR FETCH ONLY

 

[footyref]

This is not the only report that pegs out a cpu in the server. It almost seems like most, if not all, do it at some point in the process. I have noticed it on three different servers with different reports. I certainly do not think we should have to buy enough servers with enough processors to handle every expected simultaneous user (4500 named users, 450 active users, 45 simultaneous users). That would be 12 4-way servers just dedicated to IWR, and taht is ridiculous. Cognos does not have an answer except to increase the heap size. All that does is increase the amount of memory available for the processes, but does nothign to reduce the cpu usage.

 

[griffindm]

Rick/footyref,

I don't work for Cognos, so I don't have a position to defend, nor an axe to grind. What you haven't reported is how fast the SQL is running in a SQL interpreter (SQLPlus, SQLTalk, ISQLW, etc), or how fast the report is running on a client PC. I find that Impromptu (whether the client or IWR product) is VERY CPU intensive, and often consumes as many resources as it can get. It is very possible to design reports that take excessively long to run, or to have a query structure and database indexing scheme that fails to mitigate increasing row size.

Whether this is a problem or not depends on how many resources the computer has, and how LONG Impromptu takes to complete the report. If a SQL interpreter takes 15 minutes to return your SQL query, neither IWR nor Impromptu client will improve it. It will take longer because of the additional formatting (and in some cases filtering) the product does.

Your SQL presents a 5-table join, with one fact table and 4 apparent dimension lookup tables. I see no additional filter on a prompted constant, other than the month indicator. How many rows does a typical query return? Have you tried adding indices to the fact table for all of the join columns? (or alternately, remove individual dimension lookups from the report to determine the relative impact on perfomance?)

If there is a marked difference between the speed of the SQL interpreter and IWR, and all of the report filter is present in the SQL, you have a valid case to beat Cognos with on performance. If not, look to what you can do on the database side to improve the situation.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com