Update (06/07/2015). As a few people have pointed out this is more of a named instance issue than a “tabular” specific issue. I added some details on how to optimise SSAS connections here
http://blogs.prodata.ie/post/Optimising-Connections-to-SSAS-(MOLAPTabular).aspx
http://blogs.prodata.ie/post/Solved-slow-SSAS-connections-on-Hyper-V.aspx
— original post ——————————————————————————————————
Seasoned developers are often shocked when they find out the SSAS doesn’t have any support for connection pooling.
With SSAS MOLAP this didn’t matter so much as connections are much faster to acquire than say with the DBEngine and is considered trivial, so we don’t pool connections and neither does the product.
As a quick test I wrote a console application to open 1,000 connections on both the molap and tabular engine and counted the time taken.
In this test the tabular engine was some 236% slower at opening connections, on a test to a remote server it was still about 2x slower.
This will really hurt workloads like reporting services which opens a lot of connections for small parameter queries, and so far this has really hampered the tabular model in competing against MOLAP models in load testing. Even if the tabular queries are efficient the additional work to establish a connection is dragging down throughput at load.
If you are writing applications that have high concurrency tabular connections it certainly raises the question on if you should manually pool the connections rather than rely on analysis services.
if you are using say reporting services it also raises the importance of considering the usage of cached data sets for re-usable parameter queries and slowly changing data.
Here is the source code if you want to repeat the test.
<!-- wp:preformatted -->
<pre class="wp-block-preformatted">Imports Microsoft.AnalysisServices.AdomdClient</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted">Module Module1</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Sub Main()</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Dim t1 As Integer = System.Environment.TickCount</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Dim t2 As Integer</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Dim t3 As Integer</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Console.WriteLine("Testing Speed of 1000 Connections to MOLAP")</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> For x = 1 To 1000</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Using con As New AdomdConnection("Data Source=localhost;Catalog=AdventureWorksDW")</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> con.Open()</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> con.Close()</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> End Using</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Next</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> t2 = System.Environment.TickCount</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Console.WriteLine(String.Format("Time Take={0} seconds", (t2 - t1) / 1000))</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Console.WriteLine("Testing Speed of 1000 Connections to Tabular")</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> For x = 1 To 1000</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Using con As New AdomdConnection("Data Source=localhost\tabular;Catalog=AdventureWorksDW")</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> con.Open()</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> con.Close()</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> End Using</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Next</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> t3 = System.Environment.TickCount</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Console.WriteLine(String.Format("Time Take={0} seconds", (t3 - t2) / 1000))</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Console.WriteLine("Press any Key")</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> Console.ReadKey()</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted"> End Sub</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted">End Module</pre>
<!-- /wp:preformatted -->
