SQL Server Performance

Non-clustered index to 20+columns

Discussion in 'SQL Server 2005 Clustering' started by bab_ganesh, May 26, 2008.

  1. bab_ganesh New Member

    Hi All,

    I have a table with 35 columns,3+ million records,1 clustered index unique field. I developed a search application to search the records from this table. As of now the search is very slow.

    I tried to set the non-clustered index to a column (varchar(255) datatype). Then my search is gets fast for this column. And tried to next column, then its too fast.
    But my doubts is if i set non-clustered index to 20+ columns that what will happen my large table. The insert/update is happening twice a day in this table.
    What will be the performance of my table? If too much of non-clustered indexes is worst way then what is alternate one?

    Please tell me the answer as soon as possible. Awaiting for your valuable response.
    Thanks in advance
    Ganesh.

  2. FrankKalis Moderator

    That's the downside when you allow to search on almost every column. I would rather restrict this to commonly used columns in searches and try to find a covering nonclustered index that is used by your searches. Maybe some indexed view is also helpful.

  3. bab_ganesh New Member

    HI Frank,
    Thanks for your valuable response. I hope the indexed view is helpful to my process. Please tell me if I set the non-clustered index to the view then Will it happen anything on the base table while insert/update/delete?
    Is there any other major drawback using Indexed views?
    Awaiting for your valuable response.
    Ganesh.


  4. FrankKalis Moderator

    Changes to the base tables are immediately promoted to the indexed view in the same atomic operation. So when the modification is done, the view is also "up-to-date".
    The drawback is probably the storage overhead as you need additional storage space. The drawback is also the trade-off between data retrieval and data modification as the view needs to be maintained as well. another "drawback" might also be that the view is only automatically consider by the optimizer in Enterprise edition. In all other editions you would have to use WITH (NOEXPAND) to consider the view.
    You see, you need some testings in your own environment to judge on this. [:)]
  5. satya Moderator

    What is the resulting row count in using all of these columns?
    In any case using INDEXED view is better to take on, but that depends on version of SQL you are using.

Share This Page