Can I optimize my database connections via Web.Config connection strings?

Question

I just want to know is there attribute or property in our connectionstring field so that we can maintain an optimized db connections with our clients. Like in general I use.That contains just required info.

<add name ="ConnectionString" connectionString="Data Source=118.225.221.7;Initial Catalog=mycatalogue;
User Id=dba; password=some_password"/>

Another Scenario, we can enhance our connection string for MARS and Asynchronous operations too:

MARS
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
MultipleActiveResultSets=true;

Asynchronous Processing
Server=myServerAddress;Database=myDataBase;Integrated Security=True;
Asynchronous Processing=True;

From our pages i have 4-5 or sometimes ~10 connections to access data. Take a case when I have 100 users accessing the same page simultaneously assuming the code is executing "SELECT" statement and fetching some random rows for each client. Then 400-500 connections will hit the db at a time, which I think, will definitely degrade the server/application performance.

So I want to know, should I minimize the number of queries, or leave it to the .Net framework intelligence.

your bottle neck will not be the connection to the database. Of course can make it faster even for 10 queries/connections, but 100 users at the page at the same time - are you sure ? :) sounds too many, if you do have 100 at the same time, then you need more optimizations than the connection - you need a lot of cache, web garden, or even web farm -cloud. Do not mix the users that are connected with the request to your server. Also take a look to change the session handler if you won more asynchronous operations. — Aristos, Dec 30 '12 at 18:51
if your looking to stop hits on the database you need to think about caching across layers (UI, business, data) also look at taking the data differently and limiting the number of calls you have to make. — Modika, Dec 30 '12 at 18:53
@Aristos : Thanks. 100 users means, that much user using the same page for different purpose. As this one is going to be a regional directory search engine web. So I said so. — , Dec 30 '12 at 18:56
@Modika I am using Linq2Sql and I dont know the caching mechanism in this framework,Can you suggest me some architecture or framework on the same. That will be a great help. Instead I am using Compiled queries in my current application. Is that ok? — , Dec 30 '12 at 18:57
@AmitRanjan Yes ok, you may have many connection at the same time - to make it more asynchronous first change the session handler and also use web garden. To have one open connection on DB and make more than one call there and close it soon can improve some milliseconds the speed - but only few. — Aristos, Dec 30 '12 at 18:58
Take a look at: http://stackoverflow.com/questions/11325150/trying-to-make-web-method-asynchronous — Aristos, Dec 30 '12 at 19:00

score 0 · Accepted Answer · edited Oct 31 '15 at 08:37

Firstly look at optimising your linq, make sure you are not making two queries when all the info can be pulled with one call for instance.

Also look at caching. Here you will have to make the decision on how long the data should be cached for and when it should be invalidated (by someone inserting a record for example) and at what levels you do it. If your page rarely changes and it is not so heavy on insertions/updates you could possible cache at the page level (page level caching).

If that is not an option you can look at caching your data returned from linq using something like httpcontext.current.cache, a quick google search will show you the details, but in essence you cache the data returned from your linq queries, and on subsequent requests you check the cache first to see if the data is there, if it is, use that, if not go to the database and request it again. This limits the hits to your database.

Can I optimize my database connections via Web.Config connection strings?

1 Answers1