Archive

Posts Tagged ‘Linked Server’

OpenQuery and Ole DB behaviour – MDX runs twice

March 13, 2013 4 comments

Issue: Linked Server + Ole DB provider to SSAS’s combo makes the MDX to be executed TWICE which means doubling the execution time.

Resolution: Unfortunately, there is no way around it. The limitation is Linked Server with Old DB provider. According to my finding, “openquery” theoretically performs better than “exec @query At <linked server>” but on the other hand, the latter supports string longer than varchar(8000) or nvarchar(4000).  Hence, maybe we can use openquery when the string length <= 4000 and use “exec @query At <linked server>” when string >  4000 (nvarchar)

Now I’ll walk through my findings:

Query using openquery:

DECLARE @CubeLocation AS nVARCHAR(100)='<YOUR LINKED SERVER>'
DECLARE @mdx AS NVARCHAR(4000)=
      'with member measures.x as 100 select {measures.x} on 0 from <Your Cube>'
DECLARE @SQL AS NVARCHAR(4000) = 
      'SELECT * FROM OPENQUERY('+@CubeLocation+', '''+ @mdx +''')'      
EXEC sp_executesql @SQL 
Go

Run the above query first time, I get the following:

Image

Run the above query 2nd time, I get the following:

Image

Conclusion: For any new query, it is going to be executed TWICE, the subsequent execution will be run once. However, if you change the query string, it will be executed TWICE again. For example, I add an space before “with” or after “member” or anywhere in the query.

Query using “exec @query At <linked server>”:

DECLARE @CubeLocation AS nVARCHAR(100)='<YOUR LINKED SERVER>'
DECLARE @mdx AS NVARCHAR(4000)=
      'with member measures.x as 1 select {measures.x} on 0 from <Your Cube>' 
DECLARE @SQL AS NVARCHAR(4000) = 'Exec (''' + @MDX + ''') At ' + @CubeLocation            
EXEC sp_executesql @SQL 
GO

Run the above query  first time, I get the following:

Image

Run the query 2nd time and I get the same behaviour: it is executed TWICE.

Conclusion: Regardless how many time the same query runs, it is always executed TWICE.

For a tiny MDX query, you probably won’t be able to see the time gap between two MDX query as it is executed within milliseconds. But for a long running MDX queries, you will notice there will be non-overlap time gap between the execution duration. I’ve tested a MDX query which runs about 53 seconds natively on SSAS. And If I run it through openquery, you’ll see the below:

Image

As you can see, the query is executed TWICE and the total duration is 54143ms+44697ms = 1.647 minutes. However, the dataset is about 3298 rows and 700+ columns which took about 3 minutes to render in SQL management studio. And I can verify that even SQL Profiler reported the “query end” event, the SSMS is still busy with rendering the result set in the data grid. Also, I verified that if using client tools which support direct SSAS connectivity, e.g. Excel native PivotTable or ADO.NET provider, the query is executed ONCE only.

Advertisements