SQL Server Performance

Splitting Tables?

Discussion in 'Performance Tuning for DBAs' started by djfurthur, Nov 5, 2003.

  1. djfurthur New Member

    I have a partitioned view that has POS Sales (Week #, Store #, Item #) By Quarter. We thought it would be more efficient to change the quarter tables into week tables. Each of the quarter tables has the following clustered key (Store #, Item #, Week #). The quarter tables have a week range constraint. When we divided these tables up, we thought we could eliminate the Week # from the key, but it turns out that that makes it impossible to insert into the partitioned view (which kinda defeats the purpose of it). Right now, our new tables are keyed (Week #, Store #, Item #) because we thought that the Week # would be one node at the beginning of the tree to find a row. Unfortunatly, this hasn't been the most efficient method for arranging data. I could explain furthur if needed, but can anyone give me some areas to look at to make this more efficient?

    thanks,

    Chris
  2. vbkenya New Member

    Partitioning your data by the Week# does not imply that you must have a key on that column either within a clustered (or any) key at all unless you are actually using the Week# in queries.

    What sort of efficiency are you looking for? Data retrieval (reporting)? storage ( read archiving)?

    The queries (and their frequency) that 'you' send to SQL Server should determine the keys you use for your indexes. Archiving requirements, on the other hand, may not play a big role as such in your index architecture and may even be the 'rate limiting step' in your reporting and other data manipulation processes.

    You may want to reassess the usefulness of the secondary columns in your index in relation to the primary DML activity (workload) on the table.

    By the way, waht was the reason for partitioning your table on the Week# column?

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com

Share This Page