Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Advice about partitioned indexes…

    Old db. Migrating to SQLServer 2005 (from SQLServer 2000). One large table of health claims. About 10,000,000 rows.
    Loaded weekly. Add about 30,000 rows with each weekly load. Never update previous loaded records (once loaded they never change).
    Currently, reindex the entire table with each load. Very time consuming during the load. Would like to use a partitioned index to save processing time. If I understand correctly: a partitioned index will enable us to index just the 30,000 rows added with each row and add that to the partition.
    This partition is not needed to help speed user access or queries – only to speed up the load.
    What I’ve read says we need to partition the table as well and create separate files on separate drives – all of which seems like overkill…
    Question: can I just partition the index used on the table without partitioning the table? Is there a good source of information on this for someone that has never used a partitioned index? Or any other advice?
    Thanks for any advice…
    Ray

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't think that you can build an index in "chunks" unless you can guarantee that all of the data you add falls into a different "chunk" than any previous data. In other words if you have a single index and all of the data in a new "chunk" either falls "before" the existing data in your table or "after" it, then you can use partioned indexes to help with the load. If any part of the data that you're loading "crosses" even one row of what is already loaded, then you can't use a partioned index.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2004
    Posts
    57

    Archiving instead of Partitioned indexes

    Pat.
    Thanks. That's what I thought. The reality is none of our users touch the data outside the current year. And the indexes we use are clustered and have more the just a date element, which means they will 'cross' chunks. So I think the smart way to deal is better archiving. It seems like indexing might require a significant amount of design... Which might be more easily achieved by archiving...


    Ray

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Before you go to much work on archiving, I'd like to suggest one trick that might help in more ways than just your load. When it comes time to load a new week's worth of data:

    1) Drop all indexes on the table being loaded
    2) Load the data in bulk (I'd recommend using BCP if you can)
    3) Rebuild all indexes dropped in step 1

    My guess is that this will both load and run (day-to-day) faster than loading the data with the indexes in place.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •