OpenQuery and MDX

Question

Hi,

I have an MDX query which takes approx 8 mins to execute. Then Further i want the result of this MDX query into SQL table, hence i used OpenQuery but this is taking huge amount of time, same is with OpenRowSet and SSIS package. Are there any options to make this execution faster.

The MDX query returns 1,40,000 records with 13 columns.

Thanks,
Shreyas

Answer 1

OpenQuery or OpenRowset will always be slower than directly querying SSAS as there is the overhead of transferring the results from SSAS to SQL. If you run the query in SSMS does it take the same amount of time or is it significantly faster?

Also are you talking about 140 thousand rows or 1.4 million? (the comma is in a strange place in your post)

---

http://geekswithblogs.net/darrengosbell - please mark correct answers

Answer 2

When i execute just MDX in SSMS it takes approx. 8 minutes, but when queried through OpenRowSET it took 2 Hours. The record count is 140 Thousand with 13 Columns. Is there any optimised way of pulling this data.

Thanks,
Shreyas

Answer 3

When you tried with SSIS were you also using OpenQuery/OpenRowset? I would have thought that reading from a straight OLEDB connection to SSAS should have been as fast as SSMS.

---

http://geekswithblogs.net/darrengosbell - please mark correct answers

Answer 4

Whats Darren saying is right, i've done the same experience. In one of my Projects there was the same requirement. I've tried everything. Finaly i'll get the best performance from an Assembly. I wrote a .Net Assembly to do it for me. Some tries as CLR Assembly ended in Memory leaks or Out of Memory for CLR in SQL Server. The CLR Memory is restricted to few hundret MB. After that i use this Assembly as a WebService with no restrictions as in SQL Server CLR. Best Performance since that. To find the best technic in .Net was not so easy.

1. Execute MDX and write it into a Dataset
2. Analyse the Result as Structure, Columns etc.
3. Prepare Table (relational) for insert.
4. Create a special sqldataadapter "DA" from the Resultanalysis of 2.
5. Push the Dataset into "DA"
6. Let .Net do the Update for you -> DA.Update.

This was the fastets and stablest.

For more Details ask me.

---

Visit my Blog at my Company ComeUp GmbH & Co. KG to contact me

Answer 5

Hi Andy,

it's been a while since your post but would you mind to share your code? it seems to be a pretty clever approach.

Thanks and regards,
Dirk