> DELETE FROM TABLENAME > WHERE ID < 2938241 > and COURSEID NOT IN (865,794,767,824)
That IN statement is going to be very expensive if CourseID is not indexed. Index it, then try again. -Cameron ----------------- Cameron Childress Sumo Consulting Inc. http://www.sumoc.com --- land: 858.509.3098 cell: 678.637.5072 aim: cameroncf email: [EMAIL PROTECTED] > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Behalf Of Palyne PJ Gaenir > Sent: Thursday, September 30, 2004 2:58 PM > To: [EMAIL PROTECTED] > Subject: Re: nightmare db issue on hosted server... > > > Thank you SO much. Please advise, responses: > > 1. Query analyzer had changed it only slightly, I'd written: > > DELETE FROM TABLENAME > WHERE ID < 2938241 > and COURSEID NOT IN (865,794,767,824) > > (the ID should be the highest number I can get up > to the one shown without timeout. I'm up to 1807800.) > > SQL-EM revised the query to read: > > DELETE FROM [databasename].[TABLENAME] > WHERE (COURSEID NOT IN (865, 794, 767, 824)) AND (ID < 2938241) > > So that is the query I've been using. > > --------------- > > 2. Indexing: the ID field is an identity field, so it is the > primary table index. > > The normal 'drop records' script uses the entry date of the > records to drop, but it times out, nothing using the date in WHERE > clause works anymore (it's not indexed is likely why, as it's the > value least used on the actual website) so I've been doing all the > SQL for this by the ID field, as I assumed that was much faster > for the database. > > When I complained about these timeouts to my server host I was > told to use SQL EM instead of cfml to run the query. The fellow > didn't know that the SQL timeout is no different there I guess. > > --------------- > > 3. Quit digging: But I cannot cease updating the answers table of > an edu website; I can't shut the site down, and making a diff > table would require revision of every single report and more > site-wide (I am the sole coder at the moment). Dropping the extra > records from the answers table would solve everything and must > happen, but the hosted limits don't seem to let me, so I'm trying > to solve that. If you have suggestions, please, I am at the point > of begging for ideas. > > --------------- > > 4. Maxrows doesn't seem too relevent as sometimes even ONE row > affected is timing it out. I am certainly not having a problem > with too many rows; if it gives me a timeout notice I drop the > number I'm deleting until it works. If I could even delete 50 > records at a time I'd skip sleep for 3 days to get this whittled > down to where I could run a script on it, but it won't work. > Sometimes it will and I can delete up to maybe 3000 at a time, > just a couple times in a row at the most; sometimes it won't do > more than 10 or even 1; I can't get ahead of the daily incoming so > I gotta find another solution fast. > > --------------- > > 5. Even the queries which DO run on EM don't take even 15 seconds > including the display load, so there is no way the SQL limit the > server host has set could be 60 seconds that I can fathom anyway, > especially when a query can time out, but revising it to delete > merely one record less may execute to result in 5 seconds! Could > deleting one record more really take 55+ more seconds?? > > Here is the error that I get (image file): > http://www.sciencehorizon.com/0timeout.jpg > > The queries prior to what you see there, deleted 100 records > without complaint, then 50, then timed out on another 50, so I > dropped the query to 20 records more & it timed out as shown. > > When it gets to the 61 or 63 amount timeouts it usually indicates > that even running it on one record won't work, everything is > minimum 61 sometimes--even a query I already just ran that already > removed all relevent records! Does that mean the server is busy? > I'm hosted with 45 other domains on the server I think, so I can't > control that... > > > is if you wanted to run a more intensive query such as delete * > > where ID NOT IN () it should work without even a hiccup. > > I know; why is this so hard. It is affecting the website and any > second now my management is going to start ripping limbs off me > and send me to code unemployment forms. I admit I'm no DBA, and I > understand the table is huge, but I've made the situation clear to > the host's techs and I don't see why it is impossible for me to > solve it with a simple query, even lots of small queries. > > Having to manually hack the query through EM constantly and get > down to deleting 2, 5 records at a time (IF that) without timeout > is ridiculous and unworkable given the incoming daily volume so > surely it's getting worse instead of better. > > --- > > Now given you can see clearly the query analyzer result, the > actual query, the actual error message, does anybody have any > ideas? What would you do? > > > Many grateful thanks, > PJ > > > --- > [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
