Non-Clustered Indexes - Need some advice

  • Hello -

    I have 2 scenarios that I am needing to create some Non-Cluserted Indexes for:

    1. FK's on all of my tables, and...

    2. About 12 queries that are going to be used for reports.

    My question is should I make 2 groups of Non-Clustered Indexes across my tables? I was thinking of having them named FK_Index#(1) and FK_Index#(2) (where # = sequential numbering). The FK_Index1 would be only for FK's on that table, and the FK_Index2 would be for columns that may or may not be part of the FK's on that table, but will be used by the queries for the reports.

    Naming aside - is this a good strategy to go with? I do not have a proper test environment to see how this will behave (other than my own PC which will not give me anything close to the knowledge I need towards how it will perform), or is it okay for me to make one Non-Clustered Index for a given table (regardless to whether it's just the FK's of the table or the columns for the reports, or both) and just put all the columns in there that I would like to have as part of a Non-Clustered Index for the table?

    My instincts are telling me to have 2, but I want to check in with the experts in performance turning on this one.

    Many thanks in advnce...

  • None of the above. You need to create indexes that support the queries. Only time an index with 12 columns is useful for a bunch of queries is if all the queries filter on all 12 columns. For foreign keys the advice to index foreign keys does not mean one index with all the foreign key columns. Unless a query needs a left-based subset of the index key columns, it cannot seek on the index

    Take a read through this: http://www.sqlservercentral.com/articles/Indexing/68636/

    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! Thank you so much for this clarification! I was starting to wonder if I wasn't getting bad information from some of the sources regarding FK Indexing (as you always say - double check your source!)

    The reports though are 12 total - not necessarily 12 different columns. There are 12 queries being run for each report and in all honesty - there are a ton of columns being called.

    My developers did SELECT * from their factories. I tried to convince them otherwise, but there is 6 of them and 1 of me. Needless to say - I lost. :S

    But my Reports guy didn't go that route, but it's still a bit messy. Not sure if my instincts here are right or not, but any advice would be great. Perhaps it might help if I showed you one of the report's DML? I do not want to put all 12 of them out here, (as they are too large), but I will do one of the smaller ones (as they all follow the same kind of SELECT, FROM, WHERE logic). Hopefully this will show you why I am thinking that Non-Clustered Indexing is needed:

    private const string m_GetRecentBatch = "SELECT t.RetailerName, " +

    " b.TreaterID, " +

    " CONVERT(varchar(10),b.DTStart,101) AS DateStarted, " +

    " (SUBSTRING(CONVERT(varchar,b.DTStart,109), 13, 8) + ' ' + SUBSTRING(CONVERT(varchar,b.DTStart,109), 25, 2)) AS TimeStarted, " +

    " CONVERT(varchar(10),b.DTEnd,101) AS DateEnded, " +

    " (SUBSTRING(CONVERT(varchar,b.DTEnd,109), 13, 8) + ' ' + SUBSTRING(CONVERT(varchar,b.DTEnd,109), 25, 2)) AS TimeEnded, " +

    " (cu.Firstname + ' ' + cu.Lastname) AS CustomerName, " +

    " r.RecipeName, " +

    " b.CustBatch, " +

    " b.BatchID, " +

    " b.Notes, " +

    " (co.CompanyName + ': ' + cr.CropName + ' - ' + sv.VarietyName) AS SeedVariety, " +

    " b.SeedSize, " +

    " b.SeedUnits, " +

    " b.Temperature, " +

    " b.WindSpeed, " +

    " b.WheelFlowRate, " +

    " b.WheelFlowUnits, " +

    " b.WheelTotal, " +

    " ch.ChemicalName, " +

    " ch.UnitOfMeasure, " +

    " ch.FlexTank, " +

    " k.ChemicalBatch AS ChemicalBatch, " +

    " bk.KegUsage AS Usage, " +

    " cb.ActualFlowRate, " +

    " b.ApplicatorLicense " +

    "FROM BatchKeg AS bk, " +

    " ChemBatch AS cb, " +

    " Keg AS k, " +

    " Chemical AS ch, " +

    " TreaterInformation AS t, " +

    " Batch AS b, " +

    " Customer AS cu, " +

    " Recipe AS r, " +

    " Company AS co, " +

    " Crop AS cr, " +

    " SeedVariety AS sv " +

    "WHERE b.DTStart = (SELECT MAX(DTStart) AS DTStart FROM Batch WHERE TreaterID = b.TreaterID)" +

    " AND b.TreaterID = @TreaterID " +

    " AND k.KegID = bk.KegID " +

    " AND ch.FlexTank = 'false' " +

    " AND ch.ChemicalID = cb.ChemicalID " +

    " AND ch.ChemicalID = k.ChemicalID " +

    " AND cb.TreaterID = b.TreaterID " +

    " AND cb.BatchID = b.BatchID " +

    " AND t.TreaterID = b.TreaterID " +

    " AND bk.TreaterID = b.TreaterID " +

    " AND bk.BatchID = b.BatchID " +

    " AND b.CustID = cu.CloudID " +

    " AND r.RecipeID = b.RecipeID " +

    " AND sv.VarietyID = b.SeedVarietyID " +

    " AND co.CompanyID = sv.CompanyID " +

    " AND cr.CropID = sv.CropID " +

    " AND b.RecipeVersion = r.Version " +

    "UNION " +

    "SELECT t.RetailerName, " +

    " b.TreaterID, " +

    " CONVERT(varchar(10),b.DTStart,101) AS DateStarted, " +

    " (SUBSTRING(CONVERT(varchar,b.DTStart,109), 13, 8) + ' ' + SUBSTRING(CONVERT(varchar,b.DTStart,109), 25, 2)) AS TimeStarted, " +

    " CONVERT(varchar(10),b.DTEnd,101) AS DateEnded, " +

    " (SUBSTRING(CONVERT(varchar,b.DTEnd,109), 13, 8) + ' ' + SUBSTRING(CONVERT(varchar,b.DTEnd,109), 25, 2)) AS TimeEnded, " +

    " (cu.Firstname + ' ' + cu.Lastname) AS CustomerName, " +

    " r.RecipeName, " +

    " b.CustBatch, " +

    " b.BatchID, " +

    " b.Notes, " +

    " (co.CompanyName + ': ' + cr.CropName + ' - ' + sv.VarietyName) AS SeedVariety, " +

    " b.SeedSize, " +

    " b.SeedUnits, " +

    " b.Temperature, " +

    " b.WindSpeed, " +

    " b.WheelFlowRate, " +

    " b.WheelFlowUnits, " +

    " b.WheelTotal, " +

    " ch.ChemicalName, " +

    " ch.UnitOfMeasure, " +

    " ch.FlexTank, " +

    " ch.MaterialID AS ChemicalBatch, " +

    " cb.ActualUsage AS Usage, " +

    " cb.ActualFlowRate, " +

    " b.ApplicatorLicense " +

    "FROM ChemBatch AS cb, " +

    " Chemical AS ch, " +

    " TreaterInformation AS t, " +

    " Batch AS b, " +

    " Customer AS cu, " +

    " Recipe AS r, " +

    " Company AS co, " +

    " Crop AS cr, " +

    " SeedVariety AS sv " +

    "WHERE b.DTStart = (SELECT MAX(DTStart) AS DTStart FROM Batch WHERE TreaterID = b.TreaterID)" +

    " AND b.TreaterID = @TreaterID " +

    " AND ch.ChemicalID = cb.ChemicalID " +

    " AND cb.TreaterID = b.TreaterID " +

    " AND cb.BatchID = b.BatchID " +

    " AND t.TreaterID = b.TreaterID " +

    " AND ch.FlexTank = 'true' " +

    " AND b.CustID = cu.CloudID " +

    " AND r.RecipeID = b.RecipeID " +

    " AND sv.VarietyID = b.SeedVarietyID " +

    " AND co.CompanyID = sv.CompanyID " +

    " AND cr.CropID = sv.CropID " +

    " AND b.RecipeVersion = r.Version " +

    "ORDER BY DateStarted, " +

    " TimeStarted, " +

    " ch.FlexTank, " +

    " ch.ChemicalName ";

    Thank you Gail and everyone!

  • Step 1: shoot your developers (self defence) :hehe: It's near impossible to do effective indexing when all queries are select *

    Step 2: Show the reporting guy how to do joins in the from clause not the where, see if he will change his queries

    Step 3: Read up on SQL injection and make sure that steps are being taken against that.

    Step 4: Start with indexes on the columns in the where clause (considering everything covered in that article) and see if SQL uses them (execution plan)

    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
  • *facepalm*

    Rich, introduce your Report Writer to the CREATE PROC command. He may love you forever... the rest of your developers... well... once you've taken Gail's advice, I live in the desert. We've got PLENTY of sand to hide the bodies in.

    Just pack 'em in dry ice, please... the odor's a giveaway.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • 1. I'll do them one better and leave them in the frozen cold one morning while they are all on their way into the office! *evil grin*

    2. I actually did convert one of this reports with JOINS instead of the old school coding, but then one of my instructors told me that it wasn't going to make a difference (no penalty for old school coding), and thus - claimed there would be no performance hit, but my testing (even on my lil' WinXP PC) is showing me something different with some query stress test tools. I'll try to give this another go.

    3. I brought up the fact that Dynamic SQL was a big no-no, but again - 6 against 1, and they promised me that the code was more parameterized than not. Me thinks not, but. . .

    4. I just showed my reporting guy you comments, and since 3 out of 4 have been said by me for the past 2 months - I have won him over (they are not keen one me shooting them. I told them they were lucky! *evil grin*).

    I'll let you know how this works out before week's end, and thank you for the direction to take here!

    🙂

  • Craig!

    He was peeking over my shoulder as I was reading your comment, and was like "What's CREATE PROC, some sort of tool". After I laughed I shared with him how we might be able to simply make his report into a more parameterized procedure for input values, instead of 12 different reports, but these are being generated from a webpage, so I am not certain how that is going to work (newbie).

    As for holes in the dessert - these boys are pretty fat. Dig a few deep ones for me, and I'll see if I can get them all in one box!

    😉

  • Rich Yarger (3/7/2011)


    2. I actually did convert one of this reports with JOINS instead of the old school coding, but then one of my instructors told me that it wasn't going to make a difference (no penalty for old school coding), and thus - claimed there would be no performance hit, but my testing (even on my lil' WinXP PC) is showing me something different with some query stress test tools. I'll try to give this another go.

    Go to the query plan, not the code, first, once the logic's in place. Yes, there are ways to clean up logic, but the execution plan will show you which logic to concentrate on to fix.

    3. I brought up the fact that Dynamic SQL was a big no-no, but again - 6 against 1, and they promised me that the code was more parameterized than not. Me thinks not, but. . .

    Easy way to check... which your sample doesn't do. Do they run SP_EXECUTESQL with parameters? If not, guess whaaaaattt.... 😉


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Rich Yarger (3/7/2011)


    2. I actually did convert one of this reports with JOINS instead of the old school coding, but then one of my instructors told me that it wasn't going to make a difference (no penalty for old school coding), and thus - claimed there would be no performance hit, but my testing (even on my lil' WinXP PC) is showing me something different with some query stress test tools. I'll try to give this another go.

    It's not faster. Not at all.

    It's easier to read

    It's harder to make a mistake and leave a join out

    It supports outer joins (the *= syntax is only usable in compat mode 80)

    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
  • After some reformatting I figured the both queries used in the UNION statement are pretty much identical except for the join to BatchKeg and Keg and the ch.FlexTank = 'true'/'false' flag.

    So, instead of calling that "monster-join" twice, you might be better of storing the results from the second query (expanded to include ch.FlexTank = 'false' ) in a temp table or create an indexed view (depending on the insert/update frequency of each source table) and use it as the source for the UNION ALL query.

    As a side note: did you notice I referred to UNION ALL instead of UNION?

    Since both query definitely will return different values (based on the different values for ch.FlexTank) you don't have to check for duplicates.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM (3/7/2011)


    After some reformatting I figured the both queries used in the UNION statement are pretty much identical except for the join to BatchKeg and Keg and the ch.FlexTank = 'true'/'false' flag.

    So, instead of calling that "monster-join" twice, you might be better of storing the results from the second query (expanded to include ch.FlexTank = 'false' ) in a temp table or create an indexed view (depending on the insert/update frequency of each source table) and use it as the source for the UNION ALL query.

    As a side note: did you notice I referred to UNION ALL instead of UNION?

    Since both query definitely will return different values (based on the different values for ch.FlexTank) you don't have to check for duplicates.

    Another option I've use with worked well was to do a left join on both tables and then check the true/false useing isnull or a case for a more complex logic.

    At this point, another join's not going to hurt anywhere close to redoing the whole list of joins. Especially true if those ar lookup tables with very little data in them.

  • Ninja's_RGR'us (3/7/2011)


    ...

    Another option I've use with worked well was to do a left join on both tables and then check the true/false useing isnull or a case for a more complex logic.

    At this point, another join's not going to hurt anywhere close to redoing the whole list of joins. Especially true if those ar lookup tables with very little data in them.

    Yep. Sounds like another option to look into. Good point.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • You gals and guys are awesome! Thank you so much for the great mentoring and advice.

    Here is what I have done with one of the other reports. I am going to try a few other things, but this is my starting point to sell the idea, and then I will start with Gail's advice on creating indexes for the columns in the WHERE predicate...

    USE 'Database Name Here'

    GO

    SELECT

    TI.RetailerName

    ,B.TreaterID

    ,CONVERT(varchar(10),B.DTStart,101) AS DStart

    ,CONVERT(varchar(8),B.DTStart,8) AS TStart

    ,CONVERT(varchar(10),B.DTEnd,101) AS DEnd

    ,CONVERT(varchar(8),B.DTEnd,8) AS TEnd

    ,CU.FirstName + ' ' + CU.LastName AS CustomerName

    ,R.RecipeName

    ,B.CustBatch

    ,B.BatchID

    ,B.Notes

    ,CO.CompanyName + ': ' + CR.CropName + ' - ' + SV.VarietyName AS SeedVariety

    ,B.SeedSize

    ,B.SeedUnits

    ,B.Temperature

    ,B.WindSpeed

    ,B.WheelFlowRate

    ,B.WheelFlowUnits

    ,B.WheelTotal

    ,CH.ChemicalName

    ,CH.UnitOfMeasure

    ,K.ChemicalBatch

    ,BK.KegUsage

    ,CB.ActualFlowRate

    FROM BatchKeg AS BK

    INNER JOIN Keg AS K

    ON K.KegID = BK.KegID

    INNER JOIN Batch AS B

    ON BK.BatchID = B.BatchID

    AND BK.TreaterID = B.TreaterID

    INNER Join Chemical AS CH

    ON CH.ChemicalID = K.ChemicalID

    INNER JOIN ChemBatch AS CB

    ON CB.BatchID = B.BatchID

    AND CB.TreaterID = B.TreaterID

    AND CB.ChemicalID = CH.ChemicalID

    INNER JOIN TreaterInformation AS TI

    ON TI.TreaterID = B.TreaterID

    INNER JOIN Customer AS CU

    ON B.CustID = CU.CloudID

    INNER JOIN Recipe As R

    ON R.RecipeID = B.RecipeID

    INNER JOIN SeedVariety AS SV

    ON SV.VarietyID = B.SeedVarietyID

    INNER JOIN Company As CO

    ON CO.CompanyID = SV.CompanyID

    INNER JOIN Crop As CR

    ON CR.CropID = SV.CropID

    WHERE B.DTStart = (SELECT Max(DTStart) AS DTStart FROM Batch WHERE TreaterID = B.TreaterID)

    ORDER BY B.TreaterID, B.DTStart

    I still need to clean the readability of the code up a bit, but thank you all so much for the advice on how to move forward. I'll let you know if I am successful in getting this pushed through before release.

    Also - Craig - guess what? 😉

  • LOL, he loves you forever? 😉 Or am I expecting a few bodies?

    Btw, this: (SELECT Max(DTStart) AS DTStart FROM Batch WHERE TreaterID = B.TreaterID)

    I would recommend dropping to a #TMP table, indexing it, and joining it in, using this:

    SELECT TreaterID, MAX(DTStart) AS MaxStart INTO #TMP FROM Batch

    CREATE CLUSTERED INDEX idx_walla_walla_bing_bang ON #tmp (TreaterID, MaxStart)

    As Jeff Moden usually comments, "Divide and Conquer" for the win. At the least, when you start exploring the Execution Plan for why you're in pain, it's one less moving part to worry about... That's a lotta joins.

    EDIT: Silly smiley got confused on my colon and paren usage.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Who? My report writer? LOL! Nah - he thinks I'm a pain in the ace.

    I wish I had better news on that front - still pushing for it, but actually - this is pretty good news too in an of itself. My manager determined that - in fact - they were using...

    DYNAMIC SQL!

    So - guess what? They are going to redo their factories with sp_executesql. Now if I could only get them to do away with SELECT * from their factories, I wouldn't have to shoot them!

    😉

Viewing 15 posts - 1 through 15 (of 17 total)

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