SQL Server Performance Forum – Threads Archive
Slow OLAP Connection with .NET
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.
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.
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?
Check this MSDNhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql2k_connpooling.asp link specifically from CP. 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.
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.
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
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…)
]]>