Multiple Alter Table Statements in one batch

[Former Member]

Hi Team,

We have in one of our upcoming release two columns being added to a table that has over 20 million records and 14 indexes.

We needed to add two columns to the table both not null (bit). Because it was taking a while to add the columns, we thought that putting these two alter statements in one batch would speed up the operation significantly but to my surprise it did not.

Conclusion from my test: individual alter statements or batch alter statements take the same time

Here are me test and results - table Order1 and Order2 are exactly the same structure and data.

Test case 1:

===================

ALTER TABLE Order1

ADD OR_N BIT DEFAULT 0 NOT NULL

go

ALTER TABLE AccountTradeConfirmation_Alter1

ADD OR_S BIT DEFAULT 0 NOT NULL

Go

Elapsed Time: 2 hrs

-------------------------------

             Mar 18 2015 5:56PM

(1 row affected)

Non-clustered index (index id = 3) is being rebuilt.

Non-clustered index (index id = 4) is being rebuilt.

Non-clustered index (index id = 5) is being rebuilt.

Non-clustered index (index id = 6) is being rebuilt.

Non-clustered index (index id = 7) is being rebuilt.

Non-clustered index (index id = 😎 is being rebuilt.

Non-clustered index (index id = 9) is being rebuilt.

Non-clustered index (index id = 10) is being rebuilt.

Non-clustered index (index id = 11) is being rebuilt.

Non-clustered index (index id = 12) is being rebuilt.

Non-clustered index (index id = 13) is being rebuilt.

Non-clustered index (index id = 14) is being rebuilt.

(21777920 rows affected)

Non-clustered index (index id = 3) is being rebuilt.

Non-clustered index (index id = 4) is being rebuilt.

Non-clustered index (index id = 5) is being rebuilt.

Non-clustered index (index id = 6) is being rebuilt.

Non-clustered index (index id = 7) is being rebuilt.

Non-clustered index (index id = 😎 is being rebuilt.

Non-clustered index (index id = 9) is being rebuilt.

Non-clustered index (index id = 10) is being rebuilt.

Non-clustered index (index id = 11) is being rebuilt.

Non-clustered index (index id = 12) is being rebuilt.

Non-clustered index (index id = 13) is being rebuilt.

Non-clustered index (index id = 14) is being rebuilt.

(21777920 rows affected)

-------------------------------

             Mar 18 2015 7:52PM

Test case 2:

===================

ALTER TABLE Order2

ADD OR_N BIT DEFAULT 0 NOT NULL, OR_S BIT DEFAULT 0 NOT NULL

go

2 hrs elapsed time

-------------------------------

             Mar 20 2015 11:10AM

(1 row affected)

Non-clustered index (index id = 3) is being rebuilt.

Non-clustered index (index id = 4) is being rebuilt.

Non-clustered index (index id = 5) is being rebuilt.

Non-clustered index (index id = 6) is being rebuilt.

Non-clustered index (index id = 7) is being rebuilt.

Non-clustered index (index id = 😎 is being rebuilt.

Non-clustered index (index id = 9) is being rebuilt.

Non-clustered index (index id = 10) is being rebuilt.

Non-clustered index (index id = 11) is being rebuilt.

Non-clustered index (index id = 12) is being rebuilt.

Non-clustered index (index id = 13) is being rebuilt.

Non-clustered index (index id = 14) is being rebuilt.

(21777920 rows affected)

-------------------------------

             Mar 20 2015 1:12PM

[Former Member]

Generally I'd expect that one "alter table" would be quicker than two.

However, with your case there may be a reason why there's little difference (and its a guess)

You're adding 2 bit fields.

Is the OR_N the first bit field on the table ?

If so, then the server will allocate 1 byte for the bit (as thats the smallest unit of storage).

But, the second bit field OR_S doesn't need any storage as it will "piggy-back" off the OR_N field.

(This will continue for the next 6 bit fields and the 9th bit field will allocate another byte)

All it has it do is to AND the field to make the values 0 for this bit.

It would be interesting to see the time to run the first alter table and the time to run the second alter table separately.

As I said only a guess.

Also check the amount of disk reads are being done.

If it has to read all the data off disk, (say 6GB) then at 5ms per 16k page would take 43 mins just to read the data. The second alter table could read from cache and take just a couple of mins.

[Former Member]

Also check the amount of disk reads are being done.

If it has to read all the data off disk, (say 6GB) then at 5ms per 16k page would take 43 mins just to read the data. The second alter table could read from cache and take just a couple of mins.

This is an interesting test. I will schedule this to see how long it takes to do each individual process. I also assume that the rebuild index  does not do the same work as say a reorg rebuild table.

Thanks

[Former Member]

BTW...

If performance is important to you - check the performance of the disks.

Are you using SANs ? SANs are usually overloaded and can yield poor performance.

We have SAN's here which vary between 1ms per page read and 15ms per page read.

That means that the same query on the same hardware can take 15 times longer on one machine than the other.

Clear the cache and run some selects with set statistics io, time on and see the time to read 1 page

(Disks work in IOPS not BPS). If you're using SANs run throughout the day - you'll find slow times and fast times.

We've investigated alls sort of tuning measure (compression, 16k pages, partitioning, re-indexing, config tuning, syslog on separate cache, tempdb conf, multiple tempdbs, etec) and the most useful was simply to buy a machine with 3 times more RAM.

Not suggesting that the same will apply to everyone but I generally work in places with lots of data.

[Former Member]

I've done some tests on this ...

and I get different results to you....

Using a table of 1,000,000 rows with no indexes and

     adding 2 columns at once took about 4s

     adding 2 columns with 2 commands took about 7s

So better to do it in one command

Using a table of 1,000,000 rows with a single index and

     adding 2 columns at once took about 9s

     adding 2 columns with 2 commands took about 15s (8s + 7s)

Still better as one command

(Note : lots of rounding in these )

If the data is flushed out of cache then the times increase by 50%.

Adding 2 columns with 2 commands took about 21s (14s + 8s)

The first alter table gets everything off disk and the second doesn't touch the disk so much faster.

I suspect you will see the same effect.

BTW, I also ran this on a 35m row table with 1 index and it took 9 minutes which is substantially faster than your run. What hardware are you using ? What disks are you using ?

[former_member187136]

Hi Courtney,

I think it should not cause any difference except write to disk else all should be same.

Even if you run multiple alter at one time in single transaction the process will be single and locks and semaphore will be same, so even if you run in multiple command or spid, it has to wait for the locks & timing will be same.

Also I/O will not have major impact as it is only dealing with structures.If the alter table has to do select into and with many data then obviously it consumes lots of I/O which in any case will be in asynchrnous.

But running in multiple session, it can have multiple alter simultaneously and if alter needs I/O on data part which can use multiple engines it will be faster.

If you test this same scenario on insert you will feel the difference, give a try

Regards

Kiran K Adharapuram

[Former Member]

Hi Kiran,

I have read your response a few times and I was not able to understand your angle. I assume based on the results of my test that Sybase does the following in processing the alter statements

ALTER TABLE Order2

ADD OR_N BIT DEFAULT 0 NOT NULL, OR_S BIT DEFAULT 0 NOT NULL

go

----

process alter ADD OR_N BIT

-- > make copy of table

---> alter original table

--> put data back in

process alterOR_S BIT

-- > make copy of table

---> alter original table

--> put data back in

rebuild index

my expectation was that it would make a copy of the table only once and process the two alter statements. Also when doing the alter separately (test1) it rebuilt the index twice, however using the batch the index was rebuilt once (at least only one message displayed).

Regards.

[former_member188958]

When ALTER TABLE has to do a data copy (i.e, when the column isn't nullable with no default) it essentially does a SELECT INTO under the covers, adding the default values for the new columns.  If there is a clustered index, that index is forced so the rows end up in clustered index order.  The original table is then dropped and the system catalogs and memory structures jiggered with to give the new table the same object id and name as the original table.  Indexes are then rebuilt because all the data pages have moved to new pages.  (text and image is not affected and does not move).

I do find these sample times odd, I would expect using two ALTERS would take about twice as long as just one that did both columns.