Performance between Hierarchy and Custom Member

Hi,

I have a simple dimension about product:

code, name, type
1111, coke, drink
1112, milk, drink
1113, coffee, drink
2111, cookieA, other
2112, cookieA, other

I can design a product dimension which has a hierarchy. Level 1 is type, and Level 2 is name. Then I can get total amount about drink and other. This design is the dimension which has a hierarchy.

However, I can design Custom Member to get total amount about drink and other:

code, name, CMColumn
1111, coke,
1112, milk,
1113, coffee,
2111, cookieA,
2112, cookieA,
9991, drink, ([Product].[1111]+[Product].[1112]+[Product].[1113])
9992, other, ([Product].[2111]+[Product].[2112])

Custom Member is designed instead of Hiearchy. The property "CustomRollupColumn" in dimension is set as CMColumn. So that I can get total amount about drink and other, too.

My question is which one's performance is better in the two designs?

thanks,

The first option has potentially better performance (if there are aggregations at the product type level), because queries for data at the type level could be directly answered from aggregations, rather than relying on rollups at runtime.|||

Deepak Puri wrote:

The first option has potentially better performance (if there are aggregations at the product type level), because queries for data at the type level could be directly answered from aggregations, rather than relying on rollups at runtime.

thanks.

I think the first option (using Hierarchy) costs more process time but less query response time.

The second option (using Custom Member) costs less process time but more query response time.

Is my opinion correct?

|||Probably correct - it depends on the dimension structure in the second option, and what aggregations are created for that structure...

Perfomance Question

I look after a database which is part of a third party CRM product. The
users of the product complain of intermittant poor performance, the
suspicion is that some more senior users are running their own queries
(the product allows users to do this). I've been asked by the
development team to try to capture the details of long running queries.

I've looked at the events listed in profiler and can't see one that
would be useful. Ideally I want to know who is running which query that
is taking longer than x seconds.

Any suggestions

TIA

LaurencePersonally, I would say 4-6 seconds is to long for a query. You may have a
different expectation.
Run the Profiler for a period of time _ i aim for at least 3 hrs - depnds on
server traffic etc

Events to capture : Stored Procedures--RPC:Completed &&
TSQL--SQL:BatchCompleted
(all sps and t-sql statement)

The critical columns to capture are: Duration and textdata . Others as well
for whatever other analysis you may need

Use the "duration" filter , do it by db id.

Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
news:448ED88F.1000104@.open.ac.uk...
> I look after a database which is part of a third party CRM product. The
> users of the product complain of intermittant poor performance, the
> suspicion is that some more senior users are running their own queries
> (the product allows users to do this). I've been asked by the
> development team to try to capture the details of long running queries.
> I've looked at the events listed in profiler and can't see one that
> would be useful. Ideally I want to know who is running which query that
> is taking longer than x seconds.
> Any suggestions
> TIA
> Laurence|||Thanks Jack,

I'll give that a go.

Laurence

Jack Vamvas wrote:
> Personally, I would say 4-6 seconds is to long for a query. You may have a
> different expectation.
> Run the Profiler for a period of time _ i aim for at least 3 hrs - depnds on
> server traffic etc
> Events to capture : Stored Procedures--RPC:Completed &&
> TSQL--SQL:BatchCompleted
> (all sps and t-sql statement)
> The critical columns to capture are: Duration and textdata . Others as well
> for whatever other analysis you may need
> Use the "duration" filter , do it by db id.
>
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
>
> "Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message
> news:448ED88F.1000104@.open.ac.uk...
>>I look after a database which is part of a third party CRM product. The
>>users of the product complain of intermittant poor performance, the
>>suspicion is that some more senior users are running their own queries
>>(the product allows users to do this). I've been asked by the
>>development team to try to capture the details of long running queries.
>>
>>I've looked at the events listed in profiler and can't see one that
>>would be useful. Ideally I want to know who is running which query that
>>is taking longer than x seconds.
>>
>>Any suggestions
>>
>>TIA
>>
>>Laurence
>>

Percentage?

Hi,
example:
if:
Price = 50
Percentage = 50
SQL column Liquid will return 25
Select Price - ((Price*Percentage)/100) as Liquid From Product
is the best way of doing that?You need to describe your issue better.
However, what you did describe sounds valid as long as you're willing to
deal with the inevetable rounding error.
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23lA50biJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> example:
> if:
> Price = 50
> Percentage = 50
> SQL column Liquid will return 25
> Select Price - ((Price*Percentage)/100) as Liquid From Product
> is the best way of doing that?
>|||Example, if exists a product (U$50) and a 50% discount, how much the buyer
will pay?
I tried to convert to english language my "problem"... I hope you can
understand!
Thanks a lot!
"Jay" <nospam@.nospam.org> escreveu na mensagem
news:%23%232wVblJIHA.5624@.TK2MSFTNGP04.phx.gbl...
> You need to describe your issue better.
> However, what you did describe sounds valid as long as you're willing to
> deal with the inevetable rounding error.
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:%23lA50biJIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> example:
>> if:
>> Price = 50
>> Percentage = 50
>> SQL column Liquid will return 25
>> Select Price - ((Price*Percentage)/100) as Liquid From Product
>> is the best way of doing that?
>|||Is this what you mean?
USE tempdb
CREATE TABLE items (
Price DECIMAL(9,2),
Percentage TINYINT,
Liquid AS ((Price*Percentage)/100)
)
insert into items values (50, 50)
SELECT Price, Percentage, Liquid
FROM items
Returns: 50.00 50 25.000000
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23lA50biJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> example:
> if:
> Price = 50
> Percentage = 50
> SQL column Liquid will return 25
> Select Price - ((Price*Percentage)/100) as Liquid From Product
> is the best way of doing that?
>