mibuso.com
Home› SQL Performance
Options

Table index affects INSERT?

Alex_ChowAlex_Chow Member Posts: 5,063
edited 2007-10-09 in SQL Performance
I know there are a lot of aspects with SQL tuning. This question is more specific though...

Does the number of indexes or using the wrong index affect the performance on INSERT statements on SQL Server?
Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work

Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book

Answers

  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Yes it does, but not half as much as the number of SIFT updates.

    Have a look at the SQL Server management studio and open the triggers of the G/L Entry table.

    Step 1. Eliminate SIFT
    Step 2. Disable duplicate index.

    Step 0. Goto Hyneks class at directions. :mrgreen:
    Marije Brummel | Freelance Business Central Specialist
  • Options
    cnicolacnicola Member Posts: 181
    Alex Chow wrote:
    I know there are a lot of aspects with SQL tuning. This question is more specific though...

    Does the number of indexes or using the wrong index affect the performance on INSERT statements on SQL Server?

    What do you mean by using wrong index on an INSERT?

    And why are you thinking about that? Someone told me that normally in a db you have 10 reads for each write so in theory adding keys (and SIFTs) to speed up reads is most of the time worth the extra time on writes.
    Apathy is on the rise but nobody seems to care.
  • Options
    DenSterDenSter Member Posts: 8,304
    edited 2007-10-09
    Don't modify any of the trigger code though, that manages SIFT values, and you don't want to mess around with that. The SIFT values are maintained by those table triggers. For each INSERT, MODIFY and DELETE it runs that code to maintain the values in the SIFT tables. The more SIFT keys you have, the more values it has to maintain, the longer it takes to insert a record.

    Similarly, when a record is inserted into a SQL Server table, it has to update all indexes that are associated with the table. Logically then, the more indexes on the table, the longer it takes to insert a record. You can't select an index for an insert.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    cnicolacnicola Member Posts: 181
    Mark Brummel wrote:
    Step 0. Goto Hyneks class at directions. :mrgreen:

    So Mark are you saying that an 1 hour class will give us a basic foundation on Navision - SQL issues? :D
    Apathy is on the rise but nobody seems to care.
  • Options
    Alex_ChowAlex_Chow Member Posts: 5,063
    edited 2007-10-09
    cnicola wrote:
    Mark Brummel wrote:
    Step 0. Goto Hyneks class at directions. :mrgreen:

    So Mark are you saying that an 1 hour class will give us a basic foundation on Navision - SQL issues? :D

    It's not a 1 hour class. If it was, I'd be all over it like fly on poo.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • Options
    nunomaianunomaia Member Posts: 1,153
    Every time you insert a new record SQL Server has to write records and for each index save fields in index. So it affect performance because has to be written more information.

    But has Mark Brummel said SIFT can be a nightmare. There are some several updates in those tables so it much worse than a index.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • Options
    cnicolacnicola Member Posts: 181
    Alex Chow wrote:
    It's not a 1 hour class. If it was, I'd be all over it like fly on poo.

    Very powerful image Alex :sick:
    And Hynek has a session for 1 hour at Directions itself. I guess Mark may have meant the 2 days class.
    Apathy is on the rise but nobody seems to care.
  • Options
    Alex_ChowAlex_Chow Member Posts: 5,063
    Gotcha. Thank you all for your valuable input.

    Will disable some MaintainSIFTIndex first before disabling MaintainSQLIndex.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • Options
    DenSterDenSter Member Posts: 8,304
    You also want to look at individual SIFT levels. Some of the SIFT actually has a good purpose :)
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
Sign In or Register to comment.