Debate - .Net Dev Vs. DBA Who will win?
-
Wow. This is awesome information! The developers are now telling me that the dataadapter when not used with a data reader is supposed to implicitly close the connection automatically after the FILL statement, and that they only need to use a Close statement IF they are using a Data Reader. Which in the example they provided, they do not use the Data Reader. That's why they don't close the connection. I'm trying to get the code for their Class moDataConn.
-
From on-line help:
"The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open. If Fill opened the connection, it will also close the connection when Fill is finished. This can simplify your code when dealing with a single operation such as a Fill or an Update. However, if you are performing multiple operations that require an open connection, you can improve the performance of your application by explicitly calling the Open method of the Connection, performing the operations against the data source, then calling the Close method of the Connection. You should strive to keep connections to the data source open for a minimal amount of time to free up the resource to be used by other client applications."
So yes, it will implicitly close the connection when the fill is done. But is it a good idea to rely on .NET to do this for you? IMOP, no, it's not. Do you know exactly when the connection will be closed? Will it be immediately, or will it be when .NET (e.g. the garbage collector) gets around to doing it? To me, doesn't seem like a very good practice to not explicitly clean up after yourself.
-
Also, FYI, why don't you run a trace on the server, if you can, to see how all those connections are behaving, while the app is in use? If they are hanging around, open, for a long period of time, then I'd say you still have a problem that the developers need to fix.
-
Thank you DM! Yes, I've determined that the connections are just hanging around. They do eventually drop off, but it takes a couple of hours of non-use.
-
I recently migrated several .Net web applications from a Win2k server to a Win2k3 server and noticed that the garbage collection reacted very differently. When the database connections go out of scope (as in a function call) they would be "garbage collected" automatically in Win2k but that is not the case in Win2k3... I went from never seeing more than 100 connections active to watching the number grow exponentially with each passing hour. Eventually I had to start killing "stale" connections on the server as they would reach a threshold where eventually nobody could connect to the data anymore. The important thing to remember is that you should never rely entirely upon the "automatic garbage collection" features of a language as all of them have the same caveat:
"there is no guarantee for when garbage collection will occur..."
The above problem was compounded when I found that even though I had killed the connections left behind on the database server, the connections were still taking up memory on the web server at a rate of 1 MB each... The web server locked up so bad that a call to the datacenter was in order to have someone manually re-start the box.
-
Here's another example directly from on-line help, example code that populates a dataset from a data adapter:
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")
Dim selectCMD As SqlCommand = New SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
selectCMD.CommandTimeout = 30
Dim custDA As SqlDataAdapter = New SqlDataAdapter
custDA.SelectCommand = selectCMD
nwindConn.Open()
Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
nwindConn.Close()
Even here, they explicitly open and close the connection (although I'd probably use a try/catch/finally to be absolutely sure, or use a using() in c#).
-
As more of a 'Production' DBA purist (I have read all of the posts though) it seems very simply a case of sloppy code (period). I've worked with connerction pooling applications both home grown and vendor supplied (Apache and Vitria come to mind immediately) and have found that properly coded connction pool of 10,2,50 (10 initial connections, growth by 2 connections, to a maximum connection limit of 50) has supported hundreds (300-700) of LAN/WAN users. If you're talking WEB, then you could quadruple that number easily. Also, it should not take hours for the idle connections to drop off (again, sloppy code).
On an aside note, with issues like this today, just imagine when you're developers can use CLR in SQL 2K5 !!!
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
>>On an aside note, with issues like this today, just imagine when you're developers can use CLR in SQL 2K5 !!!
Indeed. Just picture it: .Net dev - "Hey, I don't even need a T-SQL cursor in this stored proc, I can just fill a dataset and use a for vb.net For Each loop".
I write .Net code and T-SQL code and shudder at some of the code I've had to maintain in each - just wait until they're mixed together.
-
"It sounds like the SQL connection mantra of "open as late as possible, close as early as possible" isn't being followed in the client code."
Heck, we did that in dBase III days! It's just common sense, but as smarter people than I have said, common sense isn't all that common.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
To reiterate more bluntly:
simply a case of sloppy code (period).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Couldn't have said it better myself as both a DBA and software developer in 5 languages.
-
Unfortunately you may eventually lose the war. The seductive sloppiness of microsoft development environments will continue to lead developers down the dark side.
Connection issues like this are very common in MS shops.
More common is the certainty of MS (.net, VB, etc.) developers that the compiler will wipe their asses for them and make everything all right. And if it's not, the problem must be in the db server.
Oh well, when the next version of SQL Server finally goes production (shudder), they will start to be half right.
-
I want to thank each of you very much. The response has been tremendous, both in quality and quantity. The technical responses have paved the way to reducing the number of connections on the server, and I won't see this type of thing from that team in the future.
The supportive reponses are sauve to my ego, as I'm sure other DBAs can attest to. I try to stress to my developers that I'm here to assist them, not fight them, but they have to do everything MY way
Sooner or later they figure out they can't beat my technical knowledge on MSSQL and are assimilated, but as I get older, it gets harder to do their research for them. So for all of this I want to thank each of you very much!...and BTW, I'm not a newbie. My computer was rebuilt recently and lost my SSC cookie. Apparently, I logged back in with this really old, Lezza account, from WAY back when!
-
We are borg (DBA's) ...
You will be assimilated (Developers) ...
Resistance is futile ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I am Sartre of Borg: existence is futile!
(sorry, came across this a couple months ago on Slashdot and couldn't resist!)
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply