Re: cfmx - request timeout setting
Big T, did you get this issue resolved completely? -Adam On Thu, 5 Aug 2004 18:30:09 -0400, Tony Weeg [EMAIL PROTECTED] wrote: :) thanks! for all your help. also, kudos to mike t.who helped as well! later. tw On Thu, 5 Aug 2004 18:18:58 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, The only thing I can say to that is run it one way , then run it the other way and see which is faster. There is an article about Query performance here: http://www.databasejournal.com/features/mssql/article.php/3298411 http://www.databasejournal.com/features/mssql/article.php/3298411 Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 5:38 PM To: CF-Talk Subject: Re: cfmx - request timeout setting so... does that looping effect buy me anything, or should i just run the whole shebang and not worry about rowcount? i guess my question is...does this help in the deletion process?help = increased speed/less downtime? thanks! -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
no. we just got the queries tuned so that it doesnt cause the problem As Much the problem with CFMX spiraling to its death upon a request to a VERY tied up db, still is there. :( any ideas? tw On Mon, 16 Aug 2004 14:51:27 -0400, Adrocknaphobia [EMAIL PROTECTED] wrote: Big T, did you get this issue resolved completely? -Adam On Thu, 5 Aug 2004 18:30:09 -0400, Tony Weeg [EMAIL PROTECTED] wrote: :) thanks! for all your help. also, kudos to mike t.who helped as well! later. tw On Thu, 5 Aug 2004 18:18:58 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, The only thing I can say to that is run it one way , then run it the other way and see which is faster. There is an article about Query performance here: http://www.databasejournal.com/features/mssql/article.php/3298411 http://www.databasejournal.com/features/mssql/article.php/3298411 Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 5:38 PM To: CF-Talk Subject: Re: cfmx - request timeout setting so... does that looping effect buy me anything, or should i just run the whole shebang and not worry about rowcount? i guess my question is...does this help in the deletion process?help = increased speed/less downtime? thanks! -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: cfmx - request timeout setting
What if you wrote some other code (VB or something) to run the stored procedure and then you could call that via CFEXECUTE and tell CF not to wait for it to finish.Then CF will call the script which will call the stored procedure and then it will move on and do its job. John -Original Message- From: DURETTE, STEVEN J (AIT) [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 6:19 PM To: CF-Talk Subject: RE: cfmx - request timeout setting Tony, The only thing I can say to that is run it one way , then run it the other way and see which is faster. There is an article about Query performance here: http://www.databasejournal.com/features/mssql/article.php/3298411 http://www.databasejournal.com/features/mssql/article.php/3298411 Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 5:38 PM To: CF-Talk Subject: Re: cfmx - request timeout setting so... does that looping effect buy me anything, or should i just run the whole shebang and not worry about rowcount? i guess my question is...does this help in the deletion process?help = increased speed/less downtime? thanks! -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
when the requesttimeout wall is hit, the user gets a message.Can't trap this one though, sucks.Note that when this occurs, the thread tied up in this request is not freed neccessarily. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou hi there. if you have a request timeout setting of 30 seconds, and that time threshold is hit, what if any notification does the requesting template get? anything? an error that can be caught?it seems like the page churns and churns, for 30 seconds, and then just nothing.no error, no nothing, just sits there...then eventually my server crashes...WTF? -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
how does one free that thread?its killing my webserver, and its getting to be a problem. tw On Thu, 05 Aug 2004 14:13:23 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: when the requesttimeout wall is hit, the user gets a message.Can't trap this one though, sucks.Note that when this occurs, the thread tied up in this request is not freed neccessarily. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou hi there. if you have a request timeout setting of 30 seconds, and that time threshold is hit, what if any notification does the requesting template get? anything? an error that can be caught?it seems like the page churns and churns, for 30 seconds, and then just nothing.no error, no nothing, just sits there...then eventually my server crashes...WTF? -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
You can't kill it.In CF5 and earlier you could set a threashold for the number of these 'unresponsive' threads.When this was hit, CF restarted itself.In CFMX I do not see this anymore, well, you might see it in the CFMX Standalone version, but not the J2EE version. Better to find out why you have these long requests.Is your app DB intensive?I have apps on intranets that are and have the timeout set to 180secs.Maybe you have one page running a nasty SQL that needs to be tuned up? Anyone know of similar settings for JRun -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou how does one free that thread?its killing my webserver, and its getting to be a problem. tw On Thu, 05 Aug 2004 14:13:23 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: when the requesttimeout wall is hit, the user gets a message.Can't trap this one though, sucks.Note that when this occurs, the thread tied up in this request is not freed neccessarily. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou hi there. if you have a request timeout setting of 30 seconds, and that time threshold is hit, what if any notification does the requesting template get? anything? an error that can be caught?it seems like the page churns and churns, for 30 seconds, and then just nothing.no error, no nothing, just sits there...then eventually my server crashes...WTF? -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
heres the problem: we have 29 million row database, that purges about 38 rows each morning @ 3:00 am. if requests to that database are made during that deletion, in about 10 - 15 minutes, we lose the cfmx server.i can reproduce 100 times over, so i know its the problem.i have the time out set to 30 seconds, in the admin, as well as the jrun.xml. what can i do about this? thanks. tony On Thu, 05 Aug 2004 14:30:05 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You can't kill it.In CF5 and earlier you could set a threashold for the number of these 'unresponsive' threads.When this was hit, CF restarted itself.In CFMX I do not see this anymore, well, you might see it in the CFMX Standalone version, but not the J2EE version. Better to find out why you have these long requests.Is your app DB intensive?I have apps on intranets that are and have the timeout set to 180secs.Maybe you have one page running a nasty SQL that needs to be tuned up? Anyone know of similar settings for JRun -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou how does one free that thread?its killing my webserver, and its getting to be a problem. tw On Thu, 05 Aug 2004 14:13:23 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: when the requesttimeout wall is hit, the user gets a message.Can't trap this one though, sucks.Note that when this occurs, the thread tied up in this request is not freed neccessarily. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou hi there. if you have a request timeout setting of 30 seconds, and that time threshold is hit, what if any notification does the requesting template get? anything? an error that can be caught?it seems like the page churns and churns, for 30 seconds, and then just nothing.no error, no nothing, just sits there...then eventually my server crashes...WTF? -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: cfmx - request timeout setting
How about deleting the rows in blocks of 25,000? Michael T. Tangorre -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 10:38 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres the problem: we have 29 million row database, that purges about 38 rows each morning @ 3:00 am. if requests to that database are made during that deletion, in about 10 - 15 minutes, we lose the cfmx server.i can reproduce 100 times over, so i know its the problem.i have the time out set to 30 seconds, in the admin, as well as the jrun.xml. what can i do about this? thanks. tony On Thu, 05 Aug 2004 14:30:05 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You can't kill it.In CF5 and earlier you could set a threashold for the number of these 'unresponsive' threads. When this was hit, CF restarted itself.In CFMX I do not see this anymore, well, you might see it in the CFMX Standalone version, but not the J2EE version. Better to find out why you have these long requests.Is your app DB intensive?I have apps on intranets that are and have the timeout set to 180secs.Maybe you have one page running a nasty SQL that needs to be tuned up? Anyone know of similar settings for JRun -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou how does one free that thread?its killing my webserver, and its getting to be a problem. tw On Thu, 05 Aug 2004 14:13:23 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: when the requesttimeout wall is hit, the user gets a message.Can't trap this one though, sucks.Note that when this occurs, the thread tied up in this request is not freed neccessarily. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou hi there. if you have a request timeout setting of 30 seconds, and that time threshold is hit, what if any notification does the requesting template get? anything? an error that can be caught?it seems like the page churns and churns, for 30 seconds, and then just nothing. no error, no nothing, just sits there...then eventually my server crashes...WTF? -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: cfmx - request timeout setting
Can you use cflock with a timeout around access to this table?(Have the purge get an exclusive lock and all readers get a read only lock with a 10 or 30 second timeout.)This could be a purely logical lock too... it doesn't need to literaly surounding the database table accesses, so if you wanted the entire web site to be off during the purge then you could put the cflock for read in application.cfm... if you can get the lock, even for a moment, then all is well, otherwise cfabort the page. Mark -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 10:38 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres the problem: we have 29 million row database, that purges about 38 rows each morning @ 3:00 am. if requests to that database are made during that deletion, in about 10 - 15 minutes, we lose the cfmx server.i can reproduce 100 times over, so i know its the problem.i have the time out set to 30 seconds, in the admin, as well as the jrun.xml. what can i do about this? thanks. tony On Thu, 05 Aug 2004 14:30:05 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You can't kill it.In CF5 and earlier you could set a threashold for the number of these 'unresponsive' threads.When this was hit, CF restarted itself.In CFMX I do not see this anymore, well, you might see it in the CFMX Standalone version, but not the J2EE version. Better to find out why you have these long requests.Is your app DB intensive?I have apps on intranets that are and have the timeout set to 180secs.Maybe you have one page running a nasty SQL that needs to be tuned up? Anyone know of similar settings for JRun -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou how does one free that thread?its killing my webserver, and its getting to be a problem. tw On Thu, 05 Aug 2004 14:13:23 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: when the requesttimeout wall is hit, the user gets a message.Can't trap this one though, sucks.Note that when this occurs, the thread tied up in this request is not freed neccessarily. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou hi there. if you have a request timeout setting of 30 seconds, and that time threshold is hit, what if any notification does the requesting template get? anything? an error that can be caught?it seems like the page churns and churns, for 30 seconds, and then just nothing.no error, no nothing, just sits there...then eventually my server crashes...WTF? -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: cfmx - request timeout setting
Tony, What database are you using?Are you doing the purge through CF or the database? If you are using SQL server try making a stored procedure that does the purge and schedule it through the Enterprise manager. That way it is all done on the back end and you don't have to worry about as many problems.Also, stored procedures can run faster (some times orders of magnitude faster) than a cf page hitting the database. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 10:38 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres the problem: we have 29 million row database, that purges about 38 rows each morning @ 3:00 am. if requests to that database are made during that deletion, in about 10 - 15 minutes, we lose the cfmx server.i can reproduce 100 times over, so i know its the problem.i have the time out set to 30 seconds, in the admin, as well as the jrun.xml. what can i do about this? thanks. tony On Thu, 05 Aug 2004 14:30:05 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You can't kill it.In CF5 and earlier you could set a threashold for the number of these 'unresponsive' threads.When this was hit, CF restarted itself.In CFMX I do not see this anymore, well, you might see it in the CFMX Standalone version, but not the J2EE version. Better to find out why you have these long requests.Is your app DB intensive?I have apps on intranets that are and have the timeout set to 180secs.Maybe you have one page running a nasty SQL that needs to be tuned up? Anyone know of similar settings for JRun -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou how does one free that thread?its killing my webserver, and its getting to be a problem. tw On Thu, 05 Aug 2004 14:13:23 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: when the requesttimeout wall is hit, the user gets a message.Can't trap this one though, sucks.Note that when this occurs, the thread tied up in this request is not freed neccessarily. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou hi there. if you have a request timeout setting of 30 seconds, and that time threshold is hit, what if any notification does the requesting template get? anything? an error that can be caught?it seems like the page churns and churns, for 30 seconds, and then just nothing.no error, no nothing, just sits there...then eventually my server crashes...WTF? -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
its adv. sql server 2000 its a sql job. that aside. my bigger problem with this... how does cfmx server/how should cfmx server handle a database that just is too busy to feed back a requested recordset?should it crash the cmfx server? or not? this is the bigger problem, i think. ill fix the db.whats the problem with cfmx? tw On Thu, 5 Aug 2004 11:02:54 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, What database are you using?Are you doing the purge through CF or the database? If you are using SQL server try making a stored procedure that does the purge and schedule it through the Enterprise manager. That way it is all done on the back end and you don't have to worry about as many problems.Also, stored procedures can run faster (some times orders of magnitude faster) than a cf page hitting the database. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 10:38 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres the problem: we have 29 million row database, that purges about 38 rows each morning @ 3:00 am. if requests to that database are made during that deletion, in about 10 - 15 minutes, we lose the cfmx server.i can reproduce 100 times over, so i know its the problem.i have the time out set to 30 seconds, in the admin, as well as the jrun.xml. what can i do about this? thanks. tony On Thu, 05 Aug 2004 14:30:05 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You can't kill it.In CF5 and earlier you could set a threashold for the number of these 'unresponsive' threads.When this was hit, CF restarted itself.In CFMX I do not see this anymore, well, you might see it in the CFMX Standalone version, but not the J2EE version. Better to find out why you have these long requests.Is your app DB intensive?I have apps on intranets that are and have the timeout set to 180secs.Maybe you have one page running a nasty SQL that needs to be tuned up? Anyone know of similar settings for JRun -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou how does one free that thread?its killing my webserver, and its getting to be a problem. tw On Thu, 05 Aug 2004 14:13:23 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: when the requesttimeout wall is hit, the user gets a message.Can't trap this one though, sucks.Note that when this occurs, the thread tied up in this request is not freed neccessarily. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou hi there. if you have a request timeout setting of 30 seconds, and that time threshold is hit, what if any notification does the requesting template get? anything? an error that can be caught?it seems like the page churns and churns, for 30 seconds, and then just nothing.no error, no nothing, just sits there...then eventually my server crashes...WTF? -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
This has been a sucky issue.If there are network issues or anything, CF can't tell when accessing the DB and has no graceful way of handling it.We use Oracle, if the TNS listner goes off line for like 5 minutes, the CF server basically chokes and dies.Maybe there is a Java approach to this by by of JDBC?I dunno. TW, maybe you can have that job chunk up the delete?Have it delete only a few rows at a time to min the impact. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou its adv. sql server 2000 its a sql job. that aside. my bigger problem with this... how does cfmx server/how should cfmx server handle a database that just is too busy to feed back a requested recordset?should it crash the cmfx server? or not? this is the bigger problem, i think. ill fix the db.whats the problem with cfmx? tw [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
heres my planned stored procbut from what i gather, set rowcount, stops processing @ the rowcount limit...right? anyway, here is the stored proc code. CREATE PROCEDURE storedProc_Kill_91stDay AS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ROWCOUNT 3 delete from newBackupReports where timeID = dateDiff(ss,'01/01/1970 00:00:00.000',DATEADD(Day, -91, GetDate())) go anyway i can tune this puppy up? or any ideas? thanks! tony On Thu, 05 Aug 2004 15:35:15 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This has been a sucky issue.If there are network issues or anything, CF can't tell when accessing the DB and has no graceful way of handling it.We use Oracle, if the TNS listner goes off line for like 5 minutes, the CF server basically chokes and dies.Maybe there is a Java approach to this by by of JDBC?I dunno. TW, maybe you can have that job chunk up the delete?Have it delete only a few rows at a time to min the impact. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou its adv. sql server 2000 its a sql job. that aside. my bigger problem with this... how does cfmx server/how should cfmx server handle a database that just is too busy to feed back a requested recordset?should it crash the cmfx server? or not? this is the bigger problem, i think. ill fix the db.whats the problem with cfmx? tw [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: cfmx - request timeout setting
Tony, A few things that might help. 1) Use set nocount on and off. 2) Calculate the datediff once instead of multiple times. (not too sure on this, I have seen it recalc not at all or on every record whatever the sql server decides). Like this: create procedure storedProc_Kill_91stDay AS Begin set nocount on set transaction isolation level serializable set rowcount 3 declare @myDateDiff bigint select @myDateDiff = dateDiff(ss, '01/01/1970', dateAdd(d, -91, getDate())) delete from newBackupReports where timeID = @myDateDiff set rowcount 0 set nocount off end This might speed it up.Note the set rowcount 0 should reset the rowcount back to where it should be. Also, what kind of field is timeID?There might be an even faster way depending on the type.Also, is this table indexed?I have heard that if you drop the index, do your delete and recreate the index that it will work faster. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 11:55 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres my planned stored procbut from what i gather, set rowcount, stops processing @ the rowcount limit...right? anyway, here is the stored proc code. CREATE PROCEDURE storedProc_Kill_91stDay AS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ROWCOUNT 3 delete from newBackupReports where timeID = dateDiff(ss,'01/01/1970 00:00:00.000',DATEADD(Day, -91, GetDate())) go anyway i can tune this puppy up? or any ideas? thanks! tony On Thu, 05 Aug 2004 15:35:15 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This has been a sucky issue.If there are network issues or anything, CF can't tell when accessing the DB and has no graceful way of handling it.We use Oracle, if the TNS listner goes off line for like 5 minutes, the CF server basically chokes and dies.Maybe there is a Java approach to this by by of JDBC?I dunno. TW, maybe you can have that job chunk up the delete?Have it delete only a few rows at a time to min the impact. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou its adv. sql server 2000 its a sql job. that aside. my bigger problem with this... how does cfmx server/how should cfmx server handle a database that just is too busy to feed back a requested recordset?should it crash the cmfx server? or not? this is the bigger problem, i think. ill fix the db.whats the problem with cfmx? tw _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
well... thank you steve. ive just got it down to like 5 min to delete 2million rows. timeID is an int, and yes its indexed. thanks for the ideas... tony On Thu, 5 Aug 2004 14:06:42 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, A few things that might help. 1) Use set nocount on and off. 2) Calculate the datediff once instead of multiple times. (not too sure on this, I have seen it recalc not at all or on every record whatever the sql server decides). Like this: create procedure storedProc_Kill_91stDay AS Begin set nocount on set transaction isolation level serializable set rowcount 3 declare @myDateDiff bigint select @myDateDiff = dateDiff(ss, '01/01/1970', dateAdd(d, -91, getDate())) delete from newBackupReports where timeID = @myDateDiff set rowcount 0 set nocount off end This might speed it up.Note the set rowcount 0 should reset the rowcount back to where it should be. Also, what kind of field is timeID?There might be an even faster way depending on the type.Also, is this table indexed?I have heard that if you drop the index, do your delete and recreate the index that it will work faster. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 11:55 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres my planned stored procbut from what i gather, set rowcount, stops processing @ the rowcount limit...right? anyway, here is the stored proc code. CREATE PROCEDURE storedProc_Kill_91stDay AS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ROWCOUNT 3 delete from newBackupReports where timeID = dateDiff(ss,'01/01/1970 00:00:00.000',DATEADD(Day, -91, GetDate())) go anyway i can tune this puppy up? or any ideas? thanks! tony On Thu, 05 Aug 2004 15:35:15 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This has been a sucky issue.If there are network issues or anything, CF can't tell when accessing the DB and has no graceful way of handling it.We use Oracle, if the TNS listner goes off line for like 5 minutes, the CF server basically chokes and dies.Maybe there is a Java approach to this by by of JDBC?I dunno. TW, maybe you can have that job chunk up the delete?Have it delete only a few rows at a time to min the impact. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou its adv. sql server 2000 its a sql job. that aside. my bigger problem with this... how does cfmx server/how should cfmx server handle a database that just is too busy to feed back a requested recordset?should it crash the cmfx server? or not? this is the bigger problem, i think. ill fix the db.whats the problem with cfmx? tw _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
steve. problem. its only deleteing 3 rows, not all of them. how can this work, deleting all, and not stopping @ 3 rows deleted? thanks. tony On Thu, 5 Aug 2004 14:12:39 -0400, Tony Weeg [EMAIL PROTECTED] wrote: well... thank you steve. ive just got it down to like 5 min to delete 2million rows. timeID is an int, and yes its indexed. thanks for the ideas... tony On Thu, 5 Aug 2004 14:06:42 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, A few things that might help. 1) Use set nocount on and off. 2) Calculate the datediff once instead of multiple times. (not too sure on this, I have seen it recalc not at all or on every record whatever the sql server decides). Like this: create procedure storedProc_Kill_91stDay AS Begin set nocount on set transaction isolation level serializable set rowcount 3 declare @myDateDiff bigint select @myDateDiff = dateDiff(ss, '01/01/1970', dateAdd(d, -91, getDate())) delete from newBackupReports where timeID = @myDateDiff set rowcount 0 set nocount off end This might speed it up.Note the set rowcount 0 should reset the rowcount back to where it should be. Also, what kind of field is timeID?There might be an even faster way depending on the type.Also, is this table indexed?I have heard that if you drop the index, do your delete and recreate the index that it will work faster. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 11:55 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres my planned stored procbut from what i gather, set rowcount, stops processing @ the rowcount limit...right? anyway, here is the stored proc code. CREATE PROCEDURE storedProc_Kill_91stDay AS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ROWCOUNT 3 delete from newBackupReports where timeID = dateDiff(ss,'01/01/1970 00:00:00.000',DATEADD(Day, -91, GetDate())) go anyway i can tune this puppy up? or any ideas? thanks! tony On Thu, 05 Aug 2004 15:35:15 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This has been a sucky issue.If there are network issues or anything, CF can't tell when accessing the DB and has no graceful way of handling it.We use Oracle, if the TNS listner goes off line for like 5 minutes, the CF server basically chokes and dies.Maybe there is a Java approach to this by by of JDBC?I dunno. TW, maybe you can have that job chunk up the delete?Have it delete only a few rows at a time to min the impact. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou its adv. sql server 2000 its a sql job. that aside. my bigger problem with this... how does cfmx server/how should cfmx server handle a database that just is too busy to feed back a requested recordset?should it crash the cmfx server? or not? this is the bigger problem, i think. ill fix the db.whats the problem with cfmx? tw _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: cfmx - request timeout setting
Tony, Remove the set rowcount 3It is limiting the delete to only the first 3 rows it encounters. Also, if you delete that don't forget to delete the set rowcount 0 at the end. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 4:01 PM To: CF-Talk Subject: Re: cfmx - request timeout setting steve. problem. its only deleteing 3 rows, not all of them. how can this work, deleting all, and not stopping @ 3 rows deleted? thanks. tony On Thu, 5 Aug 2004 14:12:39 -0400, Tony Weeg [EMAIL PROTECTED] wrote: well... thank you steve. ive just got it down to like 5 min to delete 2million rows. timeID is an int, and yes its indexed. thanks for the ideas... tony On Thu, 5 Aug 2004 14:06:42 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, A few things that might help. 1) Use set nocount on and off. 2) Calculate the datediff once instead of multiple times. (not too sure on this, I have seen it recalc not at all or on every record whatever the sql server decides). Like this: create procedure storedProc_Kill_91stDay AS Begin set nocount on set transaction isolation level serializable set rowcount 3 declare @myDateDiff bigint select @myDateDiff = dateDiff(ss, '01/01/1970', dateAdd(d, -91, getDate())) delete from newBackupReports where timeID = @myDateDiff set rowcount 0 set nocount off end This might speed it up.Note the set rowcount 0 should reset the rowcount back to where it should be. Also, what kind of field is timeID?There might be an even faster way depending on the type.Also, is this table indexed?I have heard that if you drop the index, do your delete and recreate the index that it will work faster. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 11:55 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres my planned stored procbut from what i gather, set rowcount, stops processing @ the rowcount limit...right? anyway, here is the stored proc code. CREATE PROCEDURE storedProc_Kill_91stDay AS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ROWCOUNT 3 delete from newBackupReports where timeID = dateDiff(ss,'01/01/1970 00:00:00.000',DATEADD(Day, -91, GetDate())) go anyway i can tune this puppy up? or any ideas? thanks! tony On Thu, 05 Aug 2004 15:35:15 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This has been a sucky issue.If there are network issues or anything, CF can't tell when accessing the DB and has no graceful way of handling it. We use Oracle, if the TNS listner goes off line for like 5 minutes, the CF server basically chokes and dies.Maybe there is a Java approach to this by by of JDBC?I dunno. TW, maybe you can have that job chunk up the delete?Have it delete only a few rows at a time to min the impact. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou its adv. sql server 2000 its a sql job. that aside. my bigger problem with this... how does cfmx server/how should cfmx server handle a database that just is too busy to feed back a requested recordset?should it crash the cmfx server? or not? this is the bigger problem, i think. ill fix the db.whats the problem with cfmx? tw _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
got that...and did that in the mean time, but is there a way to chunk the data, like delete in 3 row blocks, and start again, etc.? someone had suggested that as the way to do that? tw On Thu, 5 Aug 2004 16:26:34 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, Remove the set rowcount 3It is limiting the delete to only the first 3 rows it encounters. Also, if you delete that don't forget to delete the set rowcount 0 at the end. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 4:01 PM To: CF-Talk Subject: Re: cfmx - request timeout setting steve. problem. its only deleteing 3 rows, not all of them. how can this work, deleting all, and not stopping @ 3 rows deleted? thanks. tony On Thu, 5 Aug 2004 14:12:39 -0400, Tony Weeg [EMAIL PROTECTED] wrote: well... thank you steve. ive just got it down to like 5 min to delete 2million rows. timeID is an int, and yes its indexed. thanks for the ideas... tony On Thu, 5 Aug 2004 14:06:42 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, A few things that might help. 1) Use set nocount on and off. 2) Calculate the datediff once instead of multiple times. (not too sure on this, I have seen it recalc not at all or on every record whatever the sql server decides). Like this: create procedure storedProc_Kill_91stDay AS Begin set nocount on set transaction isolation level serializable set rowcount 3 declare @myDateDiff bigint select @myDateDiff = dateDiff(ss, '01/01/1970', dateAdd(d, -91, getDate())) delete from newBackupReports where timeID = @myDateDiff set rowcount 0 set nocount off end This might speed it up.Note the set rowcount 0 should reset the rowcount back to where it should be. Also, what kind of field is timeID?There might be an even faster way depending on the type.Also, is this table indexed?I have heard that if you drop the index, do your delete and recreate the index that it will work faster. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 11:55 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres my planned stored procbut from what i gather, set rowcount, stops processing @ the rowcount limit...right? anyway, here is the stored proc code. CREATE PROCEDURE storedProc_Kill_91stDay AS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ROWCOUNT 3 delete from newBackupReports where timeID = dateDiff(ss,'01/01/1970 00:00:00.000',DATEADD(Day, -91, GetDate())) go anyway i can tune this puppy up? or any ideas? thanks! tony On Thu, 05 Aug 2004 15:35:15 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This has been a sucky issue.If there are network issues or anything, CF can't tell when accessing the DB and has no graceful way of handling it. We use Oracle, if the TNS listner goes off line for like 5 minutes, the CF server basically chokes and dies.Maybe there is a Java approach to this by by of JDBC?I dunno. TW, maybe you can have that job chunk up the delete?Have it delete only a few rows at a time to min the impact. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou its adv. sql server 2000 its a sql job. that aside. my bigger problem with this... how does cfmx server/how should cfmx server handle a database that just is too busy to feed back a requested recordset?should it crash the cmfx server? or not? this is the bigger problem, i think. ill fix the db.whats the problem with cfmx? tw _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: cfmx - request timeout setting
Tony, You can leave the rowcount in.Do this: create procedure storedProc_Kill_91stDay AS Begin set nocount on set transaction isolation level serializable set rowcount 3 declare @myDateDiff bigint declare @myNumDeletes bitint select @myDateDiff = dateDiff(ss, '01/01/1970', dateAdd(d, -91, getDate())) select @myNumDeletes = count(timeID) from newBackupReports where timeID = @myDateDiff while (@myNumDeletes 0) begin delete from newBackupReports where timeID = @myDateDiff select @myNumDeletes = @myNumDeletes - 3 end set rowcount 0 set nocount off end Of course keep in any other changes that you have made (removing indexes, etc.) Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 4:34 PM To: CF-Talk Subject: Re: cfmx - request timeout setting got that...and did that in the mean time, but is there a way to chunk the data, like delete in 3 row blocks, and start again, etc.? someone had suggested that as the way to do that? tw On Thu, 5 Aug 2004 16:26:34 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, Remove the set rowcount 3It is limiting the delete to only the first 3 rows it encounters. Also, if you delete that don't forget to delete the set rowcount 0 at the end. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 4:01 PM To: CF-Talk Subject: Re: cfmx - request timeout setting steve. problem. its only deleteing 3 rows, not all of them. how can this work, deleting all, and not stopping @ 3 rows deleted? thanks. tony On Thu, 5 Aug 2004 14:12:39 -0400, Tony Weeg [EMAIL PROTECTED] wrote: well... thank you steve. ive just got it down to like 5 min to delete 2million rows. timeID is an int, and yes its indexed. thanks for the ideas... tony On Thu, 5 Aug 2004 14:06:42 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, A few things that might help. 1) Use set nocount on and off. 2) Calculate the datediff once instead of multiple times. (not too sure on this, I have seen it recalc not at all or on every record whatever the sql server decides). Like this: create procedure storedProc_Kill_91stDay AS Begin set nocount on set transaction isolation level serializable set rowcount 3 declare @myDateDiff bigint select @myDateDiff = dateDiff(ss, '01/01/1970', dateAdd(d, -91, getDate())) delete from newBackupReports where timeID = @myDateDiff set rowcount 0 set nocount off end This might speed it up.Note the set rowcount 0 should reset the rowcount back to where it should be. Also, what kind of field is timeID?There might be an even faster way depending on the type.Also, is this table indexed?I have heard that if you drop the index, do your delete and recreate the index that it will work faster. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 11:55 AM To: CF-Talk Subject: Re: cfmx - request timeout setting heres my planned stored procbut from what i gather, set rowcount, stops processing @ the rowcount limit...right? anyway, here is the stored proc code. CREATE PROCEDURE storedProc_Kill_91stDay AS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ROWCOUNT 3 delete from newBackupReports where timeID = dateDiff(ss,'01/01/1970 00:00:00.000',DATEADD(Day, -91, GetDate())) go anyway i can tune this puppy up? or any ideas? thanks! tony On Thu, 05 Aug 2004 15:35:15 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This has been a sucky issue.If there are network issues or anything, CF can't tell when accessing the DB and has no graceful way of handling it. We use Oracle, if the TNS listner goes off line for like 5 minutes, the CF server basically chokes and dies.Maybe there is a Java approach to this by by of JDBC?I dunno. TW, maybe you can have that job chunk up the delete?Have it delete only a few rows at a time to min the impact. -- --- Douglas Knudsen http://www.cubicleman.com If you don't like something, change it. If you can't change it, change your attitude. Don't complain. - Maya Angelou its adv. sql server 2000 its a sql job. that aside. my bigger problem with this... how does cfmx server/how should cfmx server handle a database that just is too busy to feed back a requested recordset?should it crash the cmfx server? or not? this is the bigger problem, i think. ill fix the db.whats the problem with cfmx? tw _ _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User
Re: cfmx - request timeout setting
so... does that looping effect buy me anything, or should i just run the whole shebang and not worry about rowcount? i guess my question is...does this help in the deletion process?help = increased speed/less downtime? thanks! -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: cfmx - request timeout setting
Tony, The only thing I can say to that is run it one way , then run it the other way and see which is faster. There is an article about Query performance here: http://www.databasejournal.com/features/mssql/article.php/3298411 http://www.databasejournal.com/features/mssql/article.php/3298411 Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 5:38 PM To: CF-Talk Subject: Re: cfmx - request timeout setting so... does that looping effect buy me anything, or should i just run the whole shebang and not worry about rowcount? i guess my question is...does this help in the deletion process?help = increased speed/less downtime? thanks! -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: cfmx - request timeout setting
:) thanks! for all your help. also, kudos to mike t.who helped as well! later. tw On Thu, 5 Aug 2004 18:18:58 -0400, DURETTE, STEVEN J (AIT) [EMAIL PROTECTED] wrote: Tony, The only thing I can say to that is run it one way , then run it the other way and see which is faster. There is an article about Query performance here: http://www.databasejournal.com/features/mssql/article.php/3298411 http://www.databasejournal.com/features/mssql/article.php/3298411 Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Thursday, August 05, 2004 5:38 PM To: CF-Talk Subject: Re: cfmx - request timeout setting so... does that looping effect buy me anything, or should i just run the whole shebang and not worry about rowcount? i guess my question is...does this help in the deletion process?help = increased speed/less downtime? thanks! -- tony Tony Weeg human. email: tonyweeg [at] gmail [dot] com blog: http://www.revolutionwebdesign.com/blog/ Check out http://www.antiwrap.com to send websites to your friends. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
