Post reply

Indexes on View column -- performance

  • September 29, 2007 at 3:33 am

    #118843

    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.DTEF00

    and T3.DTEN00 = T5.DTEF00

    and T6.dtef1s 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

  • September 29, 2007 at 4:59 am

    #737346

    Just a couple pointers, there's lots of things to check and I don't have enough time, not info to do this.

    1 - Move the join syntax into joins and outside the where conditions

    2 - As a general rule, the columns in a join operation "should" be indexed (there are a lot of cases where it doesn't help, but it's always a good place to start).

    3 - Search this and target the article section, search for these and start reading (I'd allocate at least 200-300 hours on this topic alone, that's a huge topic in sql server and it can't be mastered in a day).

    Search terms : Performance tuning, covering indexes, index tuning.

    Let us know if you have a more specific question on the topic and we'll help you get through it.

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

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