Answered Sql Dataserver - Why So Many Connections?

[Chris Hughes]

Hi

I'm hoping that somebody can explain to me why the OpenEdge implementation of the SQL dataserver requires so many connections for each physical user to a SQL database?

It is likely I'm going to start trialing different pf file parameters but just want to understand the underlying technology and the way it works first.

So we have a parameter to control the cache connections from the Dataserver, but then also the ODBC SQL Native client has its own multiple connections capability as well....

Our software can easily go 20 connections or more in a session for one user.

So how does this work, can one connection only handle one open cursor ie 3 connections requred for....

for each customer (1 connection)

for each invoice (1 connection)

for each invoiceline (1 connection)​

Any help very much appreciated.

Thanks

Chris.

 

[TheMadDBA]

The default connection management is pretty awful for the OE Dataservers (imo). Here is a KB with two parameters you can use to somewhat manage the number of connections per client, as well as a high level description of the internals.

Progress KB - How to limit connections to Microsoft SQL Server when using DataServer for Microsoft SQL Server?

Learn to love SQL Server Management Studio so you can see what each connection is (or more likely is not) doing but also keep in mind that limiting the number of connections may cause performance issues.

 

[Chris Hughes]

Thanks MAD, an interesting article. I also read the documentation and confirmed what I originally thought.....

The DataServer for Microsoft SQL Server is enhanced with the ability to form a
connection pool. A connection pool is a set of database connections that are available
for an application to use and reuse without having to be reestablished. Connection
pooling significantly improves the cursor management associated with no-lock queries,
particularly multi-table joins. Creating and tearing down connections can be resource
intensive. Using a pooled connection to keep existing connections alive results in
significant performance gains because the DataServer avoids the overhead of making
a connection for each request. ABL applications that open multiple no-lock queries and
handle their results simultaneously experience the best cumulative performance gains
from connection pooling.

and

Without a connection pool, firehose cursors would block an application until a full
result set is retrieved. Because of this, when connection pooling is disabled,
firehose cursors are also disabled. By maintaining multiple connections and one
cursor per connection, read-only requests only block the connection on which they
retrieve results, freeing ABL applications to continue processing data on the other
connections.

I too question the overall approach of this, also doing a bit of googling reveals that using Windows Authentication for users compounds the problem as they build their own connection pools. Although it is an obvious security problem using a fixed SQL login would share the connection pool between all users.

Time for me to have a play with parameters, although all docs seem to suggest the best settings are the defaults

 

[TheMadDBA]

Some really interesting design decisions were made over the years for the Dataservers.. it does work pretty well once you embrace the madness a bit. Especially if you pass the point of no return and start using the SQL specific calls (stored procs, pass through queries, etc).

Unless you are anticipating thousands of users I might just make sure nobody has messed with the connection settings on your SQL instance (defaults are set to grow as needed up to 32k connections) and just try and forget the extra connections/cursors exist. The actual overhead per cursor is pretty small on SQL/Oracle.