November 26, 2007 at 11:37 am
Hi Experts,
I want to add a new column, t_flag, to table customer that already has million records. this column must be bit and default to false.
i do this:
alter table customer
add t_flag bin not null default(0)
this runs very, very slow and just "hanging there"
what would be the best practice to do this that it'll run faster?
thank you for your help.
November 26, 2007 at 12:31 pm
keep in mind sqlserver is actualy recreating your table and indexes and inserting the existing rows into it.
IO is your slowest operation.
btw you can script this operation with Enterprise manager and this way see which steps sqlserver will taken to accomplish your action.
If you kill the operation, sqlserver will have to rollback the transaction, that will also take time.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 26, 2007 at 1:07 pm
thank you, it makes sense. so, you don't recommend changing my original script?
thank you.
November 26, 2007 at 1:58 pm
if you perform it the EM-way (rename / create new / insert / drop renamed), you may be able to avoid a needed rebuild index because of e.g relocated rows.
check out what EM might do.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 26, 2007 at 2:32 pm
[Code]
CREATE TABLE dbo.CustomerFlags (
FlagCode nchar(1) NOT NULL , -- change datatype if 1 letter is not enogh for some flags
CustomerID int NOT NULL ,
CONSTRAINT PK_CustomerFlags PRIMARY KEY ON (FlagCode, CustomerID)
)
CREATE INDEX IX_CustomerFlags ON dbo.CustomerFlags (CustomerID, FlagCode)
INSERT INTO dbo.CustomerFlags
(FlagCode, CustomerID)
SELECT 'T', CustomerID
FROM dbo.Customer
WHERE {your criteria for "T" flag}
-- To find out if the flag is set for the Customer:
SELECT C.CustomerID,
CASE WHEN F.CustomerID IS NULL
THEN 0 ELSE 1 END AS t_flag
FROM dbo.Customer C
LEFT JOIN dbo.CustomerFlags F ON C.CustomerID = F.CustomerID AND F.FlagCode = 'T'
-- To find all Customers with t_flag = 1
SELECT C.*
FROM dbo.Customer C
INNER JOIN dbo.CustomerFlags F ON C.CustomerID = F.CustomerID AND F.FlagCode = 'T'
[/Code]
If you need to set the flag to 1 add a row with CustomerID to this table, if you need to set it to 0 - remove CustomerID from there.
_____________
Code for TallyGenerator
November 26, 2007 at 3:36 pm
thank you so much!
November 26, 2007 at 11:47 pm
If you don't use the alter table.. add colum ... methode you also need to take care of foreign keys (from or towards your table) !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply