SQL Centre of Excellence

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.

image

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.

Imports Microsoft.AnalysisServices.AdomdClient
Module Module1
 
    Sub Main()
        Dim t1 As Integer = System.Environment.TickCount
        Dim t2 As Integer
        Dim t3 As Integer
 
        Console.WriteLine("Testing Speed of 1000 Connections to MOLAP")
        For x = 1 To 1000
            Using con As New AdomdConnection("Data Source=localhost;Catalog=AdventureWorksDW")
                con.Open()
                con.Close()
            End Using
        Next
        t2 = System.Environment.TickCount
 
        Console.WriteLine(String.Format("Time Take={0} seconds", (t2 - t1) / 1000))
 
        Console.WriteLine("Testing Speed of 1000 Connections to Tabular")
        For x = 1 To 1000
            Using con As New AdomdConnection("Data Source=localhost\tabular;Catalog=AdventureWorksDW")
                con.Open()
                con.Close()
            End Using
        Next
        t3 = System.Environment.TickCount
        Console.WriteLine(String.Format("Time Take={0} seconds", (t3 - t2) / 1000))
 
        Console.WriteLine("Press any Key")
        Console.ReadKey()
    End Sub
 
End Module
blog comments powered by Disqus

Page List

Page List