SQL Server Performance

Indexing the tables

Discussion in 'Performance Tuning for DBAs' started by lakshmi.aravind@fidelity.co.in, Sep 29, 2007.

  1. lakshmi.aravind@fidelity.co.in New Member

    Hi experts,
    Im very very new to sql server world..wanted to know about the indexes to be created on the below mentioned table columns for making this view run fastly.
    CREATE View hrinu.Parity as
    select
    T1.Matcle as CorpID,
    T2.Nmpres as Name,
    T4.DATDEB as LeaveFrom,
    T4.TEMDEB as PM,
    T4.DATFIN as LeaveTo,
    T4.TEMFIN as AM,
    T10.LIBLON as LeaveType,
    T8.LIBLON as Location,
    T12.LIBLON as ParentOrg

    from HRINU.zy00 T1,
    HRINU.zy3y T2,
    HRINU.zy39 T3,
    HRINU.zyag T4,
    HRINU.zy38 T5,
    HRINU.zy1s T6,
    HRINU.zd00 T7,
    HRINU.zd01 T8,
    HRINU.zd00 T9,
    HRINU.zd01 T10,
    HRINU.zd00 T11,
    HRINU.zd01 T12
    where T4.Nudoss = T3.nudoss
    and T4.Nudoss = T1.Nudoss
    and T1.Nudoss = T2.nudoss
    and T3.nudoss = T5.nudoss
    and T6.nudoss = T1.nudoss
    AND T7.NUDOSS = T8.NUDOSS
    AND T9.NUDOSS = T10.NUDOSS
    AND T11.NUDOSS = T12.NUDOSS
    AND T3.IDWKLO = T7.CDCODE
    AND T4.MOTIFA = T9.CDCODE
    AND T5.IDESTA = T11.CDCODE
    and T6.stempl = 'A'
    and t7.cdstco = 'z04'
    AND T8.CDLANG = 'U'
    and t9.cdstco = 'DSJ'
    AND T10.CDLANG= 'U'
    and t11.cdstco= 'DRE'
    AND T12.CDLANG= 'U'
    and T4.DATDEB <= T3.DTEN00 and T4.DATFIN >= T3.DTEF00
    and T3.DTEN00 <= T5.DTEN00 and T3.DTEN00 >= T5.DTEF00
    and T6.dtef1s <= getdate() and T6.datxxx > getdate()
    Also Please suggest me some links where i can get info about the indexes that has to be created on these types of queries where joins are involves and exact match queries..!!!
    Thanks in advance
    Regrds
    Arvind L
    lakshmi.aravind@fidelity.co.in, Sep 29, 2007
    #2

  2. satya Moderator

    Best thing to start with checking on Execution Plan from Query Analyzer when running this procedure or TSQL.
    If not you could run PROFILER during this execution and capture the trace in order to submit the same to Index tuning wizard for a recommendation. This will give basic idea on what could be better on having indexes.
    In a nutshell we may not know other tables or associated objects with this table, relational data too and choosing only one part of index might dent the performance on other aspect, so better you start this exercise and come up with more explanation for a suggestion.
    Hope this helps.
    satya, Oct 1, 2007
    #3

  3. ranjitjain New Member

    You can even exercise Index Tuning wizard(ITW) or Database Engine Tuning advisor depending on sql server version to make more firm decisions.
    ranjitjain, Oct 1, 2007
    #4

Share This Page