HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.olap » Long Processing time
Topic: Long Processing time
hello, i have a measure group with only 39M rows, and it has 4 measures. I
would think it should only take about 15mins to process on a 64bit Win2K3
server with 8Gb of ram. However, it is taking 4 hours!!
Can someone please tell me why it takes so long to process my measure group?
I only have a few aggregations. the measure group is used for a
Many-to-many measure group. I realize AS would like more memory, but I did
increase the paging file to 40GB on C: and 40 GB on D:.
My other measure group which joins to the measure group above takes equally
as long to process, and it's only 20M rows.
I'm at a loss as to why it's taking so long to process. I can process a 20M
row dimension in about 20 mins, so why are the measure groups taking so long?
Can someone please help me out.
Thanks,
Jason
Replies below ↓
Replies
RE: Long Processing time
Hi Jason,
Make sure your queries will perform very quick, because sqlserver will
execute these queries multiple times, for each field it will execute it again.
Then again, the processing time is not only depending to the measures, but
don't forget the dimensions. For every attribute it will do some
calculations. Minimise the number of attributes. If user don't need a certain
attribute then remove it.
If it is still slow you can setup the attribute relations. For example:
define that the customername has a one to one relation to the
customernumber...
Ofcourse there are a lot more things to look at.
Julian Kooiker
HGH business consultancy B.V.
www.hgh.nl
"Jason" wrote:
> hello, i have a measure group with only 39M rows, and it has 4 measures. I
> would think it should only take about 15mins to process on a 64bit Win2K3
> server with 8Gb of ram. However, it is taking 4 hours!!
>
> Can someone please tell me why it takes so long to process my measure group?
> I only have a few aggregations. the measure group is used for a
> Many-to-many measure group. I realize AS would like more memory, but I did
> increase the paging file to 40GB on C: and 40 GB on D:.
>
> My other measure group which joins to the measure group above takes equally
> as long to process, and it's only 20M rows.
>
> I'm at a loss as to why it's taking so long to process. I can process a 20M
> row dimension in about 20 mins, so why are the measure groups taking so long?
>
> Can someone please help me out.
>
> Thanks,
> Jason
RE: Long Processing time
Thanks Julian.
My issue only with my Orders and Orders Config measure groups. Both of
these measure groups utilize a Unique Identifier as part of the primary key.
The Order Config measure group is actually an intermediate dimension that
performs as a many-to-many measure group.
I'm going to change the Unique Identifier to an Interger value, recreate the
primary key index, and then try to reprocess. My hunch is that AS is not
liking the Unique Identifier as part of the primary key.
My next step will be to remove the many to many intermediate measure group
so I can do a direct join instead.
Jason
"HGH Business Consultancy B.V." wrote:
> Hi Jason,
>
> Make sure your queries will perform very quick, because sqlserver will
> execute these queries multiple times, for each field it will execute it again.
>
> Then again, the processing time is not only depending to the measures, but
> don't forget the dimensions. For every attribute it will do some
> calculations. Minimise the number of attributes. If user don't need a certain
> attribute then remove it.
>
> If it is still slow you can setup the attribute relations. For example:
> define that the customername has a one to one relation to the
> customernumber...
>
> Ofcourse there are a lot more things to look at.
>
> Julian Kooiker
> HGH business consultancy B.V.
> www.hgh.nl
>
>
> "Jason" wrote:
>
> > hello, i have a measure group with only 39M rows, and it has 4 measures. I
> > would think it should only take about 15mins to process on a 64bit Win2K3
> > server with 8Gb of ram. However, it is taking 4 hours!!
> >
> > Can someone please tell me why it takes so long to process my measure group?
> > I only have a few aggregations. the measure group is used for a
> > Many-to-many measure group. I realize AS would like more memory, but I did
> > increase the paging file to 40GB on C: and 40 GB on D:.
> >
> > My other measure group which joins to the measure group above takes equally
> > as long to process, and it's only 20M rows.
> >
> > I'm at a loss as to why it's taking so long to process. I can process a 20M
> > row dimension in about 20 mins, so why are the measure groups taking so long?
> >
> > Can someone please help me out.
> >
> > Thanks,
> > Jason
RE: Long Processing time
Hello Deepak, I can't even get past the processing Data stage. there is only
1 aggregation.
I don't have any distinct count measures. I'm currently remodeling the data
to remove the many-to-many. My other many-to-many's work great and are
almost equal in data storage size. I really think AS is flaking out with the
Unique Identifier as part of the primary key.
I removed the UID and removed the many-to-many. I'll keep you posted.
I did have a few referenced dimension tied to my intermediate table. That
may have been part of the problem too. I did not have referenced dimensions
tied to my other many-to-many intermediate measure groups.
Jason
"Deepak Puri" wrote:
> Do you have any distinct count measures in these measure groups - these
> can tacke much longer to process, depending on the indexes on the source
> tables? Also, is a major proportion of the time spent in building
> aggregations?
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
RE: Long Processing time
Hi Deepak, I did have the referenced dims materialized. Are you saying I
should make them unmaterialized? I thought I needed them materialized for
aggregations?
Whenever my cube tried to process the Order table (which joins to a bunch of
different dims as well as the intermediate dim, the processing would take way
too long. The intermediate measure group also took too long to process).
I was thinking it was because of the UID or the materizied referenced dims
attached through the Orders table to the Intermediate dimension.
Jason
"Deepak Puri" wrote:
> "I did have a few referenced dimension tied to my intermediate table.
> .." - if those referenced dimensions are materialized, that could slow
> down processing, because the SQL query submitted by the server will
> include a join to an intermediate dimension table for each materialized
> refernce dimension. So it would be useful to study the SQL queries from
> the cube processing dialog window.
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.developersdex.com ***
>