Performance Tuning UPDATE Statement

Below is a simple UPDATE that I have to perform on a table that has
about 2.5 million rows (about 4 million in production) This query
runs for an enourmous amount of time (over 1 hour). Both the
ChangerRoleID and the ChangerID are indexed (not unique). Is there
any way to performance tune this?

Controlling the physical drive of the log file isn't possible at our
client sites (we don't have control) and the recovery model needs to
be set to "Full".

UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
ChangerRoleID IS NULL

Any Help would be greatly appreciated!On 4 Aug 2004 08:27:50 -0700, MAS wrote:

>Below is a simple UPDATE that I have to perform on a table that has
>about 2.5 million rows (about 4 million in production) This query
>runs for an enourmous amount of time (over 1 hour). Both the
>ChangerRoleID and the ChangerID are indexed (not unique). Is there
>any way to performance tune this?
>Controlling the physical drive of the log file isn't possible at our
>client sites (we don't have control) and the recovery model needs to
>be set to "Full".
>UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
>ChangerRoleID IS NULL
>Any Help would be greatly appreciated!

Hi MAS,

If you remove the non-unique index on ChangerRoleID before doing the
update and recreate it afterwards, you'll probably save some time. The
index could have been useful if only a few of all rows match the IS NULL
condition, but with over aan hour execution time, I think there are so
many matches that a full table scan will be quicker. Removing the index
before doing the update saves SQL Server the extra work of constantly
having to update the index to keep it in sync with the data. Of course,
this might affect other queries that execute during the update and would
have benefited from this index. The index on ChangerID will neither be
used nor cause extra work for this update.

Check if there's a trigger that gets fired by the update. If you can
safely disable that trigger during the update process, do so. Same for
constraints: are there any CHECK or REFERENCES (foreign key) constraints
defined for ChangerRoleID? If so, disable constraint checking (again, only
if it is safe, i.e. you have to be sure that this update won't cause
violation of the constraint *and* that no other person accessing the
database during the time constraint checking is disabled will be able to
cause violations of the constraint).

You state that the recovery model needs to be full; from that I conclude
that you can't lock other users out of the database during the update. Can
you at least take measures to prevent other users from using (updating,
but preferably reading as well) the CLIENTSHISTORY table?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||[posted and mailed, please reply in news]

MAS (mas32677@.hotmail.com) writes:
> Below is a simple UPDATE that I have to perform on a table that has
> about 2.5 million rows (about 4 million in production) This query
> runs for an enourmous amount of time (over 1 hour). Both the
> ChangerRoleID and the ChangerID are indexed (not unique). Is there
> any way to performance tune this?
> Controlling the physical drive of the log file isn't possible at our
> client sites (we don't have control) and the recovery model needs to
> be set to "Full".
> UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
> ChangerRoleID IS NULL
> Any Help would be greatly appreciated!

To add to what Hugo said, if that index on ChangerRoleID is clustered,
and many rows have a NULL value, then you are in for a problem.

It may help to do it batches:

DECLARE @.batch_size int, @.rowc int
SELECT @.batch_size = 50000
SELECT @.rowc = @.batch_size
SET ROWCOUNT @.batch_size
WHILE @.rowc = @.batch_size
BEGIN
UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID
WHERE ChangerRoleID IS NULL
AND ChangerID IS NOT NULL
SELECT @.rowc = @.@.rowcount
END
SET ROWCOUNT 0

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Performance tuning

I have a server with just 4 databases,
the issue is that the sql server does not perform well. In the task manager
it occupies the maximum mount of memory in the system, and maximum cpu time,
the database is just a few gb's(10 -15) and the server has 1 gb ram,
and especially if i use a reporting tool made in vb as to query the database
it works exceptionally slow,
one of my guys says it bcoz of a utility we have working, it bring data from
another server connected to our network via an IPLC link(2mb link), this
utility works on just 2 tables, brings data from the table up there and
imports it in our local database, would there be some better way of
accomplishing this?
is there some way i can constantly monitor what resources the data base is
using?
any suggestions will be greatly appreciated,
Thx & Regards
SantuSantu,
How often do you run the reports? Is the data required to be current and
up-to-date? If not, you could transfer the data locally during non-peak
hours.
As for performance monitoring, SQL Server Profiler and Windows System
Monitor should help.
HTH
Jerry
"Santu" <Santu@.discussions.microsoft.com> wrote in message
news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>I have a server with just 4 databases,
> the issue is that the sql server does not perform well. In the task
> manager
> it occupies the maximum mount of memory in the system, and maximum cpu
> time,
> the database is just a few gb's(10 -15) and the server has 1 gb ram,
> and especially if i use a reporting tool made in vb as to query the
> database
> it works exceptionally slow,
> one of my guys says it bcoz of a utility we have working, it bring data
> from
> another server connected to our network via an IPLC link(2mb link), this
> utility works on just 2 tables, brings data from the table up there and
> imports it in our local database, would there be some better way of
> accomplishing this?
> is there some way i can constantly monitor what resources the data base is
> using?
> any suggestions will be greatly appreciated,
> Thx & Regards
> Santu
>|||Thank you for ur reply Jerry,
About 7-8 ppl use this tool , its constantly running,
and our co is an outbound call center, so the replication of data is
required to be realtime.
THX
"Jerry Spivey" wrote:

> Santu,
> How often do you run the reports? Is the data required to be current and
> up-to-date? If not, you could transfer the data locally during non-peak
> hours.
> As for performance monitoring, SQL Server Profiler and Windows System
> Monitor should help.
> HTH
> Jerry
> "Santu" <Santu@.discussions.microsoft.com> wrote in message
> news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>
>

Performance tuning

I have a server with just 4 databases,
the issue is that the sql server does not perform well. In the task manager
it occupies the maximum mount of memory in the system, and maximum cpu time,
the database is just a few gb's(10 -15) and the server has 1 gb ram,
and especially if i use a reporting tool made in vb as to query the database
it works exceptionally slow,
one of my guys says it bcoz of a utility we have working, it bring data from
another server connected to our network via an IPLC link(2mb link), this
utility works on just 2 tables, brings data from the table up there and
imports it in our local database, would there be some better way of
accomplishing this?
is there some way i can constantly monitor what resources the data base is
using?
any suggestions will be greatly appreciated,
Thx & Regards
Santu
Santu,
How often do you run the reports? Is the data required to be current and
up-to-date? If not, you could transfer the data locally during non-peak
hours.
As for performance monitoring, SQL Server Profiler and Windows System
Monitor should help.
HTH
Jerry
"Santu" <Santu@.discussions.microsoft.com> wrote in message
news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>I have a server with just 4 databases,
> the issue is that the sql server does not perform well. In the task
> manager
> it occupies the maximum mount of memory in the system, and maximum cpu
> time,
> the database is just a few gb's(10 -15) and the server has 1 gb ram,
> and especially if i use a reporting tool made in vb as to query the
> database
> it works exceptionally slow,
> one of my guys says it bcoz of a utility we have working, it bring data
> from
> another server connected to our network via an IPLC link(2mb link), this
> utility works on just 2 tables, brings data from the table up there and
> imports it in our local database, would there be some better way of
> accomplishing this?
> is there some way i can constantly monitor what resources the data base is
> using?
> any suggestions will be greatly appreciated,
> Thx & Regards
> Santu
>
|||Thank you for ur reply Jerry,
About 7-8 ppl use this tool , its constantly running,
and our co is an outbound call center, so the replication of data is
required to be realtime.
THX
"Jerry Spivey" wrote:

> Santu,
> How often do you run the reports? Is the data required to be current and
> up-to-date? If not, you could transfer the data locally during non-peak
> hours.
> As for performance monitoring, SQL Server Profiler and Windows System
> Monitor should help.
> HTH
> Jerry
> "Santu" <Santu@.discussions.microsoft.com> wrote in message
> news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>
>

Performance tuning

I have a server with just 4 databases,
the issue is that the sql server does not perform well. In the task manager
it occupies the maximum mount of memory in the system, and maximum cpu time,
the database is just a few gb's(10 -15) and the server has 1 gb ram,
and especially if i use a reporting tool made in vb as to query the database
it works exceptionally slow,
one of my guys says it bcoz of a utility we have working, it bring data from
another server connected to our network via an IPLC link(2mb link), this
utility works on just 2 tables, brings data from the table up there and
imports it in our local database, would there be some better way of
accomplishing this?
is there some way i can constantly monitor what resources the data base is
using?
any suggestions will be greatly appreciated,
Thx & Regards
SantuSantu,
How often do you run the reports? Is the data required to be current and
up-to-date? If not, you could transfer the data locally during non-peak
hours.
As for performance monitoring, SQL Server Profiler and Windows System
Monitor should help.
HTH
Jerry
"Santu" <Santu@.discussions.microsoft.com> wrote in message
news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
>I have a server with just 4 databases,
> the issue is that the sql server does not perform well. In the task
> manager
> it occupies the maximum mount of memory in the system, and maximum cpu
> time,
> the database is just a few gb's(10 -15) and the server has 1 gb ram,
> and especially if i use a reporting tool made in vb as to query the
> database
> it works exceptionally slow,
> one of my guys says it bcoz of a utility we have working, it bring data
> from
> another server connected to our network via an IPLC link(2mb link), this
> utility works on just 2 tables, brings data from the table up there and
> imports it in our local database, would there be some better way of
> accomplishing this?
> is there some way i can constantly monitor what resources the data base is
> using?
> any suggestions will be greatly appreciated,
> Thx & Regards
> Santu
>|||Thank you for ur reply Jerry,
About 7-8 ppl use this tool , its constantly running,
and our co is an outbound call center, so the replication of data is
required to be realtime.
THX
"Jerry Spivey" wrote:
> Santu,
> How often do you run the reports? Is the data required to be current and
> up-to-date? If not, you could transfer the data locally during non-peak
> hours.
> As for performance monitoring, SQL Server Profiler and Windows System
> Monitor should help.
> HTH
> Jerry
> "Santu" <Santu@.discussions.microsoft.com> wrote in message
> news:3992DE27-D7A2-4536-B3B5-FD57EDA6BF3B@.microsoft.com...
> >I have a server with just 4 databases,
> > the issue is that the sql server does not perform well. In the task
> > manager
> > it occupies the maximum mount of memory in the system, and maximum cpu
> > time,
> >
> > the database is just a few gb's(10 -15) and the server has 1 gb ram,
> >
> > and especially if i use a reporting tool made in vb as to query the
> > database
> > it works exceptionally slow,
> >
> > one of my guys says it bcoz of a utility we have working, it bring data
> > from
> > another server connected to our network via an IPLC link(2mb link), this
> > utility works on just 2 tables, brings data from the table up there and
> > imports it in our local database, would there be some better way of
> > accomplishing this?
> >
> > is there some way i can constantly monitor what resources the data base is
> > using?
> >
> > any suggestions will be greatly appreciated,
> > Thx & Regards
> > Santu
> >
>
>

Performance problem MDX, dependent on location in the code

Hello!

I have MDX in my cube in the tab Calculation. The same MDX perform very differently dependent on where it is located in the code.

Example:

This is in the beginning:

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5111 Sales of Goods33] AS

Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]})

,FONT_FLAGS = '1';

And this is in the middle:

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5111 Sales of Goods22] AS Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]})

,FONT_FLAGS = '1';

[5111 Sales of Goods22]is very slow.

[5111 Sales of Goods33] is a little bit slower than select [Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]from the hiearki.

Why is it like this?

How do I solve my performance problem?

Look forward to any solution.

Best regards,

Tina

Hello!

I give a new example (This is all that exists in my MDX):

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5111 Sales of Goods] AS

Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]}

)

,FONT_FLAGS = '1';

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[520 Subtotal ; Production] AS

Aggregate({ [Account].[AccountOGIS].[OGIS3Digit].&[T]&[5]&[52]&[520]}

* {[CostCenter].[OGIS CostCenter Id Name].&[1000]})

, FONT_FLAGS = '1';

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5112 Income Service] AS

Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5112]})

,FONT_FLAGS = '1';

[5111 Sales of Goods] is fast.

[520 Subtotal ; Production] and [5112 Income Service] is slow.

[5112 Income Service] becomes slow when it is located after [520 Subtotal ; Production].

[520 Subtotal ; Production] seems to bee slow due to it use two diffrent dimensions.

Why is [5112 Income Service] slow when it is located after [520 Subtotal ; Production]?

Best regards,

Tina

|||

Hello again!

I get no answers, but I don't give up, here comes additional information:

The MDX in the Cube (it exists only this code):

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[?5111 Sales of Goods] AS

--Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]}

{[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5111]}

,FONT_FLAGS = '1';

CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[?5112 Income Service] AS

Aggregate({[Account].[AccountOGIS].[OGIS4Digit].&[T]&[5]&[51]&[511]&[5112]})

,FONT_FLAGS = '1';

Query 1 - Very fast:
SELECT { { { [Time].[Time].[Year].&[2006], [Time].[Time].[Year].&[2007] } * { [Version].[Version].[Actual], [Version].[Version].[Budget] } } } ON COLUMNS ,

NON EMPTY { { { DESCENDANTS( [Customer].[CustomerCountry].[All Customer Country], [Customer].[CustomerCountry].[Country] ) } * { DESCENDANTS( [Customer].[CustomerGroup].[All Customer Group], [Customer].[CustomerGroup].[Group Omya] ) } * { DESCENDANTS( [Product].[ProductGroup].[All ProductGroup], [Product].[ProductGroup].[Product] ) } } } ON ROWS

FROM [Complete]

WHERE ( [Account].[AccountOGIS].[?5111 Sales of Goods], [Measures].[Amount] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

Query 2 - Very Slow:

SELECT { { { [Time].[Time].[Year].&[2006], [Time].[Time].[Year].&[2007] } * { [Version].[Version].[Actual], [Version].[Version].[Budget] } } } ON COLUMNS ,

NON EMPTY { { { DESCENDANTS( [Customer].[CustomerCountry].[All Customer Country], [Customer].[CustomerCountry].[Country] ) } * { DESCENDANTS( [Customer].[CustomerGroup].[All Customer Group], [Customer].[CustomerGroup].[Group Omya] ) } * { DESCENDANTS( [Product].[ProductGroup].[All ProductGroup], [Product].[ProductGroup].[Product] ) } } } ON ROWS

FROM [Complete]

WHERE ( [Account].[AccountOGIS].[?5112 Income Service], [Measures].[Amount] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

(The Queries are created from ProClarity).

If I switch place between CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[5111 Sales of Goods] and CREATE MEMBER CURRENTCUBE.[Account].[AccountOGIS].[520 Subtotal ; Production] then Query 1 becomes very slow and Query 2 becomes very fast. WHY?

Best regards,

Tina N M?rnstam

Performance of parameterized queries

I've come up with a query which allows me to perform fairly dynamic
queries depending on which parameters are set when a stored procedure
is called, but I'm not sure if what I'm doing is a performance no-no.
Here is an example:
CREATE PROCEDURE p_TradesGetForProcessing
@.tradeID int = null,
@.underlyingSymbol varchar(20) = null,
@.startTradeDate datetime = null,
@.endTradeDate datetime = null,
@.expirationDate datetime = null
AS
SELECT TradePL.TradeID, TradeID.OptionID,
FROM OptionTradeViewForSelectingTradesToProce
ss
WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
In essence, this allows me to pass in some or all of the filter
parameters. I was hoping that this would be fairly optimal in that the
optimizer has a change to compile the query. What I don't know is if
there is going to be a huge amount of overhead and I'm better off doing
it some other way.Whether some other method will be performant can be determined only by
testing the various methods. However, for a variety options, see:
http://www.sommarskog.se/dyn-search.html
Anith|||bpeikes (ben@.peikes.com) writes:
> I've come up with a query which allows me to perform fairly dynamic
> queries depending on which parameters are set when a stored procedure
> is called, but I'm not sure if what I'm doing is a performance no-no.
> Here is an example:
> CREATE PROCEDURE p_TradesGetForProcessing
> @.tradeID int = null,
> @.underlyingSymbol varchar(20) = null,
> @.startTradeDate datetime = null,
> @.endTradeDate datetime = null,
> @.expirationDate datetime = null
> AS
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
> and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
> In essence, this allows me to pass in some or all of the filter
> parameters. I was hoping that this would be fairly optimal in that the
> optimizer has a change to compile the query. What I don't know is if
> there is going to be a huge amount of overhead and I'm better off doing
> it some other way.
In SQL 2000, the optimizer have no idea of the values, and will arrange
for a one-size-fits all plan. Which in this case is mostly like to
be a table scan, because of the >= conditions. Had you only had =
conditions and all columns had been indexed, it's possible that SQL
Server would pick index concatenation.
For SQL 2005 you can add the hint OPTION (RECOMPILE) to force statement
recompilation - SQL Server will then look at the actual values and use
them for the plan.
I have a longer article on the topic of dynamic searches on my web
site: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx|||bpeikes wrote:
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol =
> @.underlyingSymbol) and (@.startTradeDate is NULL or TradeDay >=
> @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
You're not likely to get index optimization using this method. The only
way to tell for sure is to check the execution plans for each version of
the query, but I'm fairly sure you'll see a lot of index scans (assuming
indexes are in place). What you might want to do is have this procedure
call other procedures depending on the parameters passed. I don't know
if you want to allow all NULL values passed in. If so, the query could
do away with the entire WHERE clause. Once you determine the valid
versions of the query, you can validate the parameters in the main proc
and then design the necessary indexes to support those queries.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
That is a performance no-no, you'll not get good if any index usage on that
query because of the IS NULL and OR stuff.
For search stuff, like below, you should look at using either a ton of IF
ELSE statements or preferably dynamic parameterised SQL...
Build the SQL only for the parameters that are passed, if tradeID is the
only parameter passed then do this...
SET @.nsql = '
select...
from ..
where TradeID = @.tradeID'
exec sp_executesql @.nsql,
N'@.tradeID int',
@.tradeID
The execution plan will be cached and is reusable so it isn't compiled each
time.
Make sure you use parameters as i've done rather than hard code values and
you won't have a problem with injection.
The one draw back is that you will need to permission access to the base
tables/views used in the dynamic SQL rather than execute permission on the
proc; you can get round this problem of security using application roles in
your application.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"bpeikes" <ben@.peikes.com> wrote in message
news:1131574058.424530.205360@.g43g2000cwa.googlegroups.com...
> I've come up with a query which allows me to perform fairly dynamic
> queries depending on which parameters are set when a stored procedure
> is called, but I'm not sure if what I'm doing is a performance no-no.
> Here is an example:
> CREATE PROCEDURE p_TradesGetForProcessing
> @.tradeID int = null,
> @.underlyingSymbol varchar(20) = null,
> @.startTradeDate datetime = null,
> @.endTradeDate datetime = null,
> @.expirationDate datetime = null
> AS
> SELECT TradePL.TradeID, TradeID.OptionID,
> FROM OptionTradeViewForSelectingTradesToProce
ss
> WHERE (@.tradeID IS NULL or TradeID = @.tradeID)
> and (@.underlyingSymbol IS NULL or UnderlyingSymbol = @.underlyingSymbol)
> and (@.startTradeDate is NULL or TradeDay >= @.startTradeDate)
> and (@.endTradeDate is NULL or TradeDay <= @.endTradeDate)
> and (@.expirationDate is NULL or ExpirationDate >= @.expirationDate)
> In essence, this allows me to pass in some or all of the filter
> parameters. I was hoping that this would be fairly optimal in that the
> optimizer has a change to compile the query. What I don't know is if
> there is going to be a huge amount of overhead and I'm better off doing
> it some other way.
>|||Tony Rogerson wrote:
> Hi,
> That is a performance no-no, you'll not get good if any index usage
> on that query because of the IS NULL and OR stuff.
> For search stuff, like below, you should look at using either a ton
> of IF ELSE statements or preferably dynamic parameterised SQL...
> Build the SQL only for the parameters that are passed, if tradeID is
> the only parameter passed then do this...
> SET @.nsql = '
> select...
> from ..
> where TradeID = @.tradeID'
> exec sp_executesql @.nsql,
> N'@.tradeID int',
> @.tradeID
>
To add to what Tony has described, you can use dynamic SQL and send in
all parameters, regardless of whether those parameters exist in the SQL
statement. Assuming your database can support dynamic SQL, you could use
something like the following even if the SQL statement only contains
@.Param1:
Exec sp_executesql @.nvcSql, N'@.Param1 INT, @.Param2 INT, @.Param3 INT',
@.Param1, @.Param2, @.Param3
David Gugick
Quest Software
www.imceda.com
www.quest.com

Performance of aggregation functions

In theory, should the new aggregation functions in AS2005 (LastNonEmpty, LastChild, AverageOfChildren etc.) perform as well as the traditional ones (Count, Sum etc.)?

Just wondering (and because I am using LastNonEmpty for an inventory snapshot scenario)...

In general, the build-in semi-additive measures perform the same way the additive ones do. We have been using LastNonEmpty for Project REAL and we've seen absolutely no differences between measures -- see http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_ Dave|||

We've been struggling with this for several months. Any semi-additive measure or calculation using a semi-additive measure has severe performance issues when we use a date filter in BIDS. OWC and the KPI viewer use a sub-cube approach to this. This can be illustrated in the Project REAL sample data base:

CREATE SUBCUBE [REAL Warehouse] AS ( SELECT ( { [Time].[Calendar].[Calendar Month].&[12]&[2004] } ) ON COLUMNS FROM [REAL Warehouse])

// This query using a semi-additive measure runs in 1 minute & 5 seconds
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
// This query using an additive measure runs in less than 1 second (31 ms)
SELECT
{
[Measures].[Sales Qty]
}
ON COLUMNS
FROM [REAL Warehouse]

DROP SUBCUBE [REAL Warehouse]

// This query using the same semi-additive measure and a where clause runs
// in less than a second
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
WHERE [Time].[Calendar].[Calendar Month].&[12]&[2004]

AdventureWorks has a similar scenario using a date filter and the Average Rate measure (lastnonempty). The fact table is very small, so the performance issue doesn't seem as bad.

Any thoughts as to why we see this kind of performance issue? We've applied all the hotfixes, but still have the issue. We've also tested this under the SP2 CTP (using our cube) and see exactly the same results. We're running on a 2 way dual core server with 4 GB. Our test cube is tiny with only a few hundred thousand fact rows. We can change our browser code to use WHERE instead of SUBCUBE if we have to.

Performance of aggregation functions

In theory, should the new aggregation functions in AS2005 (LastNonEmpty, LastChild, AverageOfChildren etc.) perform as well as the traditional ones (Count, Sum etc.)?

Just wondering (and because I am using LastNonEmpty for an inventory snapshot scenario)...

In general, the build-in semi-additive measures perform the same way the additive ones do. We have been using LastNonEmpty for Project REAL and we've seen absolutely no differences between measures -- see http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_ Dave|||

We've been struggling with this for several months. Any semi-additive measure or calculation using a semi-additive measure has severe performance issues when we use a date filter in BIDS. OWC and the KPI viewer use a sub-cube approach to this. This can be illustrated in the Project REAL sample data base:

CREATE SUBCUBE [REAL Warehouse] AS ( SELECT ( { [Time].[Calendar].[Calendar Month].&[12]&[2004] } ) ON COLUMNS FROM [REAL Warehouse])

// This query using a semi-additive measure runs in 1 minute & 5 seconds
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
// This query using an additive measure runs in less than 1 second (31 ms)
SELECT
{
[Measures].[Sales Qty]
}
ON COLUMNS
FROM [REAL Warehouse]

DROP SUBCUBE [REAL Warehouse]

// This query using the same semi-additive measure and a where clause runs
// in less than a second
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
WHERE [Time].[Calendar].[Calendar Month].&[12]&[2004]

AdventureWorks has a similar scenario using a date filter and the Average Rate measure (lastnonempty). The fact table is very small, so the performance issue doesn't seem as bad.

Any thoughts as to why we see this kind of performance issue? We've applied all the hotfixes, but still have the issue. We've also tested this under the SP2 CTP (using our cube) and see exactly the same results. We're running on a 2 way dual core server with 4 GB. Our test cube is tiny with only a few hundred thousand fact rows. We can change our browser code to use WHERE instead of SUBCUBE if we have to.