Post reply

Index? Table Scan? Performance? Billion Records?

  • June 10, 2009 at 4:56 am

    #73922

    Hi All,

    I am in the middle of fine tuning a query.

    Details:

    Table : nav

    Record_Count : 53734143

    Lock Schema : AllPage

    Index Type : Unique Clustered

    Columns : fund_id,fund_src_dt,fnd_cd

    My query:

    select fund_id,max(fund_src_dt) fund_src_dt, 0

    from nav

    where fund_src_dt = '22/may/2009'

    and fnd_cd = 1

    group by fund_id

    If I execute the above query, it is taking 5 mins.

    So i though of creating an unique non clustered index like below

    create unique nonclustred index id1 on nav(fuund_src_dt,fnd_cd)

    but when i execute the above command, it ran for more than 30 mins.

    I have also seen the query plan, optimizer uses 'TABLE SCAN' to solve the query.

    I have seen when the update statistics ran and came to know it ran on 08/jun/2009.

    Inputs are welcome!

    karthik

  • June 10, 2009 at 8:23 am

    #1007439

    Please post full table definitions, index definitions and the execution plan. To get the exec plan on SQL 2000 add the following before the query and run it.

    SET STATISTICS PROFILE ON

    GO

    There will be a second result set produced. Copy that, paste in excel, save the spreadsheet, zip it and attach it to your post.

    Are you sure is says 'table scan'? With a clustered index on the table it should be a clustered index scan, or did you drop the clustered index when you created the nonclustered index?

    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

Viewing 2 posts - 1 through 2 (of 2 total)

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