a quick question

[The Doctor]

OK, so I'm running an SQL statement on a 903,000 row table in a clients database:

Code: Select all

UPDATE customer
SET Email = null
WHERE Email is NOT NULL

.

It's taken over 3.5 hours now and still going... can anyone give an estimation on how much longer this statement should run?

[Sol]

What SQL are you using? And no, simplex queries like that should take only a few secs for a mill or so rows (for ref I can set NULL to a column for any condition and execute it at a rate of 1k rows per 0.0197 secs at worst).
Now, whether you have a heavy load on the server your messing around with or not is a question you should ask yourself, it would have to be a **Filtered** of a load to slow it by a few hours though.

[The Doctor]

What SQL are you using? And no, simplex queries like that should take only a few secs for a mill or so rows.

Database was migrated from SQL Server 2003 to SQL Server 2012 R2 x64. I'm setting up their test server from a recent migration from their production server, and before I upgrade the database, I have to remove customer communications so customers aren't getting spammed by the companies testing... it should not take 3.5+ hours!

[Sol]

I want to say your query is wrong but... I'm not entirely sure regarding SQL 2012. If you know the server isn't flooded with capacity due to something, then it's probably the query, I would recommend trying something different like...(and I'm no pro in sql 2012)

Code: Select all

for transact sql

USE AdventureWorks2012;
GO
UPDATE database.Customer 
SET database.Customer.Email = NULL
WHERE database.Customer.Email IS NOT NULL;
GO

or...

UPDATE database.Customer 
SET database.Customer.Email = NULL
WHERE database.Customer.Email IS NOT NULL;

Either way, that execution time is abnormal.

[The Doctor]

I want to say your query is wrong but... I'm not entirely sure regarding SQL 2012. If you know the server isn't flooded with capacity due to something, then it's probably the query, I would recommend trying something different like...(and I'm no pro in sql 2012)

Code: Select all

for transact sql

USE AdventureWorks2012;
GO
UPDATE database.Customer 
SET database.Customer.Email = NULL
WHERE database.Customer.Email IS NOT NULL;
GO

or...

UPDATE database.Customer 
SET database.Customer.Email = NULL
WHERE database.Customer.Email IS NOT NULL;

Either way, that execution time is abnormal.

Database isn't being flooded, I'm the only one who can access it atm, and I've turned off all of our software which can communicate with it... I'm wondering whether it may be due to the relationships set by PKs and FKs, or the fact the database I'm upgrading from (in regards to our software) is quite old...

I won't be home for another two hours, so I'll check then, but if it still isn't done by then... I'm not sure what I can do, coz if I cancel the query, it will rollback the changes, which will take even longer >.<

[Sol]

I want to say your query is wrong but... I'm not entirely sure regarding SQL 2012. If you know the server isn't flooded with capacity due to something, then it's probably the query, I would recommend trying something different like...(and I'm no pro in sql 2012)

Code: Select all

for transact sql

USE AdventureWorks2012;
GO
UPDATE database.Customer 
SET database.Customer.Email = NULL
WHERE database.Customer.Email IS NOT NULL;
GO

or...

UPDATE database.Customer 
SET database.Customer.Email = NULL
WHERE database.Customer.Email IS NOT NULL;

Either way, that execution time is abnormal.

Database isn't being flooded, I'm the only one who can access it atm, and I've turned off all of our software which can communicate with it... I'm wondering whether it may be due to the relationships set by PKs and FKs, or the fact the database I'm upgrading from (in regards to our software) is quite old...

I won't be home for another two hours, so I'll check then, but if it still isn't done by then... I'm not sure what I can do, coz if I cancel the query, it will rollback the changes, which will take even longer >.<

Hmmm...it could be due to compatibility I guess, 2003-2012 is a fair jump, but to screw over a fairly basic query...
I feel like either way you look at it the query will have to be terminated

[Field Marshall]

I'd say the replicators have something to do with it.

Not sure. Though those bugs do give me the creeps.

[The Doctor]

I want to say your query is wrong but... I'm not entirely sure regarding SQL 2012. If you know the server isn't flooded with capacity due to something, then it's probably the query, I would recommend trying something different like...(and I'm no pro in sql 2012)

Code: Select all

for transact sql

USE AdventureWorks2012;
GO
UPDATE database.Customer 
SET database.Customer.Email = NULL
WHERE database.Customer.Email IS NOT NULL;
GO

or...

UPDATE database.Customer 
SET database.Customer.Email = NULL
WHERE database.Customer.Email IS NOT NULL;

Either way, that execution time is abnormal.

Database isn't being flooded, I'm the only one who can access it atm, and I've turned off all of our software which can communicate with it... I'm wondering whether it may be due to the relationships set by PKs and FKs, or the fact the database I'm upgrading from (in regards to our software) is quite old...

I won't be home for another two hours, so I'll check then, but if it still isn't done by then... I'm not sure what I can do, coz if I cancel the query, it will rollback the changes, which will take even longer >.<

Hmmm...it could be due to compatibility I guess, 2003-2012 is a fair jump, but to screw over a fairly basic query...
I feel like either way you look at it the query will have to be terminated

It's going on 7 hours now!... and if I cancel it, it will rollback the changes, taking as much time, if not more than it has been currently using...

[The Doctor]

I did notice that earlier when running a 'sp_who2' its status was set as suspended... coinciding with a few SQL Agent jobs running... may have contributed to the amount of time it has taken to run this.

Either that, or as FM said... the damn replicators

[Drought]

why not just set the entire column to null instead of checking each entry if it isnt already ?

update table.column set email = NULL


concerning the lengthy time, any chance your using locking methods on the column ?

[Field Marshall]

why not just set the entire column to null instead of checking each entry if it isnt already ?

update table.column set email = NULL


concerning the lengthy time, any chance your using locking methods on the column ?

Pssh.

Script0rz

[Drought]

Pssh.

Script0rz

Shouldnt you be pestering people who like gay folks ?


oh wait ...


I could sum you up with a few words
Traitor ...
Thief ...
Multi ...

[The Doctor]

Just checked on it after waking up... 14+ hours!!!!!! Next time I might just go with the statement Drought mentioned. I originally went with the check for if it is already null, but thats probably what is locking it up.

[Field Marshall]

Pssh.

Script0rz

Shouldnt you be pestering people who like gay folks ?


oh wait ...


I could sum you up with a few words
Traitor ...
Thief ...
Multi ...

eh bro? u mad?

[Sol]

why not just set the entire column to null instead of checking each entry if it isnt already ?

update table.column set email = NULL


concerning the lengthy time, any chance your using locking methods on the column ?

Can be technically excessive, if it's already NULL you're resetting it. But either way, the actual query time should be insignificant.