November 2, 2004 at 12:12 pm
This is driving me crazy as well as putting me way behind.
I have a good sized table (SQL 2000) with some data that needs cleanup (i.e. replace some incorrect values with corrected; about 500K rows with indexed identity column). I am getting hit with response times so incredibly slow (whether using QA or EM) as to make these relatively simple queries useless.
For example, the simple select statement (on varchar(50) fields) brings up the half dozen rows as fast as you can click the mouse:
SELECT , , , name
FROM newmaster
WHERE ( = 'V5940R,V5941R')
But an update, with virtually identical structure runs on 15-20 minutes, never finshing because I need to do many of these and cannot sit around for such slow response.
UPDATE newmaster
SET = 'V5940R', = 'V5941R'
WHERE ( = 'V5940R,V5941R')
I tried indexing each of these three fields but that made no visible difference.
I can't understand why this is running so slow, or what to do to fix it.
Jay
...
-- FORTRAN manual for Xerox Computers --
November 2, 2004 at 2:25 pm
I think you need to write:
WHERE ( IN ('V5940R', 'V5941R'))
I wasn't born stupid - I had to study.
November 2, 2004 at 9:08 pm
What indexes do you have and is there a clustered index on the fields you are changing?
Also, are you doing this while other users are accessing the data?
I have learned the hard way clustered indexes on frequently changed fields can be extremely painful.
If the phone doesn't ring...It's me.
November 3, 2004 at 2:45 am
Batch the update it'll run much faster.
See Microsoft's Tips and Tricks centre for help - it's an old trick that works real well.
http://www.microsoft.com/sql/techinfo/tips/development/updatebatches.asp
November 3, 2004 at 10:09 pm
is the datatype of I&MPressureVessel3 also varchar(50)?
Run the following:
/********
set statistics profile on
go
Update blah blah
set statistic profile off
go
***//
Check the output...
Look for Index scans ...
or
returned rows vs. estimated rows where ret rows outside 20% of estimated rows in either direction.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply