Table index affects INSERT?

Alex_Chow

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?

Marije_Brummel

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.

cnicola

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.

DenSter

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.

cnicola

Mark Brummel wrote:

Step 0. Goto Hyneks class at directions.

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

Alex_Chow

cnicola wrote:

Mark Brummel wrote:

Step 0. Goto Hyneks class at directions.

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

It's not a 1 hour class. If it was, I'd be all over it like fly on poo.

nunomaia

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.

cnicola

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.

Alex_Chow

Gotcha. Thank you all for your valuable input.

Will disable some MaintainSIFTIndex first before disabling MaintainSQLIndex.

DenSter

You also want to look at individual SIFT levels. Some of the SIFT actually has a good purpose