Post reply

are there are any thing can do after create index to enhance query speed ?

  • January 3, 2022 at 1:41 am

    #3969156

    I work on sql server 2014 i need to enhance query

    my execution plan as below :

    https://www.brentozar.com/pastetheplan/?id=ryWtGAknF

    now i create index as execution plan analysis

    CREATE NONCLUSTERED INDEX MissingAttr_idx ON [dbo].[TPartAttributes] ([ZfeatureKey]) INCLUDE ([PartID],[Name])

    my question are there are any thing remaining can do to improve enhance

    after create index as above

  • January 4, 2022 at 12:50 am

    #3969506

    My first recommendation is that you actually desk check the query to make sure that you don't have anything "silly" in it.  For example, looking at your query, you have the following very "silly" bit of code in it...

    Or (PM.ZfeatureKey= 1506990016 And Name Not In('#4-40','#6-32','#1-64','#1-72','#8-32','#10-32','#2-56','#2-64','#3-48','#4-36','#3-56','#4-48','#6-40','#0-80','#5-40','#10-24','#12-24','#2-28','#4-20','#8','#4','#6','#2','#10','#14','#10-16','#2-32','#0.25-20','#8-18','#12-14','#4-4','#6-13','#6-19','#6-13','#6-20','#32','#6-13','#6-19','#6-9','#6-20','#2-32','#2-28','M3x0.5','M3 x 0.5','M2.5 x 0.45','M4 x 0.7','M3.5x0.6','M2 x 0.4','M1 x 0.25','M5 x 0.8','M4','M5','M2','M2.5','M3.5x0.5','M3.5x0.6','M2.5x10','M2.5x12','M3x6','M1.6x0.35','M2.5x11.3','M2.5x16','M2.5x6','M2.63x1.06','M2x10','M2x5','M2x6','M2x8','M3.5','M3.5 x 0.6','M3.5x0.5','M3x8','M3x9','M4x1.46','M4x12','M4x14','M4x22','M4x25','M4x30','M4x8','M3'))
    Or (PM.ZfeatureKey= 1506990016 And Name In     ('#4-40','#6-32','#1-64','#1-72','#8-32','#10-32','#2-56','#2-64','#3-48','#4-36','#3-56','#4-48','#6-40','#0-80','#5-40','#10-24','#12-24','#2-28','#4-20','#8','#4','#6','#10','#14','#32','#10-16','#2-32','#0.25-20','#8-18','#12-14','#4-4','#6-13','#6-19','#6-13','#6-13','#2','#6-19','#6-20','#6-9','#6-20','#2-32','#2-28','M3x0.5','M3 x 0.5','M2.5 x 0.45','M4 x 0.7','M3.5x0.6','M2 x 0.4','M1 x 0.25','M5 x 0.8','M3','M4','M5','M2.5','M3.5x0.5','M3.5x0.6','M2.5x10','M2.5x12','M3x6','M1.6x0.35','M2.5x11.3','M2.5x16','M2.5x6','M2.63x1.06','M2x10','M2x5','M2x6','M2x8','M3.5','M3.5 x 0.6','M3.5x0.5','M3x8','M3x9','M4x1.46','M4x12','M4x14','M4x22','M4x25','M4x30','M4x8','M2'))

    Do you see anything "silly" there? 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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