"John Kotuby" wrote:

> Hi guys,
>
> I hope this is the last post from me for a while. Your help in the last 2
> weeks has been appreciated. We have run into problems with a production
> system that was deployed at a site with 4 times the data found at our other
> sites and have just 2 more weeks to optimize.
>
> We have found that using clustered primary keys on Order and Invoice tables
> has decreased the Read time dramatically. The system is highly transactional
> and now I am concerned about inserts.
> Each site has multiple internal Divisions. We have included the records for
> all Divisions (by table type) in a single table, rather than creating
> separate tables for each Division.. This results in a PK of (for example)
> DIVID + INVNUM.
>
> I am now expecting that we will be dealing with many page splits during peak
> transactional  periods because of the clustered index. In lieu of creating
> separate Invoice tables for each Division (a maintenance nightmare) how can
> we minimize the impact of page splits since new records will be inserted at
> the end of each "division group" within the table.
>
> I have set up a nightly maintenance plan that reorganizes Data and Index
> pages. I currently have it set to allow 10% free space. It also shrinks the
> database allowing 10% free space. Should the free space be increased to
> handle a daily load of about 40 new invoices per Division and 10 Divisions?
>
> Each record is about 3k in size with no variable size fields. Am I being
> overly concerned about the performance effects of inserts with the Clustered
> index?
>
> Please take into consideration that this sytem is deployed at 30 smaller
> sites already and there is a huge amount of code written to read and write
> on the basis of DIVID + INVNUM. There are about 12 other tables that hold
> accounting related info that get updated simultaneously with every write. Of
> course we will consider a complete re-write (if necessary) for future
> releases.
>
> Any suggestions would be appreciated. Thanks to all......
>
>
>

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:FB376DEF-6846-43D5-B437-9FC0FAFE5F46@microsoft.com...
> Hi John
>
> Your solution sounds like you may benefit from a partitioned view (see
> books
> online for more).
>
> John
>
> "John Kotuby" wrote:
>
>> Hi guys,
>>
>> I hope this is the last post from me for a while. Your help in the last 2
>> weeks has been appreciated. We have run into problems with a production
>> system that was deployed at a site with 4 times the data found at our
>> other
>> sites and have just 2 more weeks to optimize.
>>
>> We have found that using clustered primary keys on Order and Invoice
>> tables
>> has decreased the Read time dramatically. The system is highly
>> transactional
>> and now I am concerned about inserts.
>> Each site has multiple internal Divisions. We have included the records
>> for
>> all Divisions (by table type) in a single table, rather than creating
>> separate tables for each Division.. This results in a PK of (for example)
>> DIVID + INVNUM.
>>
>> I am now expecting that we will be dealing with many page splits during
>> peak
>> transactional  periods because of the clustered index. In lieu of
>> creating
>> separate Invoice tables for each Division (a maintenance nightmare) how
>> can
>> we minimize the impact of page splits since new records will be inserted
>> at
>> the end of each "division group" within the table.
>>
>> I have set up a nightly maintenance plan that reorganizes Data and Index
>> pages. I currently have it set to allow 10% free space. It also shrinks
>> the
>> database allowing 10% free space. Should the free space be increased to
>> handle a daily load of about 40 new invoices per Division and 10
>> Divisions?
>>
>> Each record is about 3k in size with no variable size fields. Am I being
>> overly concerned about the performance effects of inserts with the
>> Clustered
>> index?
>>
>> Please take into consideration that this sytem is deployed at 30 smaller
>> sites already and there is a huge amount of code written to read and
>> write
>> on the basis of DIVID + INVNUM. There are about 12 other tables that hold
>> accounting related info that get updated simultaneously with every write.
>> Of
>> course we will consider a complete re-write (if necessary) for future
>> releases.
>>
>> Any suggestions would be appreciated. Thanks to all......
>>
>>
>>