benefit of open connection with LINQ

Question

Hi All

I have a website uses the LINQ , and I have a large number of SQL statment

is the  open LINQ connection  and close it after complated is usefull ?

how I can get the best performance?

please help me.

 

Answer 1

Can you explain better?

If super optimal performance is a must then perhaps L2S is not for you and you are better off with raw TSQL

With S2L there is a trade off performance for the LINQ goodness, nothing in life is free.

Regards 

Answer 2

Hi Ora,

Close connection has certain benefit. If you want to improve the efficiency of DB, the important point is the use of connection pool.

When the connection end up, you should close the connection, so it will be returned to the pool. Generally, execute the connection.close() in try {}finally.

Make an suppose, I create a database, it contains of table and stored procedure, then add data into database. The data may lost when end of the test. The database is in use, program will pop up error.

You can read the blog "Don't forget to close your Linq to Sql connections". I think that it can help you.

I hope this will help resolve your problem. If anything is unclear, please free feel to let us know.

 

Related Links:

http://www.jdconley.com/blog/archive/2007/08/30/dont-forget-to-close-your-linq-to-sql-connections.aspx

 

Best Regards,

Terry

Answer 3

may be the TSQL is more useful in some time  but I got the website depends on LINQ and it is not perfect ,

I don't have a time to fix it

there is many SQL Statment executed in the page load and in every time the LINQ open the connection and close it again

I try to open the connection before first statment and close it in the end , is it useful?

Answer 4

Most times there is no need to maintain a connection open the whole time, pooling has become very efficient so opening and closing connections is very fast for most cases

In most scenarios, you only open the connection, use it and close it inmediately.

Regards

Answer 5

thank you Serguey

could you gave me the tips for pooling setting

 

Answer 6

This FAQ is helpful http://msdn.microsoft.com/en-us/library/bb386929.aspx

This is about SQL server pooling http://msdn.microsoft.com/en-us/library/8xx3tyca(v=VS.100).aspx

Regards

Answer 7

thank you

 could you gave me more tips for Asp.net

 

Answer 8

Note that the supplied like only applies if you explicitly open the connection on the context. If you allow the context to open and close itself, you don't need to explicitly close the connection. Also in that example, each request used a separate SubmitChanges which could be optimized using the UnitOfWork pattern which would only open the connection once for all updates.

You do want to dispose the data context correctly.

---

http://www.ThinqLinq.com http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie

Answer 9

the