SQL Server Performance

Challanging scenario in Index

Discussion in 'SQL Server 2005 General Developer Questions' started by bab_ganesh, Aug 3, 2008.

  1. bab_ganesh New Member


    I have a table with 4 million records,35 fields. A primary key field. Every 1 hours 1000s or 10000s of records inserting in this table using bulk insert.

    I have report in my web application querying 25 fields including the primary key field. But while querying the primary key field then the result pretty fast. But quering the non-indexed field then the process dead slow.

    So that I created an indexed view with all the 25 field. Then set the clusterted index and the non-clustered Index for 24 field. Then my all the query are Good fast.

    Now I expirencing that, the insersion in the base table is too slow. I doubt the indexed view ( bcz SQL up-to-date the indexed view too while insersion happen in the base table - I think it takes time to up-to-date)

    So drop the indexed view. Now my all the queries gets slow. :( I don't know What I do next?

    Can I set the non-clustered index to all the 24 fields.(Querying fields) in the base table itself.? Will it leads any problem mentioned above.?

    Please suggest me.

    Thanks in Advance
  2. madhuottapalam New Member

    Without any doubt, indexed view will cause performance hit in Insert/Update/Delete. In this scenario, you may try Covering index on the base table. Create a covering index which include all the 25 columns. Keep the most selective column at the first in the index and so on
    SQL Server Blog
    SQL Server 2008 Blog
  3. bab_ganesh New Member

    I could not create covering Index for 25 columns. Its alowing only 16 columns and sum of 900 bytes. But I have 25 columns with 4000+ bytes. :(
  4. Adriaan New Member

    Have you looked at the INCLUDE option for the CREATE INDEX syntax?
  5. bab_ganesh New Member

    Ya.. Index created with INCLUDE option. Now how about performance of the insert and update process. I see in a website that if more indices then the select will performance fast and INSERT and UPDATE reflect poor performance. Is it true?
    Then whats the way to handle both select and insert/update/delete for a table in efficient performance.?
  6. bab_ganesh New Member

    I saw the Execution plan for the covering indexes. The operator cost is high. nearly 40.xx. Is it OK?
  7. SQL2000DBA New Member

    I dont think by creating non-clustered index on all 24 fields will increase the performance of your queries.
    First try to analysis which queris are causing issue(i.e performance is below expectation). fine tune such queries. In case you are not sure which columns to consider for nonclustered. Take help of Index Tunning Wizard. Using ITW, I have reduced query performance from 4minutes to 1second.
  8. moh_hassan20 New Member

    what is the hardware configuration you use for that system?
  9. bab_ganesh New Member

    Windows 2003 Server R2 Dell Server with 4GB of Ram and some 200GB of HDD.
    SQL Server 2005 Enterprise Edition.

  10. preethi Member

    Can you post the worst select queries you are using along with the table and index information?
    I am suggesting some options based on the information you have given so far.
    I dont think Index on all 25 columns will help you in this matter. Either at the time of Inserts/Updates or during Selects you will pay the price.
    Can you check what are the columns involved in the Where and order by Clause in your Select queries? You may have to create indexes on them.
    Additionally, you need to reorganize/rebuild the indexes periodically.
    Also, you need to think whether you have the facility to partition the table. (Both Vertical and horizontal) Horizontal partition helps the table to perform better especially if you can have multiple hard disks
  11. bab_ganesh New Member

    Thanks for your reply.
    My problem is that i dont know which field will query commonly. All the 25 fields are coming in the scene.
    (Here I have given the dummy table and field name. Please adjust..[:)] )
    SELECT * FROM TableName WHERE Field1 = 'value' ANDField2='value' AND Field3 IS NULL AND Field4 BETWEENCONVERT(DATETIME,'...',103) AND
    CONVERT(DATETIME,'..',103) OR (Field5>1050)
    Seethis is my query. Varchar field, numeric field and date field iscomming in the select query. One user query these condition. Anotheruser will query other fields. So I dont know which field can query.
    [quoteuser="preethi"]I dont think Index on all 25 columns will help you inthis matter. Either at the time of Inserts/Updates or during Selectsyou will pay the price.[/quote]
    Yes. I accept. I feel it.
    Please give your valuable suggestion
  12. preethi Member

    You can run profiler over a period of time to find whcih queries are executed much and taking more time.
    In most of the cases you search for information based on date time? If so, create an index on field4.
  13. bab_ganesh New Member

    Can you tell me which order Can I create the index?
    Like DateTime, Numeric, etc.,
  14. madhuottapalam New Member

  15. moh_hassan20 New Member

    [quote user="madhuottapalam"]

    Index should be created according to the selectivity of the columns. Most selective column first and so on.
    ...In condition that query include ALL FIELDS, or part of fields in the same order of index keys ,
    else optimizer will not use that index.
  16. moh_hassan20 New Member

    If you use clustered index , Don't? why
    Any insertion will lead to rebuilding the clustered index and reorganize rows physically , and rebuild unclustered index.
    Avoid using clustered index (only on that case).[;)]
    what is the PK of your table?
  17. Adriaan New Member

    Adding indexes will increase execution time for inserts, updates and deletes. Adding an indexed view has the same effect.
    You didn't say which index has an INCLUDE on non-key columns.

Share This Page