Performance improvements

Any suggestions on improving this query? It is currently timing out after 5 mins.

With Set [Account] As {[Account].&[17253], Descendants([Account].&[17253], 1, Leaves), Descendants([Account].&[17253], 2, Leaves), Descendants([Account].&[17253], 3, Leaves), Descendants([Account].&[17253], 4, Leaves),Descendants([Account].&[17253], 5, Leaves), Descendants([Account].&[17253], 6, Leaves), Descendants([Account].&[17253], 7, Leaves)}Set [Year] As {[Years].&[2005], [Years].&[2006], [Years].&[2007]} Select CrossJoin({CrossJoin({[Organization].&[1]}, {CrossJoin({[Years].[Years].members}, {[Period].[Period Name].members})})} ,{[Measures].[Current vs. Prior], [Measures].[Value]}) On Columns,Crossjoin([Account], [Scenarios].&[1]) on Rows From TestCube

We're trying to get all the descendants of account #17253. Just using the Descendants function causes even worse performance.

thanks,

Andrew

First try looking at the performance guide and see what are the recommendataions for improving query performance.

Please also take a look at NON EMPTY, NONEMPTY and nonemptycrossjoin. And NON_EMPTY_BEHAVIOR property.

Here some more information

http://blogs.msdn.com/bi_systems/articles/162852.aspx

http://www.sql-server-performance.com/wp_msas_9.asp

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.