query plan caching?

SQL Server has the concept of caching the query plan. Parameterized queries can take advantage of this feature.

I'm wondering if there's an equivalent feature in SSAS. If I use MDX parameters (i.e. using @.Param in the query and setting the Parameters object of the AdomdCommand object) and run the same query twice with similar parameter values, will SSAS be able to reuse the query plan at all?

What I'm wondering is whether it is better (a) to use parameters and then StrToSet to convert those parameters into MDX objects... OR (b) to just run dynamic MDX with no parameters. (Yes, I'm referring to how Reporting Services does MDX queries. Wondering if it would be worth suggesting to the SSRS team that they take the parameter values and build dynamic MDX instead of using MDX parameters.)

There is no equivalent for cached query plans in the SSAS 2005 version.

> What I'm wondering is whether it is better (a) to use parameters and then StrToSet to convert those parameters into MDX objects... OR (b) to just run dynamic MDX with no parameters.

In SSAS 2005, building MDX on the fly is always better than using StrToSet and parameters.

|||

Thanks Mosha. That answered my question. I reported this as a suggestion for Katmai SSRS:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263472

|||I diagree that this is SSRS issue. SSRS is absolutely correct using StrToSet( @.param, CONSTRAINED ) for many reason, one being security. This should be reported as suggestion for Katmai SSAS - to optimize StrToXXX( , CONSTRAINED) family of functions.

Query Load Balancing

Hi,

We have a massive database that users run complex queries on. The queries are never the same so caching cannot be used too much effect. When many queries are run at one time by the various users the system slows down.

What we are trying to achieve is maybe have a primary SQL Server 2005 machine and multiple (4-5) SQL Server 2005 secondary machines. The primary machine would get the query and would decide based on loads as to which secondary machine would fulfill the query.

We are not worried about failover and clustering and all that fun stuff. Our primary concern is to achieve the quickest execution of the query. We were wondering if SQL Server 2005 has any feature that enables query load balancing of some kind.

Thanks

Sounds to me that you should seriously investigate replication.

Now your comment "The primary machine would get the query and would decide based on loads as to which secondary machine would fulfill the query." is not addressed by SQL Server out of the box, but you could maybe design some custom code.

My main point here is that I would NOT recommend looking at database mirroring for your solution. Mirroring is primarily a failover/hot standby solution for availability.

Thanks,

Mark