1) Making something an 'identity field', does not automatically make it an
index. Also, you should make sure it does have an index and is clustered.
If so, and it is not working then proceed to step 2.
2) Change recovery model to bulk (This will decrease the reporting done and
save time).
3) Turn off logging, then delete.
If you still have a problem, decrease your fill factor on the index.
FYI, you can rewrite the entire database schema without having to
change your code. Setup Indexed views to act like the tables and it will
work fine.
Also, make sure you have no triggers being fired on delete. It
sounds like you have to much indexing or to much data in this table, so
normalize as soon as possible.
You can also loose the not in, which is sometimes a hog, and do:
"and id<>234 and id<>264 and id<>294"
Jacob
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Palyne PJ Gaenir
Sent: Thursday, September 30, 2004 4: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
