CODEWITHSUNDEEP
performanceindexingsybase
Jithu
Jithu

Reputation: 469

Sybase - Performance considerations while indexing existing table

I have a table in SYBASE which has around 1mio rows. This table currently does not have any index created and I would like to create one now. My questions are

  1. What precautions should I take before creating an index?
  2. Does this process require more tablespace to be allocated?
  3. Any other performance considerations I should take care of?

Cheers

Ranjith

Upvotes: 4

Views: 2537

Answers (1)

Robert
Robert

Reputation: 25753

  1. From manual.

    When to index

    Use the following general guidelines:

    • If you plan to do manual insertions into the IDENTITY column, create a unique index to ensure that the inserts do not assign a value that has already been used.

    • A column that is often accessed in sorted order, that is, specified in the order by clause, probably should be indexed so that Adaptive Server can take advantage of the indexed order.

    • Columns that are regularly used in joins should always be indexed, since the system can perform the join faster if the columns are in sorted order.

    • The column that stores the primary key of the table often has a clustered index, especially if it is frequently joined to columns in other tables. Remember, there can be only one clustered index per table.

    • A column that is often searched for ranges of values might be a good choice for a clustered index. Once the row with the first value in the range is found, rows with subsequent values are guaranteed to be physically adjacent. A clustered index does not offer as much of an advantage for searches on single values.

    When not to index

    In some cases, indexes are not useful:

    • Columns that are seldom or never referenced in queries do not benefit from indexes, since the system seldom has to search for rows on the basis of values in these columns.

    • Columns that can have only two or three values, for example, "male" and "female" or "yes" and "no", get no real advantage from indexes.

  2. Try

    sp_spaceused tablename, 1

    Here is link to documentation.

  3. Yes - Updating statistics about indexes.

    Here is link to documentation.

Upvotes: 1

Related Questions