Query Prepration taking a long time

I have a query - it's moderately comploex, but nothing earth shattering. Problem is, this query takes forever to compile/prepare.

Clicking on the Execute button in SSMS, this query takes 17 seconds to run. Clicking on the syntax check takes 16 seconds to run.

Using the query in an SSRS report is even worse, with the report designer going 100% compute bound for minute at a time on leaving the query editor tab (or executing the query).

Are there any tricks to getting preparation time under control? This feels like there's something pathologically wrong that's making query preparation so slow on this query.

Update: I've found that I can perturb the query in a variety of ways (e.g. commenting out small sub-sections) and end up with queries that parse almost instantly, while other variations take even longer to parse. Unfortunately, none of the variations that parse quickly will produce the correct result - I've commented out some important piece each time.

Cache Warming is one obvious solution it seems.

See the below link.

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!994.entry

Regards,

Jiju

|||

Cache warming doesn't explain it, I don't think. This 16-second overhead is experienced every time I run the query, even when there's no intervening activity at all. I would expect a cache-related delay to come after query prep, for that matter - so I'd see the delay on executing the query, but not in a simple syntax check.

|||

I think I know what's going on. My suspicion is that "parsing" from SSMS or "preparation" from SSRS is running enough of the query to produce the set of tuples for each axis of the query. The query expression used for rows is of the form

Code Snippet

topCount(

order(

filter(

{ ... big expression },

[Measure] > @.Parameter

),

[Measure],

bdesc

),

@.Parameter

)

So determining the set of rows requires actually running the topCout/order/filter over the entire dimensional space of the "big expression", which is many thousands of members - in fact, it's probably the majority of the execution time of the entire query. The measure that's used for the filter and order expressions is the most expensive in the query - calculating it pretty much requires calculating everything else.

So, "preparation" takes so long because SSAS is doing >95% of the work required to run the entire query as a part of this operation.

That's my theory anyway - does it hold water?

|||

Actually, I am pretty sure that SSAS executes the query twice when you preview from the report designer, I'm not so sure about the parsing from SSMS, but seeing as it works with relational datasources as well there is a good chance that it too is issuing an OLEDB command to execute for prepare when you click the parse button.

SSAS does not have the concept of running a query for prepare only like SQL does, for a lot of queries the columns can only be determined by actually running the query, so SSAS ends up actually runs the entire query twice. You would be able to confirm this by using SQL Profiler to trace the SSAS activity while you are working in the report designer.

A simple thing like putting the NON EMPTY clause on an axis means that until all the data has been retrieved, the shape of the final result set cannot be determined.