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
