Clustered Indexes


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Clustered Indexes

  1. #1
    Larry Hunter Guest

    Clustered Indexes



    Hi -

    I am working on a program that does a great deal of searching and sorting
    through very large recordsets. Currently, the performance is just ok. I
    am hoping to do better than that and one way of doing so that I have been
    told about is through the use of clustered indexes.

    Now, after doing some reading about clustered indexes I am thinking that
    this might be a really good approach. I am thinking about placing multiple
    fields within the clustered index but have been told that that approach will
    ultimately be counter productive.

    To clear up the confusion can someone please provide a brief overview of
    the best/worst uses for a clustered index?

    Many thanks

    Larry

  2. #2
    C. E. Buttles Guest

    Re: Clustered Indexes

    Clustered indexes are great for data that undergoes very little change. An
    example of this would be a data base of state names and abbreviations.

    They actually rearrange the data in the order of the index, so when using
    multiple columns, be sure they are in the order you want to see your data.
    For example a Driver's License database. If you put DL # first, then that
    is the primary order and since DL #s are unique, any further clustering is
    unnecessary. If, however, you want to look up people by name, then putting
    first name followed by last name in a clustered index would give you all the
    people of the same first name in a group and if they have the same last
    name, those will be then grouped as well.

    John Jones
    John Jones
    John Smith
    etc.

    If you add DL #s to the end of the cluster, then they will be filtered by
    that order last.

    John Jones 35578
    John Jones 43269
    John Smith 35577

    Of course, middle names/initials can contribute to the mix.
    John A. Jones 21906
    John A. Jones 43269
    John A. Jones 86349
    John C. Jones 35578
    John B. Smith 35577

    Note that the example is just that as a name search on names in a DL
    database would not be very productive. You still have to be sure you get
    the right one. Of course, other identifiers would be helpful, like address
    (but they change), SSN, etc.

    "Larry Hunter" <Hunterlmc@gateway.net> wrote in message
    news:38d266ed$1@news.devx.com...
    >
    >
    > Hi -
    >
    > I am working on a program that does a great deal of searching and sorting
    > through very large recordsets. Currently, the performance is just ok. I
    > am hoping to do better than that and one way of doing so that I have been
    > told about is through the use of clustered indexes.
    >
    > Now, after doing some reading about clustered indexes I am thinking that
    > this might be a really good approach. I am thinking about placing

    multiple
    > fields within the clustered index but have been told that that approach

    will
    > ultimately be counter productive.
    >
    > To clear up the confusion can someone please provide a brief overview of
    > the best/worst uses for a clustered index?
    >
    > Many thanks
    >
    > Larry



  3. #3
    Robert Varga Guest

    Re: Clustered Indexes


    Also, Clustered Indexes are (usually) more cost effective when dealing with
    data ranges within queries/sp's such as:

    select FirstName, Surname
    from Client
    where DateOfInput BETWEEN 'Jan 01 1999' AND 'Jan 23 1999'

    Make sure that when you create a table, you do not accept MSSQL's default
    for the PK to be a clusered index, as this is often not the most cost effective.

    Cheers
    Robert

    "C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
    >Clustered indexes are great for data that undergoes very little change.

    An
    >example of this would be a data base of state names and abbreviations.
    >
    >They actually rearrange the data in the order of the index, so when using
    >multiple columns, be sure they are in the order you want to see your data.
    >For example a Driver's License database. If you put DL # first, then that
    >is the primary order and since DL #s are unique, any further clustering

    is
    >unnecessary. If, however, you want to look up people by name, then putting
    >first name followed by last name in a clustered index would give you all

    the
    >people of the same first name in a group and if they have the same last
    >name, those will be then grouped as well.
    >
    >John Jones
    >John Jones
    >John Smith
    >etc.
    >
    >If you add DL #s to the end of the cluster, then they will be filtered by
    >that order last.
    >
    >John Jones 35578
    >John Jones 43269
    >John Smith 35577
    >
    >Of course, middle names/initials can contribute to the mix.
    >John A. Jones 21906
    >John A. Jones 43269
    >John A. Jones 86349
    >John C. Jones 35578
    >John B. Smith 35577
    >
    >Note that the example is just that as a name search on names in a DL
    >database would not be very productive. You still have to be sure you get
    >the right one. Of course, other identifiers would be helpful, like address
    >(but they change), SSN, etc.
    >
    >"Larry Hunter" <Hunterlmc@gateway.net> wrote in message
    >news:38d266ed$1@news.devx.com...
    >>
    >>
    >> Hi -
    >>
    >> I am working on a program that does a great deal of searching and sorting
    >> through very large recordsets. Currently, the performance is just ok.

    I
    >> am hoping to do better than that and one way of doing so that I have been
    >> told about is through the use of clustered indexes.
    >>
    >> Now, after doing some reading about clustered indexes I am thinking that
    >> this might be a really good approach. I am thinking about placing

    >multiple
    >> fields within the clustered index but have been told that that approach

    >will
    >> ultimately be counter productive.
    >>
    >> To clear up the confusion can someone please provide a brief overview

    of
    >> the best/worst uses for a clustered index?
    >>
    >> Many thanks
    >>
    >> Larry

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center