Forums
This topic is locked
Changing code for efficiency like rset connections
02 Nov 2001 21:45:13 B. B. posted:
Hello! Having a database issue and receiving a message indicating too many client tasks. Have done some research and it is caused from using a dsn less connection or the recordsets not being closed completely.
Working on a site that uses a dsn connection so that should not be the cause here. Looks like UD4 does close all recordsets on a page if you use their behaviors but they do not clear the connection object from server memory with the set rsname = Nothing code.
Was wondering what the consensus is in this forum concerning connection code changes.
Have most users added the set rsname = Nothing statement to their code in order to clear up the object for the connection?
Have a related question too. If you have a site with multiple pages with unique recordsets on each page, do most UD4 users add code to check if the recordset is already open on the page, maybe set a session variable to hold a signal, therefore the recordset would not have to be created each time the end user goes to a page. This would save server processing and more. Was wondering what the consensus is on this possible efficiency change and does it save a lot of server processing?
Would appreciate any and all recommendations or comments concerning this. Thanks!
Replies
Replied 05 Nov 2001 15:28:28
05 Nov 2001 15:28:28 Joel Martinez replied:
well, as far as the connection goes, I'm not sure where you heard it, but a dsn-less connection using OleDb is far better than ODBC dsns. the reason being the the dsn is nothing more than a wrapper for the ODBC provider, which in turn is a wrapper for the OleDb provider.
here's a good article on ado connections www.basic-ultradev.com/articles/ADOConnections/
If you are really serious about performance and changing the code to reflect that, I would manually close all recordsets and connections after they are used. even though in theory the garbage collection runtime closes them when the request is done, you cant trust it.
if you are done with the recordset halfway down the page, then why wait until the entire page executes to close the recordset, I would find the repeat region loop, and put somethign like this right after it.<pre id=code><font face=courier size=2 id=code><%
recordset1.close
set recordset1 = nothing
connection1.close
set connection1 = nothing
%></font id=code></pre id=code>This explicitly closes the connection, thus freeing up resources earlier for other processes to use.
The thing that sucks with the connections, is that even if it's left open by a page, you can't take advantage of it on another, so it's a wasted connection.
hopefully that helps...
Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/
Edited by - joelmartinez on 11/05/2001 15:30:07
here's a good article on ado connections www.basic-ultradev.com/articles/ADOConnections/
If you are really serious about performance and changing the code to reflect that, I would manually close all recordsets and connections after they are used. even though in theory the garbage collection runtime closes them when the request is done, you cant trust it.
if you are done with the recordset halfway down the page, then why wait until the entire page executes to close the recordset, I would find the repeat region loop, and put somethign like this right after it.<pre id=code><font face=courier size=2 id=code><%
recordset1.close
set recordset1 = nothing
connection1.close
set connection1 = nothing
%></font id=code></pre id=code>This explicitly closes the connection, thus freeing up resources earlier for other processes to use.
The thing that sucks with the connections, is that even if it's left open by a page, you can't take advantage of it on another, so it's a wasted connection.
hopefully that helps...
Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/
Edited by - joelmartinez on 11/05/2001 15:30:07
Replied 05 Nov 2001 15:59:47
05 Nov 2001 15:59:47 B. B. replied:
Hey Joel! Very much appreciate the tips. Have read the connections link and will add some code to explicitly close all the connections.
The web host is the one that indicated that dsns were better than dsn less connections but they may have meant under certain circumstances.
They tried moving the database from a low volume pool to a medium volume pool on the same server and the error for too many client tasks was still happenning. They are in the process of moving it to another server in hopes of correcting this but will still add the code for closing the connections.
Have one more question if you have a chance. Have read on the google forum that MS Access can only handle 8 simultaneous connections. Was wondering if that was accurate, and if so, what happens when more clients try to access the database and can anything be done to minimize any errors short of moving up to SQL server in order to handle more volume.
This forum is a great help. Keep up the good work. Thanks!
The web host is the one that indicated that dsns were better than dsn less connections but they may have meant under certain circumstances.
They tried moving the database from a low volume pool to a medium volume pool on the same server and the error for too many client tasks was still happenning. They are in the process of moving it to another server in hopes of correcting this but will still add the code for closing the connections.
Have one more question if you have a chance. Have read on the google forum that MS Access can only handle 8 simultaneous connections. Was wondering if that was accurate, and if so, what happens when more clients try to access the database and can anything be done to minimize any errors short of moving up to SQL server in order to handle more volume.
This forum is a great help. Keep up the good work. Thanks!
Replied 06 Nov 2001 16:33:33
06 Nov 2001 16:33:33 Joel Martinez replied:
well, if you're looking at more than 8 <b>concurrent</b> connections, then I think you need to be looking at upgrading to sql server anyways... acces just doesn't do well under stress.
BTW... don't confuse <b>concurrent</b> connections with database size. I've heard of access tables with hundreds of thousands of rows of data that work great, but this was only because it had one user.
Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/
BTW... don't confuse <b>concurrent</b> connections with database size. I've heard of access tables with hundreds of thousands of rows of data that work great, but this was only because it had one user.
Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/