SQL Server Performance

Slow OLAP Connection with .NET

Discussion in 'Analysis Services/Data Warehousing' started by gus, Apr 18, 2005.

  1. gus New Member

    Hi there.

    I'm testing a couple of pages we have.
    .NET Framework v1.1.

    And after a lot of response.write, I found out that there is almost 1 second o time waste in the OLAP connection.

    it seems they are not pooled like SQL connections.
    actually they seem to be pooled, but only in the SAME execution cycle of the same page.
    I mean, two connection in a row in the same page, the second is fast.
    But, if a postback occurs, the first connection is again slow (and the second fast).

    this is more o less the relevent code.


    Dim cn As New ADODB.Connection()
    Dim rd As New ADODB.Recordset()


    cn.ConnectionString = ConfigurationSettings.AppSettings("DefaultConnection")
    cn.Open("", "sa", "")


    rd.Open(array, cn)


    While Not rd.EOF
    ????
    ????
    rd.MoveNext()
    End While

    rd.Close()


    cn.Close()
    cn = Nothing
    rd = Nothing


    the conenction string is like this :


    <add key="DefaultConnection" value="Provider=MSOLAP;Data Source=SERVER;Initial Catalog=CUBEDB"/>

    and I've tried also with MSOLAP.2

    <add key="DefaultConnection" value="Provider=MSOLAP.2;Data Source=SERVER;Initial Catalog=CUBEDB"/>


    OLAP server and cliente are running Analisys Services 2k SP3.

    Any indeas as how enable CUBE connection pooling?
    some trick in the connection string?
    some registry trick?

    Thanx.
  2. satya Moderator

    Are there any external tools are enabled such as Anti virus or spyware between the .NET and OLAP servers?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. gus New Member

    No, there's no proxy, no firewall, no antivirus and no antispyware.

    I've read some links (from the first two sticky topics) about olap connection pooling, but it seems a little over the top for me.
    too much complicated.

    I don't know, but i guess it's gotta be as simple as SQL connection pooling.

    What am I missing?
  4. satya Moderator

  5. gus New Member

    I've read that.

    But i'm still trying to do it in .NET. With no success so far.

    If anyone have some advice about .NET and OLAP connection pooling, please let me know.

    Thanx.
  6. Adriaan New Member

    Couldn't you create a library function like - well this is a rough sketch for one that should work in VBA, not sure what it would be in your .NET app:

    Public Function OpenConnection(Optional blnReset As Boolean = 0) As ADODB.Connection

    Static HoldConnection As ADODB.Connection

    If blnReset Then
    Set HoldConnection = Nothing
    End If

    If HoldConnection Is Nothing Then
    Set HoldConnection = New ADODB.Connection
    HoldConnection.ConnectionString = ConfigurationSettings.AppSettings("DefaultConnection")
    HoldConnection.Open("", "sa", "")
    End If

    OpenConnection = HoldConnection

    End Function
  7. gus New Member

    It's ASP.NET.

    So, I should use session or some kind of persistent pool, with some closing and purging stratregy.
    Because I should reuse connection between different pages and different users.

    But I don't want to get involved in all those complicated business.

    Supposedly that's the way connection pooling works, so I don't want to reinvent the wheel. (cos certainly I'll reinvent it almost square...)


Share This Page