November 25, 2005 at 6:02 am
Hello all;
Read everywhere that in .net applications, connection to sql server should be kept open for the minimum time i.e. open connection, do the db access & close it immediately.
Ours is a Data Entry application, which does continuous inserts. 1500 records an hour by each operator. Wouldn't it be better to keep the connection open throughout(application's life) instead of opening & closing with each record? I know that will not allow connection pooling but definately reduce db trips? Pl. suggest. thanks,Sonali
November 27, 2005 at 3:00 pm
November 28, 2005 at 2:01 am
Locks would be freed by commiting the transaction (which has this INSERT/UPDATE stmt) in the stored procedure. The application need not close the connection(after every update) for releasing the locks. Moreover, by default SQL server acquires row level locks and not table level.
I think, in this typical scenario, where there are 10 operators using this program to continuously insert(mostly, update sometimes) rows into a table at a rate as high as 1500 per hour, we can keep db connections open for each of these processes & have transactions managing the locks. (instead of 120,000 connection.open & 120000 connection.close(1500 inserts * 8hours * 10 operators))....any suggestions?
November 28, 2005 at 2:11 am
It all depends on how you have implemented your program. Is it supposed to be realtime entry or is it possible that you are allowed a few minutes for updates to take place...
If the latter is correct, I propose writing say 50 rows of information to your client side program at which time this bulk insert can be run after the row limit of 50 is reached per user. So instead of doing 1000's of inserts you are able to say do 20 inserts for similiar amount of data.
November 28, 2005 at 5:10 am
It was discussed & decided that every insert should be a physical one commited to db. The question was whether to follow Microsoft's recommendation to close the connection after every transaction (& open again for the next one) or not in this particular scenario.....wanted to compare it against keeping the connection open thruout on criterions like performance, n/w traffic etc.
November 29, 2005 at 11:19 am
I can tell you this:
Where I work we have tools that will query the database and open lots of new connections very fast under the right conditions. Some of those connection requests fail because there are so many of them.
We are actively looking at how to do some form of connection pooling so that we do fewer connections. In fact our main application runs through Java code running on the server and that code is always connected, and we don't get locking issues.
I would re-read the suggestions from Microsoft to ensure that you understand what they are saying, but yes someone should be able to log in, and do inserts/updates/deletes all day without a problem presuming the transactions are handled properly. In fact Query Analyzer does just this. Definately keep the connection open while the user is in a data maintenance mode. If there is another portion of the app, that does something non-database related like creates e-mail, when you go to that section, closing the connection would be a good idea.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply