April 28, 2004 at 6:35 am
Hi
WE use ADO and/or XML to select,insert,update records on a SQL 2000 database.
This is achieved in two ways:
1) A connection is kept opening until the client application is close. All transactions are then processed through this conection.
2) Connections are opened when the client application send a transcation to the database. After the transaction has completed the connection is then closed. this process is repeated for each tranasctions. The connections are never reused.
Which of the two methods is the most efficient/quicker etc.?
I personally would say that maintaining the connection is the most efficent method because the server only needs to perserve the connection in memory rather than having to create a new connection each time the client application initiates a transaction. However, in some literature (can't remember the titles) the authors suggest that open and closing connections is the preferred method because open connections require lots or resources.
April 29, 2004 at 4:26 am
of course the second method is more efficent ...
because in first method you may suffer from the LOCK problems .. if more than one connection open the same table
but sometimes you need to use the second method ... (as in Access) because of the weekness of the application (programming language) you use.
I hope this help u.
Alamir Mohamed
Alamir_mohamed@yahoo.com
April 29, 2004 at 4:35 am
I'm still not convinced.
With the second method you may still suffer locks because one or more of the client applications could log on at the same time and request data from the same table.
With the first method the connection only holds locks when the SQL statement is being executed. Once the sql statement is complete there is no longer a lock. This is the same as the second method the only difference being that the connection remains open.
Doesn't continually opening and closing connection create a performance bottleneck?
April 29, 2004 at 7:58 am
I use a combination. As long as the application is active the connection stays open. A timer is used to check: If no ADO activity for 2 minutes, then close the connection. Then re-connect as needed.
Works Great! No concerns about locking.
May 4, 2004 at 10:12 am
My concern isn't with locking its with inefficient use of resources.
Have i misunderstood what is meant by locking. My understanding is that you only get locking during the execution of a transaction or if you specifically request a lock when viewing records. ADO and Access don’t request locks when they view data. When an update is initiated the code attempts to find out whether the data has changed since it was retrieved. If it has changed then the client is informed and asked to take appropriate action.
If a connection isn't doing anything how is it a problem?
It is only using 12 KB + (3 * Network Packet Size) of memory per connection.
Does making a new connection use more resources than approximately 64kb of memory?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply