SQL Server Performance Forum – Threads Archive
# of connections problem
What do you do if you have many more front end users than connections to the SQL server available? Do you write the front end to open a connection every time you make a transaction and close it right after? What do you do then about record locking (record won’t be locked if you close the connection)? What about connection pooling? Tom LaurieWell your front-end should take advantage of connection pooling automatically, if you are using the latest version of MDAC which is where ADO comes from. Our solution here is to write our own server that is allowed 25 connections to the database and then our front-end hooks into our server instead of the database directly.
"How do you expect to beat me when I am forever?"
Yours is a "big" question with lots of different answers depending on the circumstances. Below are two references to tips covering your questions for VB and ASP. If you are using VB, check out tips at this URL: http://www.sql-server-performance.com/visual_basic_performance.asp If you are using ASP, check out tips at this URL: http://www.sql-server-performance.com/asp_sql_server.asp ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Brads’ right, it’s really a huge question and it can also depend on the type of application and it’s needs.
Using .NET you can take advantage of the fact that alot of ADO.Net is written to work with data offline so you could make a single connection and retrieve most of the data in one swoop and then work with it in the DataSet ‘offline’ (from the database) which is more scalable as you’re freeing up database connections and don’t have to keep on returning, you could also take advantage of caching data, especially data that’s frequently used and is fairly static. If you’re worried about updating data and need record locks, the ADO.Net DataSet can also help you out here as you can make updates and inserts offline and then make one call to the database to do this, any ‘dirty data’ won’t be updated. You can also cache with the previous version of ADO by persisting the data as xml which is also quite elegant.. all depends on what your application is required to do. Cheers
Shaun
World Domination Through Superior Software
Fair commends by fellow members, For example, if the maximum number of user connections to your SQL Server box is equal to 50, you can set the ‘max worker threads’ options to 50. This frees up resources for SQL Server to use elsewhere. If the maximum number of the user connections to your SQL Server box is equal to 500, you can set the ‘max worker threads’ options to 500, which can improve SQL Server performance because thread pooling will not be used. Satya SKJ
]]>