SQL Server Performance

Setting Non-Clustered index in Indexed View Vs setting in Table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by bab_ganesh, Jun 16, 2008.

  1. bab_ganesh New Member

    Hi All,
    I have a table with 30 columns and 3 million records. I am quering a field amount 25 fields. All the 25 fields are searchable fields. When I quering this table with a field, it was taking too time to bring the result. I can feel this slow in the front end.(Web based application)
    Then I knew about the indexed view. So I created a indexed view with a unique field and the 25 fields.(bcz I dont want to disturb the large base table) I set Clustered index to the unique field and set the non-clustered index to all the 25 fields. Now my search is fast. I created the view like this:
    CREATE VIEW myview WITH SCHEMABINDING
    AS
    SELECT Field1,Field2...Field25 FROM tblTable
    Go

    Then created the indexes.
    I read so many articles about the indexed view, all are saying that indexed view should be used for aggregate, joins scenario. But my view is simply a select statement.[:(] Now I confused, Shall I maintain the indexed view or setting the non-clustered index to the fields of the BASE Table. ? I dont know whether this select statement view is right way to utilize the indexed View.
    Please kindly clarify my confusion.
    Thanks in advance
    Ganesh.
  2. FrankKalis Moderator

    Personally I think the "right" way to use some feature is the way that gives you the result you expect. So, if the requirement is to query that many columns in various combinations and you're happy with the performance, that's all fine. [:)]

Share This Page