How to increase indexes performance for fast queries results

  • Hi All,

    I have a table named "Tbl_Inv" in my database that contains 4.7 Lacs records with six non clustered indexes on below mentioned columns:-

    ROWID, DOC_CAT, DOC_NO, I_CODE, BOXNO, AMT+GL_CODE

    This table does't have any clustered index.

    So Please help me and tell me should i create more clustered indexes or i need to create clustered index for fast query results.

    Thanx.

    Neel

  • Hello,

    First off all I would suggest to create a unique clustered index, on the column that is a primary key.

    In Your case this would be the ROWID column.

    Further IF is possible to change table schema structure, I would create a new column to act as the primary key, instead of ROWID, let's call it UID, using the most narrow data type SMALLINT, INT or BIGINT to further improve the unique cluster index efficiency.

    Important to remember! :

    You can create only one (1) clustered on a table, regardless the fact that is unique or not. Internally the db engine ensures the uniqueness of it.

  • --

  • Create an Clustered index. Try to minimize the no of non clustered by creating include indexes.

  • Are you facing any performane issues?

    Indexes are query specific.

    So, please post your SQL statement and execution plan.

  • Use sys.dm_db_index_usage_stats to know the effectiveness of your existing indexes.

  • Clustered indexes don't have to be the primary key column. Sometimes there are better places to put the.

    Take a read through these.

    http://www.sqlservercentral.com/articles/Indexing/68439/

    http://www.sqlservercentral.com/articles/Indexing/68563/

    http://www.sqlservercentral.com/articles/Indexing/68636/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hello MSzI,

    Firstly very thanx for your reply and According to your suggestions :-

    I created clustered index on ROWID column that is a primary key and i include DOC_CAT, DOC_NO, I_CODE, BOXNO, AMT+GL_CODE into one non clustered index.

    After this i running query "Select * from "Tbl_Inv" then it taking 1.24 minutes but before this it was taking 2.53 minutes to complete

    Is it right or pls suggest some technique that i need to do something different for fast query result because i need to minimize result time near at 50 seconds.

    Thanx

    Neel

  • neellotus07 (9/27/2012)


    ...After this i running query "Select * from "Tbl_Inv" then it taking 1.24 minutes but before this it was taking 2.53 minutes to complete...

    Have you cleared the buffer before doing this bench marking?

  • neellotus07 (9/27/2012)


    After this i running query "Select * from "Tbl_Inv" then it taking 1.24 minutes but before this it was taking 2.53 minutes to complete

    Why are you SELECTing all the 4,70,000 rows? Why don't you use WHERE condition?

  • No index will help a SELECT * FROM <table>. That is a read of the entire table, it's a table scan, nothing else. Indexes are mainly to help locate rows when you have a where clause or join.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • neellotus07 (9/27/2012)


    I created clustered index on ROWID column that is a primary key and i include DOC_CAT, DOC_NO, I_CODE, BOXNO, AMT+GL_CODE into one non clustered index.

    And are those good choices for indexes? Are they useful for queries?

    After this i running query "Select * from "Tbl_Inv" then it taking 1.24 minutes but before this it was taking 2.53 minutes to complete

    Is it right or pls suggest some technique that i need to do something different for fast query result because i need to minimize result time near at 50 seconds.

    Filter the data. Query and return just what you need, not the entire table

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    Please try to create the non cluster index which you are selecting and using in the where clause.

    If there are multiple columns, try to identify the main columns which will be main part of the non cluster index and other columns can be added in the Include clause of Non Cluster Index.

    Thanks

    Nitin Varshney.

  • If you are talking about a query that you have tried to benchmarked it can be pretty straight forward.

    Just script your table and post it.

    Along with that send the query that you are using for your testing.

    What columns should be included depends on the query there is no hard and fast rule.

    Regarding clustered or any index they should be on a column which is more selective and with the little information that you have provided, it looks like the RowID that you are using may be the right candidate.

    Cheers...

    Amit Pandey

    http://www.debugsql.com/

  • Hi GilaMonster,

    Firstly lot of Thanxxxxxx for your suggestions.

    I created clustered index on ROWID this column never used in Where Clause.

    And i created non clustered index on those columns which are always used in Where Clause. And I have also other columns that are currently using in where clause like as MRN_CAT,MRN_NO,REF_NO,REF_DT,ORD_CAT,ORD_NO etc, but i dont create indexes on these columns due to slowness of insertion, updation & deletion operations.

    As i already told you this table storing invoices detail so it contains records in Lacs and these records increasing day by day.

    And mostly reports, pop-up & views are created on the basis of this table's record so i need to run it fast.

    Should i create separate primary key & then clustered index on this key for better performance of clustered index.

    If you have any other good suggestions pls tell me i will be very thankfull to you.

    Thanx

    Neel

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply