Showing posts with label timing. Show all posts
Showing posts with label timing. Show all posts

Friday, March 30, 2012

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.

Friday, March 9, 2012

Performance - Run Long Queries

A question about long running queries. We have an
application that is running across a WAN. We are trapping
long running queries. Does the timing of the query
include serving the data set back to the requestor?
L
Yes it does. That's why it is highly recommended that a stored procedure is
used because it is only running on the server and can take advantage of
cached plans.
"Laurel" <landj.griffith@.attbi.com> wrote in message
news:4e0801c48098$347dcae0$a301280a@.phx.gbl...
> A question about long running queries. We have an
> application that is running across a WAN. We are trapping
> long running queries. Does the timing of the query
> include serving the data set back to the requestor?
> L
>

Performance - Run Long Queries

A question about long running queries. We have an
application that is running across a WAN. We are trapping
long running queries. Does the timing of the query
include serving the data set back to the requestor?
LYes it does. That's why it is highly recommended that a stored procedure is
used because it is only running on the server and can take advantage of
cached plans.
"Laurel" <landj.griffith@.attbi.com> wrote in message
news:4e0801c48098$347dcae0$a301280a@.phx.gbl...
> A question about long running queries. We have an
> application that is running across a WAN. We are trapping
> long running queries. Does the timing of the query
> include serving the data set back to the requestor?
> L
>

Performance - Run Long Queries

A question about long running queries. We have an
application that is running across a WAN. We are trapping
long running queries. Does the timing of the query
include serving the data set back to the requestor?
LYes it does. That's why it is highly recommended that a stored procedure is
used because it is only running on the server and can take advantage of
cached plans.
"Laurel" <landj.griffith@.attbi.com> wrote in message
news:4e0801c48098$347dcae0$a301280a@.phx.gbl...
> A question about long running queries. We have an
> application that is running across a WAN. We are trapping
> long running queries. Does the timing of the query
> include serving the data set back to the requestor?
> L
>