SAPConnection Pooling and Web Services

[Steven_UM]

Hi,

We have build a lot of .NET web services which are connecting to various SAP systems (depending on the front-end user).

To improve performance we were considering using the SAPConnectionPool class ...

We have some questions about this class:

1) While thinking about it we were wondering if there is actually any benefit considering the stateless nature of web services ...

How would we actually manage that the connection pool remains in place for each subsequent web service call (so the pool does not need to restart over and over again, obviously loosing its benefits) ...

Would it work by add methods to the Global class?

2) To get at least one open connection available to each SAP system before the first web service is called, would it be sufficient to just ask for a connection to each system and immediately return it again to the pool (this would then be executed in the global class )

Perhaps we are missing some fundamental stuff here but be patient with us ... we are only working for 6 months in the .NET environment (coding like hell in C# )

Thx,

Steven

-

---

Here is the code we added to the global.asax:

public static void InitializeSAPConnectionPooling()

{

// Try to get the SAP connections from the application memory

Hashtable wSAPConnections = (HashtableHttpContext.Current.Application["SAPConnections"];

// When we have it

if ( wSAPConnections != null )

{

// For each connection, start up a connection in the pool

foreach( DictionaryEntry wElement in wSAPConnections )

{

// Get a connection

SAPConnection wConnection = SAPConnectionPool.GetConnection((string)wElement.Value);

// Return the connection back to the pool SAPConnectionPool.ReturnConnection(wConnection);

}

}

}

[Steven_UM]

Thanks to both of you for the quick responses!

Steven

[reiner_hille-doering]

Let me shortly explain when Connection Pool is usefull and how it works:

Connection Pool makes sense, if you have many subsequent calls with the same connection string,e.g.

string connectionString = "...";

...

SAPProxy1 proxy = new SAPProxy1();

...

proxy.Connection = SAPConnectionPool.GetConnection(connectionString);

proxy.CallMethod();

SAPConnectionPool.ReturnConnection(proxy.Connection);

...

proxy.Connection = SAPConnectionPool.GetConnection(connectionString);

proxy.CallMethod();

SAPConnectionPool.ReturnConnection(proxy.Connection);

This is faster than creating and closing the connection twice.

ReturnConnection will keep the connection open and store it in a kind of static hashtable with the connection string as key. Later the call to GetConnection will eventually fetch the open connection with fitting connection string from pool. If the open connection is not reused within a certain period, it is automatically closed and removed from the pool.

So the important thing is SUBSEQUENT and SAME CONNECTION STRING.

In all other cases the SAPConnectionPool will create new connections, so it would not help.

I don't understand the code snipped you posted, but it seems somehow useless. Just regard the ConnectionPool as static connection buffer - you don't have to deal with Application state to use it.

[Steven_UM]

Hi Reiner,

Thx for the response!

I understood the meaning of the pooling - the tricky question is whether the hashtable with the open connections 'survives' between 2 web service calls ...

If not then the whole point of using the connection pooling is useless to us ...

My code snippet is perhaps looking useless as I lost some characters during the paste ... its idea is simply to have for each SAP server one connection being available when the server restarts ...

unfortunately this does not makes much sense when there is a 'keep alive' check build in ...

Some more questions then:

1) Can we have control over the 'timeout'?

2) Is there a way I can check whether the returned connection by the pool is a new one or an existing one?

3) How does this work together with "load balancing" ... I guess I will get the same connection again when my connection string directs towards a message server in stead of an application server?

4) Is a connection being opened once the connection object is returned by the pool or only when the actual BAPI call is executed?

Thx,

Steven

[reiner_hille-doering]

0: The static hashtable in the connection pool survives WebService calls. It's just a static variable. Statics are part of the app domain - which contains constant between calls.

1: Yes, this can be set in web.config. Here's an example:

<configuration>

<configSections>

<section name="SAPConnectionPool" type="System.Configuration.NameValueSectionHandler" />

</configSections>

<SAPConnectionPool>

<add key="InitialCapacity" value="10" />

<add key="MaxIdleTime" value="200" />

<add key="CleanupInterval" value="3" />

</SAPConnectionPool>

</configuration>

Note that the syntax slidely changes with NCo 2.0.

2: No.

3: Indeed the connection is only load-balanced when it is created. So if e.g. your web service requests would be serialized for some reason and thus the pool would always return the same connection, you would only load one app server.

4) As far as I know in NCo 1.x the connection is always open when it is returned from GetConnection(), thus if it is a new one, it is automatically opened by GetConnection(). But I'm not sure in the moment.

[Former Member]

Yes, the GetConnection() always returns a opened connection.

Guangwei

[Former Member]

Hi Reiner,

Is their a limit to the size of the 'InitialCapacity' key?

Regards

wrighnik

[reiner_hille-doering]

No, but in fact this setting doesn't have any recognisable effect: It's the initial capacity of the Hashtable for the connections. If they are more connections put back to the pool, the hashtable is anyway extended. NCo 1.x doesn't have a feature to limit the number of connection in the pool and neither for connections outside the pool. NCo 2.0 has such features.

[Former Member]

> NCo 1.x doesn't have a feature to

> limit the number of connection in the pool and

> neither for connections outside the pool. NCo 2.0 has

> such features.

I read how it works and I think it's really useful.

However, I think the 'used connections counter' should be based on connectionstrings instead of being global: this will improve scalability as we can give more control to the pool balancing.

(ref. "Note that the counter is global. Thus it's not usefull to use this feature if an application has connections to different SAP systems in parallel." in the Config.ConnectionPoolSettings.MaxOpenConnections Property)

another question is: is there a timeout for the block that happens to the methods that return a connection when the MaxOpenConnections has reached?

Message was edited by: matro

[reiner_hille-doering]

> However, I think the 'used connections counter'

> should be based on connectionstrings instead of being

> global: this will improve scalability as we can give

> more control to the pool balancing.

Thanks for the feedback. Putting the limit on a per-conection string basis would be possible, but I don't think it would be really usefull: Usually connetion strings are different - at least user name and password differ - even if the point to the same app server.

Unfortunately it's allmost impossible to detect from a connection string to which server it would exactly go. Imagine that you could use an IP, name with or without DNS domain, load balancing and so forth.

What could be done and might be usefull is to give you as a user the possibility to hook in your own code that decides whether GetConnection should block or not.

A feasable way is to offer a static event on Connection

that is fired on every "opening" connection. What do you think?

> another question is: is there a timeout for the block

> that happens to the methods that return a connection

> when the MaxOpenConnections has reached?

Currently not. The question would be how to proceed after timeout. Ignore the limit and just open the connection? Or return null from GetConnection?