OdbcConnection.Close needs about 2 sec. How to solve?

[Former Member]

Hi,

we have a strange problem. A VB.net 2005 (.net 2.0) application runs on many WinXP clients and accesses a central DB (MaxDB 7.5.0.26) on a server. It works fine for all clients except for one of them. By the help of a profiler we found out on this client that every call of the VB command "myOdbcConnection.Close" needs about 2 seconds, which completely slows down the application (it only opens the connection when needed and afterwards closes it right away, which works fine on all the other clients). The odbc driver is 7.5.0.26.

The client runs on WinXP SP3, the same problem occurs if SP3 is deinstalled and only SP2 is avaiable. We also deactivated the anti virus program ("F-secure") and the firewall - problem remains the same. Not only .net 2.0 (2.0.50727) is installed on the client but also .net 3.0 (need by another program). We deinstalled .net 3.0 - problem remains the same. ("Shared Add-in Extendability Update for Microsoft .NET Framework 2.0" and "Shared Add-in Support Update for Microsoft .NET Framework 2.0" are also installed on the client.)

What could be wrong with the client? What should we have a closer look at?

Thanks in advance for help.

Kind regards,

Michael

Edited by: Michael Poetzsch on Jun 13, 2008 3:40 PM

Edited by: Michael Poetzsch on Jun 13, 2008 3:42 PM

[lbreddemann · Accepted solution]

Hi Michael

> we have a strange problem. A VB.net 2005 (.net 2.0) application runs on many WinXP clients and accesses a central DB (MaxDB 7.5.0.26) on a server. It works fine for all clients except for one of them. By the help of a profiler we found out on this client that every call of the VB command "myOdbcConnection.Close" needs about 2 seconds, which completely slows down the application (it only opens the connection when needed and afterwards closes it right away, which works fine on all the other clients). The odbc driver is 7.5.0.26.

Hmm... opening a db connection every time you need to access the database? That's not really a good idea as it's quite 'expensive' to open a new session - and you don't save many ressources on MaxDB side if you disconnect afterwards.

The usertasks will be there - whether you are connected or not.

A better way would be, to either keep the connections open or, if there are too many clients, pool them.

Anyhow - disconnecting is not so 'expensive' - especially when you committed your last action in the database.

BTW: MaxDB 7.5 Build 26 ... stoneold! Get MaxDB 7.6.04 here from SDN and do an upgrade - really!

> What could be wrong with the client? What should we have a closer look at?

Well, I'm not too sure that it is the client that makes the problem here.

Is the database located on the same server where the VB program runs or are the clients accessing the server via the network?

What you need here is some more timing information.

First of all I would make sure that you really know, how long each disconnect really takes from VB side.

Then I would try to perform a VTRACE of the session disconnect in the DB Kernel. You can also check the "DISCONNECT" line in the knldiag and/or the x_server.log file to match the timing of your VB calls.

I bet there will be some time missing "In-between"...

If it really turns out to be a performance-hole in-between your application and the MaxDB than I'd take a closer look to the network setup.

KR Lars

[TTK]

Hello Michael

Maybe it helps to switch on "Connection Pooling" in the ODBC driver manager (Control Panel -> Amdin Tools -> Data Sources (ODBC): Connection Pooling). Depending on your application, you have to choose the time out value.

Regards Thomas

PS: FYI, SAP MaxDB 7.7 (which should come as download edition soon AFAIK) will contain a .Net Dataprovider for MaxDB.

[Former Member]

Hi,

the db is on a linux server, i.e. not on the client the .net2.0 application is on.

We checked the time myOdbcConnection.Close needs again. It's not about 2sec, as I mentioned in my question, but about 200ms. All times the profiler (ANTS Profiler V2.6 UI.exe) tells seem to be to high by the factor of 10.

I measured the needed time by the method given below in a small separate .net2.0 application. Here the time measurements:

on the "bad" client: 1st call: 18.06.2008 10:41:19: Connection.Open: 484ms 18.06.2008 10:41:19: Sql-Select (50 datasets): 46ms 18.06.2008 10:41:19: Connection.Close: 203ms 2nd call: 18.06.2008 10:41:25: Connection.Open: 46ms 18.06.2008 10:41:25: Sql-Select (50 datasets): 0ms 18.06.2008 10:41:25: Connection.Close: 218ms 3rd call: 18.06.2008 10:41:54: Connection.Open: 15ms 18.06.2008 10:41:54: Sql-Select (50 datasets): 15ms 18.06.2008 10:41:54: Connection.Close: 203ms

on the "good" client: 1st call: 18.06.2008 10:47:01: Connection.Open: 62ms 18.06.2008 10:47:01: Sql-Select (50 datasets): 15ms 18.06.2008 10:47:01: Connection.Close: 15ms 2nd call: 18.06.2008 10:47:14: Connection.Open: 15ms 18.06.2008 10:47:14: Sql-Select (50 datasets): 0ms 18.06.2008 10:47:14: Connection.Close: 0ms 3rd call: 18.06.2008 10:47:33: Connection.Open: 15ms 18.06.2008 10:47:33: Sql-Select (50 datasets): 15ms 18.06.2008 10:47:33: Connection.Close: 0ms

I forgot to mention in my question that the "bad" client was "good" at the beginning, i.e. the user tells that the application in question was as fast as on other clients. But it went slow over time (2 or 3 days) until it reached the times mentioned above.

We decided to install the client again. This time we'll do it step by step: first to add the application in question and let a user work with it a few days, and then step by step to install other software which is not installed on "good" clients to hopefully find the point where the application in question becomes slow.

Thanks for your replies so far.

Kind regards,

Michael

Code the time was measured with

It is placed in a click event of a button, so it can be called again and again by just clicking the button.

        Dim ocmd1L As Odbc.OdbcCommand
        Dim odr1L As Odbc.OdbcDataReader
        Dim nRowsL As Integer
        Dim idL As Integer
        Dim date_startL As Date
        Dim timespanL As TimeSpan


        ' === Open a connection to the db

        ocmd1L = New Odbc.OdbcCommand
        ocmd1L.Connection = New Odbc.OdbcConnection( _
            "DSN=xyz; UID=abc; PWD=abc;")

        date_startL = Date.Now

        ocmd1L.Connection.Open()

        timespanL = Date.Now.Subtract(date_startL)
        Me.txtInfosM.Text &= _
                date_startL & ": " & "Connection.Open: " _
                & timespanL.Milliseconds & "ms" & vbCrLf
        Me.txtInfosM.Refresh()


        ' === Read Ids of about 50 datasets

        ocmd1L.CommandText = "SELECT * FROM myUsers"
        odr1L = ocmd1L.ExecuteReader()

        date_startL = Date.Now

        nRowsL = 0
        Do While odr1L.Read
            idL = odr1L.Item("id") & " "
            nRowsL += 1
        Loop

        timespanL = Date.Now.Subtract(date_startL)
        Me.txtInfosM.Text &= _
                date_startL & ": " _
                & "Sql-Select (" & nRowsL & " datasets): " _
                & timespanL.Milliseconds & "ms" & vbCrLf
        Me.txtInfosM.Refresh()


        ' === Close the connection to the db

        date_startL = Date.Now

        ocmd1L.Connection.Close()

        timespanL = Date.Now.Subtract(date_startL)
        Me.txtInfosM.Text &= _
                date_startL & ": " & "Connection.Close: " _
                & timespanL.Milliseconds & "ms" & vbCrLf
        Me.txtInfosM.Refresh()

        Me.txtInfosM.Text &= vbCrLf

[TTK]

Hello Michael

The Connection Pooling of the Windows Drivermanager is an application side feature, i.e. it is meant for DB client applications. Maybe you could give it a try.

When the application is used for several days, did it run permanent or is it restarted for every use?

Regards Thomas

[Former Member]

Hi Thomas,

I suggested the customer's admin to check whether connection pooling helps.

All the clients are all shutdown over night (that is at the end of a work day).

Kind regards

Michael

[Former Member]

>

> The Connection Pooling of the Windows Drivermanager is an application side feature, i.e. it is meant for DB client applications. Maybe you could give it a try.
> 

Connection Pooling didn't help, unfortunately.

As already mentioned, we decided to install the client again. This time we did it step by step: first to add the application in question and let a user work with it a few days, and then step by step to install other software which is not installed on "good" clients to hopefully find the point where the application in question becomes slow.

Surprisingly the client has not become slow till now and meanwhile all components are installed. The best result of the whole process would be to be able to locate the problem and then to solve it. Nevertheless, it is at least the 2nd best result possible if the problem really disappears :-).

Thanks again for your help. -- Kind regards.

Michael