Re: CFQUERY Timeout for SQL2005 Fails
Anyone? Bueller? I really need an answer to this -- even though I'm running Fusion Reactor and can keep a survival strategy, this is no way to keep a server running. Is there anyone out there who's successfully used the TIMEOUT parameter in CFQUERY with CFMX 7.0.2 and SQL 2005 Standard? Hi Casey, What happened today is that the SQL server basically locked up. CF kept waiting for the request to come back, but it never did because it just hung infinititely. It seems as if CF waits forever in a case like this, so it's vital that I can kill the connection from the CFQUERY. The real query I run returns usually within milliseconds, about 75ms at most. So this is something that is out of the ordinary, but I want to prevent it from happening in the future. Is there a way? Thanks. how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321605 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
On Tue, Apr 14, 2009 at 5:25 PM, Sung Woo s...@cornell.edu wrote: Hi Casey, What happened today is that the SQL server basically locked up. CF kept waiting for the request to come back, but it never did because it just hung infinitely. It seems as if CF waits forever in a case like this, so it's vital that I can kill the connection from the CFQUERY. That's totally separate issue. if sqlserver is locking up, coldfusion is waiting to make a connection, not getting the result back. the timeout on cfquery only works on the actually sql statement itself. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321606 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CFQUERY Timeout for SQL2005 Fails
The query timeout is not designed to work when there is no connection at all to the DB server. It is designed to time the statement - so if the connection itself is lost the setting will not have any affect. In affect you have an orphaned thread out there that is taking up space in the running request queue. The big question is why do you lose connection? Networking? Overloaded SQL server? That's where you need to focus I think. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Sung Woo [mailto:s...@cornell.edu] Sent: Wednesday, April 15, 2009 7:58 AM To: cf-talk Subject: Re: CFQUERY Timeout for SQL2005 Fails Anyone? Bueller? I really need an answer to this -- even though I'm running Fusion Reactor and can keep a survival strategy, this is no way to keep a server running. Is there anyone out there who's successfully used the TIMEOUT parameter in CFQUERY with CFMX 7.0.2 and SQL 2005 Standard? Hi Casey, What happened today is that the SQL server basically locked up. CF kept waiting for the request to come back, but it never did because it just hung infinititely. It seems as if CF waits forever in a case like this, so it's vital that I can kill the connection from the CFQUERY. The real query I run returns usually within milliseconds, about 75ms at most. So this is something that is out of the ordinary, but I want to prevent it from happening in the future. Is there a way? Thanks. how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321608 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
This is an interesting thread, I've seen what I believe to be very similar behaviour here on one of my webservices, seemed that one particular query sometimes just hangs indefinitly, however other threads and requests are all just fine. I tried playing around with application request timeouts, concurrent threads and cfquery timeout settings but seem to still have the same problem on occasion, I'm wondering if they're caused by the same issue, I too running SQL2k5 however I'm on CF8. Mark, I'd concur that its waiting to establish a connection, is there anything which can be done to cure that problem with a timeout on the connection? I'd imagine mine is down to load as its a busy statistical database which we're having problems with which really needs moving to a beefier box but in the mean time it'd be nice to try and find a quick-fix to tide me over. Rob That's totally separate issue. if sqlserver is locking up, coldfusion is waiting to make a connection, not getting the result back. the timeout on cfquery only works on the actually sql statement itself. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321611 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQUERY Timeout for SQL2005 Fails
Mark and Casey, Thanks for clarifying this for me. I don't own the SQL Server that I need to hit, so it looks like I'm between a rock and a hard place. I still don't think the TIMEOUT parameter is working, though. In my example, I'm hitting a database that is fully operational, and I have a query that runs for about 3.5 seconds. However, I have the TIMEOUT param set to 1, so shouldn't it time out in exactly 1 second and return an error? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321612 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
Also -- this may just be me being dense, but I don't get why the TIMEOUT feature in CFQUERY has to work this way. It's CF that's making the initial request to the datasource, so it should start counting down from the moment it creates that request. And then after waiting for a predetermined point of time (in my case, 1 second), it should sever the connection if the connection still exists and return an error. Is there another way this can be handled? Like I said, CFSETTING is also useless in this example, since it, too, has to wait for the CFQUERY to end. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321613 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
Hi Mark, To answer your question -- there's a DDL transaction that's running on the server, and from what the folks have told me, an ALTER TABLE command was causing the lock to occur. And the downside is that giving hints (i.e., WITH (NOLOCK)) is not going to have an effect in this situation. Working for a large corporation, I often do not have control over the databases that are in the company. So as much as I'd love to fix the issue, I can't. I'm sure we've all been there. This is why it's imperative that CF has the ability to protect itself against underperforming datasources. - Sung The query timeout is not designed to work when there is no connection at all to the DB server. It is designed to time the statement - so if the connection itself is lost the setting will not have any affect. In affect you have an orphaned thread out there that is taking up space in the running request queue. The big question is why do you lose connection? Networking? Overloaded SQL server? That's where you need to focus I think. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com Anyone? Bueller? I really need an answer to this -- even though I'm running Fusion Reactor and can keep a survival strategy, this is no way to keep a server running. Is there anyone out there who's successfully used the TIMEOUT parameter in CFQUERY with CFMX 7.0.2 and SQL 2005 Standard? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321617 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: CFQUERY Timeout for SQL2005 Fails
For an ALTER TABLE to have this affect it would need to be running on a table that you were trying to access. Dropping and adding columns (if that is what was happening) on production tables will cause lots of problems - and not just with CF. It should be done infrequently and very carefully. I would be at a loss to really help if there is no cooperation at the other end. The DB folks have to be involved here. You can't work around poor practice or bad communication. -Mk -Original Message- From: Sung Woo [mailto:s...@cornell.edu] Sent: Wednesday, April 15, 2009 10:38 AM To: cf-talk Subject: Re: CFQUERY Timeout for SQL2005 Fails Hi Mark, To answer your question -- there's a DDL transaction that's running on the server, and from what the folks have told me, an ALTER TABLE command was causing the lock to occur. And the downside is that giving hints (i.e., WITH (NOLOCK)) is not going to have an effect in this situation. Working for a large corporation, I often do not have control over the databases that are in the company. So as much as I'd love to fix the issue, I can't. I'm sure we've all been there. This is why it's imperative that CF has the ability to protect itself against underperforming datasources. - Sung The query timeout is not designed to work when there is no connection at all to the DB server. It is designed to time the statement - so if the connection itself is lost the setting will not have any affect. In affect you have an orphaned thread out there that is taking up space in the running request queue. The big question is why do you lose connection? Networking? Overloaded SQL server? That's where you need to focus I think. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com Anyone? Bueller? I really need an answer to this -- even though I'm running Fusion Reactor and can keep a survival strategy, this is no way to keep a server running. Is there anyone out there who's successfully used the TIMEOUT parameter in CFQUERY with CFMX 7.0.2 and SQL 2005 Standard? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321618 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
The docs say, no, it may not timeout at 1 second. Check out the livedocs... Because the timeout attribute only affects the maximum time for each suboperation of a query, the cumulative time may exceed its value. And, even then, the query stuff is considered to be 3rd party (or at least, so I remember reading a ways back) and does not fall under the same rules as CF templates when it comes to setting a timeout using CFsetting or by way of the administrator. The best way to kill those hung threads is going to be something like FusionReactor or SeeFusion. Matthew Williams Geodesic GraFX ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321619 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
Check this out. This one, as I've said before, outputs a number around 3000. cfquery name=test datasource=dsn timeout=1 select top 2 * from table/cfquery cfoutput#cfquery.ExecutionTime#/cfoutput However, this one results in an error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver]Execution timeout expired. cfquery name=test datasource=dsn timeout=1 select top 1 * from table waitfor delay '00:00:05' /cfquery cfoutput#cfquery.ExecutionTime#/cfoutput Porbably because the query in the second test ran under a second (completed successfully), then it waited five seconds. I read that portion of the docs, too, but I thought it meant if you'd strung more than a single SQL statement -- i.e., SELECT/INSERT/SELECT. Regardless, I think it's terrible that there isn't more substantial control over this. Shouldn't CF know how long a 3rd-party request is taking? There's a timeout parameter in CFHTTP, and it works just fine: wait.cfm cfset thread = CreateObject(java, java.lang.Thread) cfset thread.sleep(1) test.cfm cfhttp url=wait.cfm method=GET timeout=1 /cfhttp cfdump var=#CFHTTP# Result, in about two seconds: Statuscode 408 Request Time-out Which leads me to believe that the connection was indeed broken and CF completed the thread. And that's a 3rd-party call, too, isn't it? The docs say, no, it may not timeout at 1 second. Check out the livedocs... Because the timeout attribute only affects the maximum time for each suboperation of a query, the cumulative time may exceed its value. And, even then, the query stuff is considered to be 3rd party (or at least, so I remember reading a ways back) and does not fall under the same rules as CF templates when it comes to setting a timeout using CFsetting or by way of the administrator. The best way to kill those hung threads is going to be something like FusionReactor or SeeFusion. Matthew Williams Geodesic GraFX ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321627 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
CFQUERY Timeout for SQL2005 Fails
Here's my test: cfquery name=test datasource=dsn timeout=1 select top 2 * from table /cfquery cfoutput#cfquery.ExecutionTime#/cfoutput I'm getting 3000+ for this value. This shouldn't be, right? Because I have the timeout parameter set for a second? The driver I'm using is the one that's supplied with CFMX7.02, Microsoft SQL Server. The only way I can make this work is if I use cfsetting requesttimeout=1 enablecfoutputonly=No, but I don't want to do that. What is going on? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321579 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
Actually, I was wrong -- even cfsetting requesttimeout=1 enablecfoutputonly=No doesn't work, because it still waits for the cfquery to finish before it outputs the timeout error. Is there any solution for this? The database we're relying on is unstable and we need to be able to kill the query sooner. Thanks, - Sung Here's my test: cfquery name=test datasource=dsn timeout=1 select top 2 * from table /cfquery cfoutput#cfquery.ExecutionTime#/cfoutput I'm getting 3000+ for this value. This shouldn't be, right? Because I have the timeout parameter set for a second? The driver I'm using is the one that's supplied with CFMX7.02, Microsoft SQL Server. The only way I can make this work is if I use cfsetting requesttimeout=1 enablecfoutputonly=No, but I don't want to do that. What is going on? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321590 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFQUERY Timeout for SQL2005 Fails
how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery On Tue, Apr 14, 2009 at 4:58 PM, Sung Woo s...@cornell.edu wrote: Actually, I was wrong -- even cfsetting requesttimeout=1 enablecfoutputonly=No doesn't work, because it still waits for the cfquery to finish before it outputs the timeout error. Is there any solution for this? The database we're relying on is unstable and we need to be able to kill the query sooner. Thanks, - Sung ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321591 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CFQUERY Timeout for SQL2005 Fails
Hi Casey, What happened today is that the SQL server basically locked up. CF kept waiting for the request to come back, but it never did because it just hung infinititely. It seems as if CF waits forever in a case like this, so it's vital that I can kill the connection from the CFQUERY. The real query I run returns usually within milliseconds, about 75ms at most. So this is something that is out of the ordinary, but I want to prevent it from happening in the future. Is there a way? Thanks. how about adding WITH (NOLOCK) If your queries are just selecting data, i'd use WITH (NOLOCK) that way you don't need to wait for transactions to finish if that table gets heavy inserts cfquery name=test datasource=dsn timeout=1 select top 2 * WITH (NOLOCK) from table /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321594 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
