Don't try to do it in QA or EM if you're having such a tough time there... Do this instead:
<cfloop from="1807713" to="2938241" index="i"> <cfquery name=delrecord" datasource="dsname"> Delete tablename where idfield = #i# </cfquery> </cfloop> And if that times out or whatever, try this so the page won't be running a million queries. (You will be running the page a million-odd times though.) <cfparam name="url.rownum" default="1807713"> <cfquery name=delrecord" datasource="dsname"> Delete tablename where idfield = #url.rownum# </cfquery> <cfset variables.rownum = url.rownum + 1> <cflocation url="thisfile.cfm?rownum=#variables.rownum#"> Surely, since they're both doing only one record at a time, one of these two methods will work for you. You could even slap a try/catch block around them to make sure it's not failing. Let me know if this doesn't work or if you need more help. I'm sure we can figure something out without too much difficulty. --Ferg > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Behalf Of Palyne PJ Gaenir > Sent: Thursday, September 30, 2004 12:11 PM > To: [EMAIL PROTECTED] > Subject: nightmare db issue on hosted server... > > > Gods I hope some dba-type person can help here. > > I have a table that is vastly too big. This is SQL-Server 7 and CF > 5 hosted remotely. > > I have been trying for over a month to drop/delete old records, > which I usually do 3x a year. I let it slide a little longer than > usual to help a researcher. But then when I began to worry about > size and speed and start on doing it while just excluding his > records, guess what -- it won't work. > > The server host has limited the SQL query time to 60 (milliseconds?). > > Every day I go to SQL Enterprise Manager (since scripts won't work > for timeout it's easier to be right there in EM) and delete records. > > Sometimes up to a few hundred a time during good moments, often > down to ONE AT A TIME. > > I need to delete OVER A MILLION RECORDS! They are adding at the > rate of thousands a day so obviously I am not keeping up. > > The ID field is indexed on the table so I have to work by that. I > can't do anything by the date field due to timeout. > > I got the maximum ID number for my cut-off date; I need to delete > everything "less than" that (that brings the table up to only > holding records since May 1). The lowest ID value still in the > table is about 1,807,713 and the number I need to delete up to > right now is 2,938,241. > > The server host won't extend the timeout. But often even deleting > ONE record takes "61" or "72" instead of 60 so it won't let me do > it!! This is manually running the query through SQL-Server EM > locally. > > I wait, hoping for a less busy time on the server so I can delete, > you guessed it, ONE RECORD via EM that won't timeout. Meanwhile > reports on the site that do clearing of empty records are starting > to timeout. > > I'm panicking. The whole site is totally slowed down from this > bogged down table. I've asked my rep for help but he is not > qualified as a CF/DBA the way the previous rep was and every day > for the last month, TONS more records get added which just makes > it worse. > > If I can't delete through script and I can't delete through SQL > EM, what the hell am I supposed to do?? > > I would really appreciate any advice. It is only a matter of time > before the whole damn site crashes because of this table -- it's > the answers table of an edu website -- if I can't clean it out. > > Palyne > > > --- > [This E-mail has been scanned for viruses.] > > ---------------------------------------------------------- > To post, send email to [EMAIL PROTECTED] > To unsubscribe: > http://www.dfwcfug.org/form_MemberUnsubscribe.cfm > To subscribe: > http://www.dfwcfug.org/form_MemberRegistration.cfm > > > ---------------------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm ---------------------------------------------------------- To post, send email to [EMAIL PROTECTED] To unsubscribe: http://www.dfwcfug.org/form_MemberUnsubscribe.cfm To subscribe: http://www.dfwcfug.org/form_MemberRegistration.cfm
