PLease suggest index for this table

  • Smash125

    Hall of Fame

    Points: 3529

    You cannot vote on your own post

    0

    I have table called FACT_NLTRANS. Basically this is fact table. I am using this table for my SSRS table which has 60 lakh records

    Could you please suggest right index for this table. Table definition as below.

    CREATE TABLE [dbo].[FACT_NLTRANS](

    [doc-id] [nvarchar](12) NULL,

    [docnumber] [int] NULL,

    [docdate] [datetime] NULL,

    [sourceacc] [nvarchar](20) NULL,

    [baseval] [numeric](17, 2) NULL,

    [currvalue] [numeric](17, 2) NULL,

    [qty] [numeric](18, 3) NULL,

    [yearno] [numeric](15, 0) NULL,

    [period] [numeric](15, 0) NULL,

    [periodid] [int] NULL,

    [trtyp] [nvarchar](12) NULL,

    [inputdate] [datetime] NULL,

    [stat] [nvarchar](2) NULL,

    [costcentre] [nvarchar](30) NULL,

    [expensecode] [nvarchar](30) NULL,

    [expense_sk] [int] NULL,

    [company_sk] [int] NULL,

    [currency] [nvarchar](8) NULL,

    [transdate] [datetime] NULL,

    [CREATED_BY] [varchar](50) NULL,

    [CREATED_DATE] [datetime] NULL,

    [UPDATED_BY] [varchar](50) NULL,

    [UPDATED_DATE] [datetime] NULL,

    [upddate] [datetime] NULL,

    [unqkey] [nvarchar](24) NULL

    ) ON [PRIMARY]

    GO

    We have to create non clustered index which column I need to go so that I can speed query.

    looking for your answers

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    It might be useful if you also gave us the query...

    (if you run the query and check the actual execution plan, it gives a hint about possible indexes)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Smash125

    Hall of Fame

    Points: 3529

    please find the query as below

    DECLARE @DrillSelected VARCHAR(100)

    if @DrillSelected is null

    begin

    select D.BUSINESSDIVISION,

    (SUM((nL.baseval)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END)) OUTSTANDINGAMOUNT

    FROM

    vw_fact_nltrans nl

    INNER JOIN

    VW_DIM_COMPANY C

    ON nl.COMPANY_sk=C.COMPANY_sk

    INNER JOIN DIVISIONMAPPING D

    ON D.COMPANYID=C.COMPANYID

    INNER JOIN VW_DIM_EXPENSECODE EC

    ON NL.COMPANY_SK=EC.COMPANY_SK AND NL.EXPENSE_SK=EC.EXPENSE_SK

    LEFT OUTER JOIN VW_FACT_CURRENCY CURR

    ON c.COMPANY_SK=CURR.COMPANY_SK

    AND CURR.CURRENCY IN ('GBP','STER') where d.active in ('yes','no') and

    nl.yearno in (select distinct fiscalyear from VW_DIM_FISCAL_CALENDAR_PERIOD where periodid='201302') and nl.periodid<='201302' and (EC.EXPENSEGROUP IN ('1-###-##','2-###-##','3-###-##') OR EC.EXPENSECODE='4-030-04') AND C.COMPANY_DESC NOT IN ('Advanced Health & Care NZ Ltd')

    group by D.BUSINESSDIVISION

    end

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    First I would optimize the query a bit.

    select distinct fiscalyear from VW_DIM_FISCAL_CALENDAR_PERIOD where periodid='201302'

    I assume this only returns one single value? If yes, I would execute this query first and store the result in a variable. Then use this variable in the where clause of the main query and use = instead of IN.

    Why are there columns yearno and periodid in the fact table? Don't you have a date dimension?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • T.Ashish

    SSCarpal Tunnel

    Points: 4246

    create your index on

    yearno

    periodid

  • Gail Shaw

    SSC Guru

    Points: 1004484

    T.Ashish (10/24/2013)


    create your index on

    yearno

    periodid

    Sure that's the best index? A non-covering index that only supports the where clause.

    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
  • T.Ashish

    SSCarpal Tunnel

    Points: 4246

    GilaMonster (10/24/2013)


    T.Ashish (10/24/2013)


    create your index on

    yearno

    periodid

    Sure that's the best index? A non-covering index that only supports the where clause.

    Agree. Not the best.

    covering index will be a better idea but I think covering index will be limited to this query only.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    T.Ashish (10/24/2013)


    covering index will be a better idea but I think covering index will be limited to this query only.

    So you recommend an index on two columns but say that an index on those two columns plus a couple more will be limited only to this query? How does that work?

    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
  • Gail Shaw

    SSC Guru

    Points: 1004484

    Smash, it would be really useful if we could see the execution plan of that query, plus any other queries that run frequently against the table. Since we have little idea of data volumes or distribution it's really hard to suggest useful indexes.

    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
  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Just following a common practice in dimensional modelling, I would start with defining a unique index on your surrogate keys of the fact table.

    (they should be unique, if not your design is incorrect)

    And put all the date attributes in the date dimension.

    I see way too many date and varchar columns in your fact table. A fact table should only have numeric data (foreign keys which are integers and your measures).

    Normally you shouldn't filter on your fact table at all, you should filter on dimensions only.

    If you have SQL Server Enterprise, the database engine will detect it's a star join query and it can apply very efficient optimizations.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Smash125

    Hall of Fame

    Points: 3529

    Thanks guys all your input. Tuning of query is given to me in the morning, i have no idea about the query and database involved. Thank fully i went for Database Tuning Adviser for right indexes. DTV suggested some NON CLUSTERED INDEXES and STATISTICS. I took these and applied. It worked for me. Now queries are bit faster .

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    Smash125 (10/25/2013)


    Thanks guys all your input. Tuning of query is given to me in the morning, i have no idea about the query and database involved. Thank fully i went for Database Tuning Adviser for right indexes. DTV suggested some NON CLUSTERED INDEXES and STATISTICS. I took these and applied. It worked for me. Now queries are bit faster .

    There are still quite some flaws in the design that might need to be addressed.

    The Database Tuning Advisor is also not the most optimal tool there is to find. It's possible the indexes are useful for the one query, but they are useless for other queries.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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