CF10 Datasource settings

[PhilBro]

I'm looking over the datasource settings for MS SQL Server and I have some questions about.

Limit Connections

I can't think of any reason to use this except to help minimize the damage of a DOS attack.

Max Pooled Statements

I understand that this should be set to equal the amount of unique cfquery tags that use the cfqueryparam tag and cfstoredproc tags. Thats great an all but why?

Timeout

This is how long it keeps each connection to that datasource open. Is there any reason to change this and how would you determine the right setting?

Query Timeout

It defaults to 0 (no timeout) and I don't want a query running forever. I can't think of the last time I had a query run more than 1 second. Any reason why I wouldn't want to set it to 5 seconds or less?

Thanks
Phil

[BKBK · Accepted solution]

Limit Connections: Why?

The main reason is that connections cost a lot of processing overhead (CPU resources). If you have too many of them going, things will eventually slow down to a treacle.

Max Pooled Statements: Why set it equal to the number of unique cfquery tags that use the cfqueryparam tag and cfstoredproc tags?

The ultimate purpose is efficiency. These types of query involve reuse. The underlying Java engine therefore creates and maintains a pool of prepared statements to be reused by each such query. The number of unique queries is a good starting point. It is unnecessary to allocate more memory space (for a larger pool) than is needed. Furthermore, since Java creates pooled statements based on currently active connections, the connection must be maintained for reuse of pooled statements to be possible.

Timeout: Is there any reason to change it and how would you determine the right setting?

This is how long ColdFusion maintains an idle connection before destroying it. I would leave it at the 20-minute default setting. That falls within the generally accepted range for session timeout in ColdFusion applications, namely, 20 to 30 minutes.

Query Timeout: Any reason to set it to 5 seconds or less?

The Relational Database Management System is separate from ColdFusion. A query may, for reasons beyond ColdFusion's control, run beyond the time that the ColdFusion application expects. The Coldfusion threads that wait on the query would continue to do so indefinitely. If it is critical to your application that a query must run under 5 seconds, or else abort, then you should set 'Query Timeout' to 5 seconds. Charlie Arehart wrote an instructive article on this.

[PhilBro]

Thanks for the response. This is exactly the type of response I was looking for. I have a couple followup questions.

Limit Connections:

Any idea what happens to connections that come in after the limit is reached? I'm guessing they are rejected and an error is thrown.

Max Pooled Statements:

This may explain some problems we have had after recent code changes that increased the use of cfqueryparam and a low Max Pool value. Any idea if increasing the use of ColdFusion ORM would have a positive or negative impact on the number of pooled statements?

Query Timeout:

After reading Charlie's post and the submitted bug, I'm not sure if they ever fixed the problem.

Phil

[BKBK]

Of course, connections cannot be created when the limit is reached. It is impossible to second-guess the decisions of the Java engine, but I can imagine that it would attempt to create a new connection to the database, should a request need one. Chances are, it might then time out idle connections to make room. If a new connection cannot be created, you will get an exception similar to: "A problem occurred when attempting to contact the server (Server returned: Connection reset). Please ensure that the server parameters passed to the driver are correct and that the server is running. Also ensure that the maximum number of connections have not been exceeded for this server.".

ORM makes elaborate use of pooled statements. These kinds of processes are optimized and performed under the hood by the ORM machinery.

Coldfusion 11 is now upon us. To my knowledge, Adobe has yet to implement Charlie Arehart's suggestion.