Re: Adding columns to SQL Server tables dynamically
From: Sahil Malik (contactmethrumyblog_at_nospam.com)
Date: 12/05/04
- Next message: Gary F: "RE: How to resolve "General Network Error"?"
- Previous message: Gary F: "RE: How to resolve "General Network Error"?"
- In reply to: David Browne: "Re: Adding columns to SQL Server tables dynamically"
- Next in thread: Mark Rae: "Synchronising a DataRow in one DataSet with a structurally identical DataRow from another DataSet"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 4 Dec 2004 21:16:45 -0500
I couldn't agree more with you on the below, and I can't say I haven't
learnt anything from your very patient replies.
- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:%23L3I6Wl2EHA.1392@tk2msftngp13.phx.gbl...
>
> "Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
> news:%23WE4yqj2EHA.936@TK2MSFTNGP12.phx.gbl...
>> David,
>>
>> So would you recommend a production app to add columns through ado.net
>> sqlcommands? In other words; is it okay to have a production application
>> add columns on the fly to the database, in addition to
>> select/update/delete statements? I always thought that was not a
>> recommended practise precisely because you'd have to run DBCC DBREINDEX
>> on your production servers more frequently.
>>
>
> I wouldn't want to see columns added nearly as frequently as
> select/update/delete. But for, say, daily import job it isn't a very big
> deal, especially if the you only insert new rows with the added columns,
> not update existing rows. New nullable columns are really free to add:
> it's like they were there the whole time, but null on all existing rows.
> Mass updates which significantly increase the size of the target rows will
> cause page splits, and will take a long time.
>
> It really depends on the frequency of the changes and the size of the
> table. If the table has 100 million rows, then I would never ALTER it
> except over the Christmas holiday. For any table under a million rows,
> it's just not a big deal. And for any Sql Server whose active database is
> smaller than the amount of ram dedicated to Sql Server, it's just not a
> big deal, since Sql Server index fragmentation only really affects disk
> throughput.
>
> "Back in the day", say 1995, RAM sizes were much smaller, and disk access
> was relatively faster. Database servers used _much_ more physical IO than
> they do these days. Any issue affecting physical IO throughput went right
> to the bottom line of server performance.
>
> For instance you might have 4 100 mhz Pentium processors, 128 megs of ram
> accessed across a 33mhz bus, and 20gigs of storage spread among 10 hard
> drives. The hard drives were almost half as fast as todays drives, and
> the large number of spindles probably made up for it. But the RAM was 10x
> slower and 10x smaller, and the CPU was 30x slower. In addition to log
> flushing, any query of any size, most sorts, and many DML operations
> required really significant physical IO.
>
> Compare that with a low-end server today. 2 cpu's at 3.4ghz 2gigs of ram
> on the other end of an 800mhz memory bus, and 2 35gig SCSI drives in a
> mirror. It's not just that today's machine is faster, it's a different
> kind of machine: it's a machine where you only touch the disks when you
> need to push something to non-volatile storage. It's not that it's so
> fast that you don't have performance problems: you just have different
> performance priorities.
>
> David
>
- Next message: Gary F: "RE: How to resolve "General Network Error"?"
- Previous message: Gary F: "RE: How to resolve "General Network Error"?"
- In reply to: David Browne: "Re: Adding columns to SQL Server tables dynamically"
- Next in thread: Mark Rae: "Synchronising a DataRow in one DataSet with a structurally identical DataRow from another DataSet"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
