February 25, 2016 at 10:52 am
Hi,
We are currently using a combination of SQL Server relational tables and values coming from an SSAS database. The results coming from the SSAS db are being joined with tables to produce the final result. In order to achieve that we are using a linked server for the MDX queries. The linked server uses OLE DB MSOLAP Provider and the servers are on the same machine.
The issue that we have observed is as follows:
When the rows returned are around 20,000, it is approximately the same speed as running directly against the SSAS db. However as the rows are increasing, the time almost doubles for each 30,000 extra rows returned, whereas the executing on the cube only changes marginally.
The below MDX query via linked server takes 10 seconds. Running directly on cube via SSMS query takes 9 seconds. For 50,000 then the cube takes 10 seconds, but the Linked Server Query takes 18 seconds. As the data set gets large the difference between them variables massively. 100,000 rows takes 12 seconds on SSAS, and 30 seconds via linked server.
EXEC
('
SELECT {
[Measures].[MemberId],
[Measures].[Clicks]
} ON COLUMNS
,
SUBSET([Item].[CompanyItem].[CompanyItem], 0, 20000)
ON ROWS
FROM [AnalysisServicesCube]
'
)AT [Test.Cube]
I understand that there is some overhead in sending MDX queries through linked server, but was wondering if someone has any ideas on why the gap in performance grows dis-proportionally to the number of rows and if it is possible to limit the effect.
Any suggestions more than welcome.
February 26, 2016 at 3:09 am
Whenever I have *had* to do this (emphasis on had because if you're having to do this then you have an underlying design issue in my opinion) I have used OPENQUERY rather than EXEC. That's just personal preference though I guess.
You don't mention if the SSAS instance is on the same physical box as the query that you are running. Regardless, you will always encounter a network performance overhead with remote server queries - this is possibly why you are seeing performance get worse the more rows that are returned.
Is there not a way to use a reporting tool (SSRS for example) to return the high level results from OLAP and then provide a hyperlink for users who want to see the detail of those 20,000+ rows? It's called SQL Server ANALYSIS Services. It's high level. How much of an analytical decision can be made from looking at 20,000+ rows?
Anyway.....;-)
There are some performance tips for this situation here: https://support.microsoft.com/en-us/kb/218592#bookmark-4
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply