[ACCEPTED]-Why is it bad practice to make multiple database connections in one request?-database-connection

Accepted answer
Score: 10

Database connections are a limited resource. Some 23 DBs have a very low connection limit, and 22 wasting connections is a major problem. By 21 consuming many connections, you may be blocking 20 others for using the database.

Additionally, throwing 19 a ton of extra connections at the DB doesn't 18 help anything unless there are resources 17 on the DB server sitting idle. If you've 16 got 8 cores and only one is being used to 15 satisfy a query, then sure, making another 14 connection might help. More likely, though, you 13 are already using all the available cores. You're 12 also likely hitting the same harddrive for 11 every DB request, and adding additional 10 lock contention.

If your DB has anything 9 resembling high utilization, adding extra 8 connections won't help. That'd be like 7 spawning extra threads in an application 6 with the blind hope that the extra concurrency 5 will make processing faster. It might in some 4 certain circumstances, but in other cases 3 it'll just slow you down as you thrash the 2 hard drive, waste time task-switching, and 1 introduce synchronization overhead.

Score: 4

It is the cost of setting up the connection, transferring 13 the data and then tearing it down. It will 12 eat up your performance.

Evidence is harder 11 to come by but consider the following...

Let's 10 say it takes x microseconds to make a connection.

Now 9 you want to make several requests and get 8 data back and forth. Let's say that the 7 difference in transport time is negligable 6 between one connection and many (just ofr 5 the sake of argument).

Now let's say it takes 4 y microseconds to close the connection.

Opening 3 one connection will take x+y microseconds 2 of overhead. Opening many will take n * (x+y). That 1 will delay your execution.

Score: 2

Setting up a DB connection is usually quite 9 heavy. A lot of things are going on backstage 8 (DNS resolution/TCP connection/Handshake/Authentication/Actual Query).

I've had an issue once with some weird 7 DNS configuration that made every TCP connection 6 took a few seconds before going up. My login 5 procedure (because of a complex architecture) took 4 3 different DB connections to complete. With 3 that issue, it was taking forever to log-in. We 2 then refactored the code to make it go through 1 one connection only.

Score: 1

We access Informix from .NET and use multiple 14 connections. Unless we're starting a transaction 13 on each connection, it often is handled 12 in the connection pool. I know that's very 11 brand-specific, but most(?) database systems' cilent 10 access will pool connections to the best 9 of its ability.

As an aside, we did have 8 a problem with connection count because 7 of cross-database connections. Informix 6 supports synonyms, so we synonymed the common 5 offenders and the multiple connections were 4 handled server-side, saving a lot in transfer 3 time, connection creation overhead, and 2 (the real crux of our situtation) license 1 fees.

Score: 0

I would assume that it is because your requests 15 are not being sent asynchronously, since 14 your requests are done iteratively on the 13 server, blocking each time, you have to 12 pay for the overhead of creating a connection 11 each time, when you only have to do it once...

In 10 Flex, all web service calls are automatically 9 called asynchronously, so you it is common 8 to see multiple connections, or queued up 7 requests on the same connection.

Asynchronous 6 requests mitigate the connection cost through 5 faster request / response time...because 4 you cannot easily achieve this in PHP without 3 out some threading, then the performance 2 hit is greater then simply reusing the same 1 connection.

that's my 2 cents...

More Related questions