CODEWITHSUNDEEP
c#asp.netsql-serverdatabase
Kvadiyatar
Kvadiyatar

Reputation: 964

to open sql connection from c# code taken time?

Actually I am confuse about one thing.

I have .net application and using sql server 2008 for database.

Now, on my Method A i am filling datareader.

now, during while loop i am calling another method B by pass one property of result. at that time i also open DB connection & close it.

same thing doing Calling another method C from method B. at that time also open DB connection & close.

To fill final list of Method A. it is taking time.

so, my point is.To open DB connection & closing it. Is it time consuming process?

Upvotes: 0

Views: 659

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294297

To open DB connection & closing it. it is time consuming process

Yes. If pooling is enabled and the connection is returned from the pool then opening and closing costs at least one round-trip, to reset the connection. If the connection is not pooled opening and closing is a full SSPI complete handshake. If SQL authentication is used and encryption is enabled, there is another complete SSL handshake to establish a secure channel before the SQL handshake. Even under ideal conditions, it takes 10s of ms, it can go up to whole seconds with some minimal network latency added.

A well written ASP.Net application needs one (pooled) connection per request, never more.

Upvotes: 4

Liath
Liath

Reputation: 10191

It is generally very bad practice to connect to a third part application (database, WebService or similar) in any kind of loop. Communication like this is always takes a relatively long time.

As the number of elements in the loop increases the application will become exponentially slower and slower.

A much better approach is to perform an operation for all the elements then pass the required data into your loop logic.

As with all things there are exceptions, loops where you have millions of entities to process and the connection is a small overhead may create circumstances where it's more efficient to process each entity atomically.

Upvotes: 2

Related Questions