Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    Dec 2003
    Posts
    61

    Unanswered: Indexing on a Large table ???

    I have a 1.2 million record (55 Column) table. I have an ASP page reading data from this table based on some criteria.
    The criteria uses anywhere from 1 to 10 columns in the where clause depending on the user's entry on the front end.

    Whats the best way to get the optimal performance for the query ??
    Is it advisable to create 10 individual indexes ???

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    First of all - what kind of database do you have - OLTP or WAREHOUSE?
    If it is OLTP - I do not envy you (You have to split your database: OLTP and WAREHOUSE). If WAREHOUSE - you have to have fact table(s) and dimensions and a lot of your problems are gone. In WAREHOUSE you can have as much indexes as you want - even for every field. You could find a huge amount of tips about indexes in net. This just an example:

    http://www.sql-server-performance.com/
    Last edited by snail; 12-06-03 at 10:08.

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Snail asks a good question, other than that you have to balance your inserts versus your queries. Each index you have is an overhead on your inserts, however, if you have heavy inserts it is essential to (1) have a clustered index and (2) to have it on the right type of column to avoid page splits.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    Dec 2003
    Posts
    61
    Originally posted by snail
    First of all - what kind of database do you have - OLTP or WAREHOUSE?
    If it is OLTP - I do not envy you (You have to split your database: OLTP and WAREHOUSE). If WAREHOUSE - you have to have fact table(s) and dimensions and a lot of your problems are gone. In WAREHOUSE you can have as much indexes as you want - even for every field. You could find a huge amount of tips about indexes in net. This just an example:

    http://www.sql-server-performance.com/
    There are no transactions (Inserts/Deletes) happening on the table. Its a kind of a static table only.

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    The table never gets updated ? Also, do you join to any other tables ? What are the variations of the columns in the where clause - are there consistent matchings of columns or are they totally independent ? For the 10 columns - how unique is the data ?

  6. #6
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    If there are no indexes, updates or deletes, index the heck out of it, why not?
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  7. #7
    Join Date
    Dec 2003
    Posts
    61
    Originally posted by rnealejr
    The table never gets updated ? Also, do you join to any other tables ? What are the variations of the columns in the where clause - are there consistent matchings of columns or are they totally independent ? For the 10 columns - how unique is the data ?
    The data in 10 columns is totally unique

  8. #8
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by forXLDB
    The data in 10 columns is totally unique
    Just create index for every column - even if you will do search for couple or more columns - only one or two indexes are used but it is possibility to for searching by every column.

  9. #9
    Join Date
    Dec 2003
    Posts
    61
    Originally posted by snail
    Just create index for every column - even if you will do search for couple or more columns - only one or two indexes are used but it is possibility to for searching by every column.
    There is every possibility of using all the columns for searching.

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    having an index for each field individually will have very little if any positive effect on performance if more than one field is present in the where clause. you'll need composite indexes to improve your performance, along with individual ones.

  11. #11
    Join Date
    Dec 2003
    Posts
    61
    Originally posted by ms_sql_dba
    having an index for each field individually will have very little if any positive effect on performance if more than one field is present in the where clause. you'll need composite indexes to improve your performance, along with individual ones.
    In that case, how do we design an indexing strategy which involves search based on anywhere from 1 to 10 columns.

  12. #12
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I follow this rule during indexing:

    if your SQL syantax is :

    Select * from Table WHERE Column1='a'
    AND Column2='b'
    AND Column5='c'

    then you should create composite index following your where clause syantax

    Composite index i_table1_1
    Column1
    Column2
    Column5


    so it is recommended that you follow a standard way of coding your SQL Syantax so as the sequence of your "WHERE" columns are almost uniform and you have less composite indexing to do.

    Alternatively you could also use the index tuning wizard from your SQL Query Analyzer. Copy paste your syantax to QA, press ctrl-i and follow the instructions from there.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  13. #13
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Patrick, depending on the size and number of columns in the table, SQL may not choose to use that composite index. For example if you have 10 columns and there is low selectivity on the three columns, it would not make sense to go to the composite index to fetch back to the data pages where the rest of the data resides (the *)

    Best thing would be to use the "set statistics IO on" command to see which indexes give you the lowest number of page reads.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  14. #14
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    aaah..ic..thanks for the tip...
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "having an index for each field individually will have very little if any positive effect on performance if more than one field is present in the where clause. you'll need composite indexes to improve your performance, along with individual ones."

    Not necessarilly. SQL serve can use more than one index at a time, and it you have multiple indexes set and keep good statistics then SQL server should be able to choose the most optimum combination of index and table scans. That's why its called the "optimizer".

    Covered (composite) indexes aren't going to help you much here because, according to your initial post, your query could search based upon any combination of columns. You would need a composite index for each potential combination of columns.

    blindman

Posting Permissions

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