From: Ellen K. 

Are these four tables often joined in queries?   If so then putting them
on different disks is a good idea.  But you are going to need a LOT of
disks then because you still need redundancy and also putting the
transaction log on its own disk is probably more important.   How many
disks are you gonna have?

You can certainly add indexes once you move the back end to SQL Server.

You could also add constraints to handle the data validation that
apparently isn't being handled in the front end, but this can be a
double-edged sword because it means the transaction gets sent to the
database and then if it's not acceptable an error message is returned, i.e.
you have more network traffic.

On Tue, 23 Mar 2004 07:37:57 +0200, "Antti Kurenniemi"
 wrote in message :

>Thanks, Ellen. The database design is not an issue to us at this point
>because we can't do anything to it - and it is disaterously bad. Duplicated
>data all over the place, sucky indexing (useless ones + missing some
>important ones), and we can't change it because it's the "built
in" version.
>I'm planning on breaking the database on to different disks, because at the
>moment it's all in one big file but most of the stuff goes on in only four
>tables, so I think if I could break those, or at least their indexes, it
>might make a big difference.
>
>I'm really looking forward to getting to play with SQL server :-)
>
>
>Antti Kurenniemi
>
>"Ellen K." 
wrote in message
>news:j1gv505oi89pe5oj6vlue9b1uf2gf64i8m{at}4ax.com...
>> The one that's in full production has somewhere between 400 and 500
>> users and now that they're up to speed with the application they're
>> adding about 1GB a week.  Right now it's only about 32 GB but at this
>> rate it will be a lot bigger in no time.  This is the one with the
>> recordings.
>>
>> In general for a database if you can configure the box to have the
>> transaction log on its own drive(s) you're already way ahead, because
>> the log is constantly being written to.
>>
>> The reason it's good to have the nonclustered indexes on their own
>> drive(s) is that the nonclustered index records point to the clustered
>> index, not to the actual data, so it's always a two-step.  If the
>> nonclustered index is on a different drive from the clustered one, it
>> doesn't have to lose its place every time going back and forth.
>>
>> Disk I/O is the most important factor in database performance, and a lot
>> of memory is a good idea too.
>>
>> However (or of course) the above won't completely make up for a stupid
>> database design.  The database design is extremely critical for
>> performance.   SQL Server defaults to making the primary key the
>> clustered index (although you can override this), and you can only have
>> one clustered index per table.   The data are physically stored in the
>> order of the clustered index.  Therefore you should use natural primary
>> keys whenever possible.  For example, suppose you have a contract status
>> history table -- your PK should be the contract number and a sequence
>> number, that way all the records for each contract stay together.   If
>> you take the newbie way out and use identity columns (an otherwise
>> meaningless automatically incremented number that uniquifies each
>> record) for your PKs you not only open the door to duplicate data (rows
>> which are the same except for the artificial PK), you're storing the
>> rows in the order in which they're written to the database... To go back
>> to our example of   contract status history, the application is going to
>> want to see the history of a particular contract, which obviously it can
>> fetch much faster when finding the first row means it's found all of
>> them.   As Joe Celko has pointed out, the artificial PK is really a
>> reincarnation of the "record number" used in the Bad Old
Days of flat
>> files.
>

--- BBBS/NT v4.01 Flag-5