May 26, 2010 at 1:02 am
Dear All,
I was wondering whether it's better to open a single database connection then reuse it all over the application or keep opening new connections for each module thats being used.
In first method the connection after some time becomes slow when executing SQLs, i think due to incomplete clearing of result sets from previous queries.
In second method i am afraid opening all those connections might consume resources from SQL server and start slowing things down too.
We are using RDO and ODBC for connectivity with SQL Server.
Waiting forward for your replies and thanks in Advance
Nader
May 26, 2010 at 6:30 am
You can open more then one connection , as per i have tested it does not affect much on the resources of sql server, SQL SERVER 2005 by default provides 32767 number of connection at atime (can check in sp Configure ) you can even increase it .
Hope it helps.
May 26, 2010 at 6:33 am
Thanks for ur reply
May 26, 2010 at 6:55 am
I would suggest using connection pooling. As much as possible, reusing connections is the way to go because it reduces the amount of memory that connections take up (new memory is used for each connection). It's also faster because the mechanisms required to establish a new connection slow things down. Finally, dropping connections also causes more work on the server as memory is cleaned up & deallocated.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 26, 2010 at 7:04 am
Grant Fritchey (5/26/2010)
I would suggest using connection pooling. As much as possible, reusing connections is the way to go because it reduces the amount of memory that connections take up (new memory is used for each connection). It's also faster because the mechanisms required to establish a new connection slow things down. Finally, dropping connections also causes more work on the server as memory is cleaned up & deallocated.
Thanks Grant, thats really helpfull and what i was suspecting, although when i tested it, it didnt consume as much as i thought.
I opened 1000 connection and memory in SQL Server only increased by 60 MB which is not much.
May 26, 2010 at 7:12 am
nadersam (5/26/2010)
Grant Fritchey (5/26/2010)
I would suggest using connection pooling. As much as possible, reusing connections is the way to go because it reduces the amount of memory that connections take up (new memory is used for each connection). It's also faster because the mechanisms required to establish a new connection slow things down. Finally, dropping connections also causes more work on the server as memory is cleaned up & deallocated.Thanks Grant, thats really helpfull and what i was suspecting, although when i tested it, it didnt consume as much as i thought.
I opened 1000 connection and memory in SQL Server only increased by 60 MB which is not much.
No, it's not huge. But it's cumulative. Since the better practice of connection pooling is cheap and easy, why pay the cost?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply