0

I have a .NET 6 Web API that is hosted on server A. SQL Server is on server B. Both servers are in the same network.

Each endpoint of the Web API makes use of the Entity Framework to query data from the database.

I wanted to enable pooling at the Entity Framework level so that connections are reused. But I'm reading the SQL Server has its own pool of connections anyways. Link: https://learn.microsoft.com/en-us/ef/core/performance/advanced-performance-topics?tabs=with-di%2Cwith-constant#dbcontext-pooling

Note that DbContext pooling is orthogonal to database connection pooling, which is managed at a lower level in the database driver.

So I want to ask - What is the difference between pooling at Entity Framework vs SQL Server level?

variable
  • 8,262
  • 9
  • 95
  • 215
  • Note "SQL Server level" is ADO.NET, not the database server. Both ADO.NET and DbContext pooling are client-side. – Dan Guzman Feb 16 '22 at 19:13
  • @DanGuzman - where can I find more info on the sql server level (database server) pooling? Is there anything like that? – variable Jul 10 '22 at 10:41
  • 1
    @veriable, the link you provided in the comment on the answer explains connection pooling with ADO.NET. The DbContext connect pooling link you just added to your question is about EF object pooling and not directly related to the lower level database connection in the stack (the database connections you see on the server). Again, all pooling is not at the SQL Server database engine level; it is entirely managed by the client. The only involvement SQL Server has in connection pooling is to support connection resets for reused connections (`sp_reset_connection` over the TDS protocol). – Dan Guzman Jul 10 '22 at 10:53
  • 1
    Other APIs (e.g. JDBC) manage connection pooling differently. For example, the MS JDBC driver supports connection pooling but the pools are managed at a higher level (e.g. middleware like hikari, apache, etc.). The query in [this answer](https://stackoverflow.com/questions/72643998/connection-pooling-for-same-connection-string-but-from-different-hosts-ip-in-sql/72644369#72644369) might be helpful to monitor pooled connections at the server level. – Dan Guzman Jul 10 '22 at 10:58

1 Answers1

4

I wanted to enable pooling at the Entity Framework level so that connections are reused

Entity Framework doesn't get involved at the "connections are reused" level. Pooling in that regard is a process of ADO.net forging e.g. a TCP connection to a database (which is a relatively long and resource intensive operation) and keeping it open. When your old school code like using var conn = new SqlConnection("connstr here") calls conn.Open() one of these already-connected connections is leased from the pool and handed to you; you do some queries and then Close (or dispose, which closes) the connection but that doesn't actually disconnect the database; it just returns the connection to the pool

As noted, EF doesn't get involved in this; it's been a thing since long before EF was invented and is active by default unless you've turned it off specifically. EF will use ADO.net connections like any other app, so it already benefits from connection pooling passively

The article youre reading is about a different set of things being pooled. Typically a DbContext is a light weight short lived thing that represents a device that forms queries to a database; you're supposed to make one, use it for a few queries and then throw it away.

It's designed for fast creation but that doesn't mean there aren't minor improvements to be had and if you've identified a situation where you need to wring every last drop of performance out of the system then EF offers a way to dispense with the typical "throw it away and make another" route of making sure you're using a fresh DbContext by providing a facility for DbContexts to be pooled, and rather than being made anew they're reset

It's probably unlikely you're in that place where pooling your contexts would make a significant difference; you're asking about enabling connection pooling but in all likelihood it'll be already enabled because you would know if you'd put "Pooling=false" in a connection string.

For more info on connection pooling, see https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • 1
    Ok I now understand your point about EF pooling- that it is essentially the c# object instance that is pooled. And I also understand that this is orthogonal to the database connection pooling. May I ask - is ado.net pooling (which is used to run the EF queries) the same as `sql server level` database connection pooling? The link says that the default pool size is 100 connections. So this is referring to the `sql server level` database connection pooling? Or is this the client side pooling and if so then what is the concept of sql server side connection pooling? – variable Jul 10 '22 at 10:37