ASP->COM->OLAP

[TomSark]

Hi,

SQL 2000, SP2, etc.

We are issuing MDX queries via a COM+ object in an ASP application using ADO (not ADOMD). We are noticing major CPU spikes when the component issues MDX queries to the cube. Analysis services reports that queries aren't consuming any time. NICs are OK. We are the only users on the server.

Is there an issue with ADO hitting OLAP? We know it flattens the MDX result set, but only 30 rows or so are coming back from the queires. We use ADO because it can save the result set as XML. Why the spikes? Any clues?

Thanks Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TomSark]

Ok fine, I'll answer my own post. We have worked with Microsoft and have made a few determinations.

Do NOT issue VBA function calls in MDX queries. The Analysis Services parser serializes the sections of the queries (with then select then from then where). This serialization in itself is a bottleneck. Within this serialization, VBA DLLs are serialized, in which a spin lock is issued, the DLL is loaded, and the result is a lot of waiting for VBA to get processed in an already bottlenecked design. Microsoft is working on a fix for this and should be released in SP3.
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com