Metric; Count on an Attribute - Performance?

[amanx]

One of our reports has been running extremely slow. Taking almost 5 minutes for 32 rows

By removing one of the metrics on the report, the execution time improved dramatically from 5 minutes to 45 seconds!

The metric I removed was just a count on an Attribute (Employee ID).

So, I created a Fact based on (Employee_id) and then created a metric to count on the fact instead (of the Attribute).

The report now runs in a decent 45 seconds.

I looked at the Report SQL of the two scenarios
(Using an Attribute in a Metric VS a Fact in a Metric)
I found:

- Using a fact total number of passess = 1

- Using an attribute total number of passess = 5

Does anyone have any insight on why this occurs?

Thanks
Aman

 

[amanx]

Also, we had another report with the same situation. Its sql execution time was 7 seconds. After using a fact in the metric instead, sql execution was improved to 3 seconds.

Thanks,
Aman

 

[nlim]

just a suggestion, if you are using oracle,db2,teradata or sqlserver, you should be able to turn on derived tables. That will limit the number of individual passes required, and your optimizer will have a better view of the explain path.

on attribute counting, the engine cannot assume that the lookup values also exist at the fact level, so it goes and runs separate passes against the attribute lookup.

 

[mstrNate]

Are derived tables recommended for Oracle as well? I know on Teradata they're recommended because of the MPP architecture, but I hadn't heard derived tables being recommended for Oracle, or SQL Server. I suppose I could try it out.

Nate

 

[nlim]

Oracle has a decent handle on DT, they pretty much use the same parsing concept it their optimizer for their materialized views.

db2 just takes the dt syntax and parses it like their common table syntax.

ss2000 may be suspect...