Modifying and/or deleting indexes on various tables
Posted: Fri Jan 24, 2020 1:10 pm
Getting back to that squeaky wheel with slow Jiwa.
I've been doing some monitoring over the past few weeks on their SQL Server. One of the diagnostic tools I'm using has reported some tables as heavily indexed. This has a few issues particularly with INSERTs and UPDATEs. Without going into the nitty gritty of negative performance with too many indexes, as no doubt you're just as knowledgeable (!) about their pros and cons, I just wanted to touch base on the issue via this thread.
One of the slow areas of Jiwa is saving sales orders. Coincidentally, the tool I'm using reports SO_Main as being over-indexed.
I can see that this table has 16 indexes, yet the JiwaDemo database has 14. I think I'll drop those indexes seeing as they're not standard. They are non clustered on SO_Main.CreditNote. Another index on SO_Main.WholesaleInvoice.
But the other indexes...what would your feeling be if I dropped a few indexes and maybe just combined them into a single, wider index with a few columns?
Just as an example only, dropping the single indexes for InvoiceNo, OrderNo, SOReference and just having one index thus:
Another table: GL_Ledger.
You have single indexes on each 6 segments. Seeing as this client only goes to 3 segments do you have any reservations about dropping the indexes on Seg4, Seg5, Seg6?
What about dropping the indexes on Seg1, Seg2, Seg3 and just combining into a wider index with all 3 segments to:
or
Are those segments really used extensively (or at all) on your code to perform queries?
Have you done much research on performance gains over large databases with big transactional load (such as a file watcher with 500 files to import as sales orders, with a few other users already in the sales order module doing manual entry and saving of orders) using various indexes?
Cheers
I've been doing some monitoring over the past few weeks on their SQL Server. One of the diagnostic tools I'm using has reported some tables as heavily indexed. This has a few issues particularly with INSERTs and UPDATEs. Without going into the nitty gritty of negative performance with too many indexes, as no doubt you're just as knowledgeable (!) about their pros and cons, I just wanted to touch base on the issue via this thread.
One of the slow areas of Jiwa is saving sales orders. Coincidentally, the tool I'm using reports SO_Main as being over-indexed.
I can see that this table has 16 indexes, yet the JiwaDemo database has 14. I think I'll drop those indexes seeing as they're not standard. They are non clustered on SO_Main.CreditNote. Another index on SO_Main.WholesaleInvoice.
But the other indexes...what would your feeling be if I dropped a few indexes and maybe just combined them into a single, wider index with a few columns?
Just as an example only, dropping the single indexes for InvoiceNo, OrderNo, SOReference and just having one index thus:
- Code: Select all
CREATE NONCLUSTERED INDEX [IX_MyNewIndexName] ON dbo.SO_Main
(InvoiceID) INCLUDE (InvoiceNo, OrderNo, SOReference)
Another table: GL_Ledger.
You have single indexes on each 6 segments. Seeing as this client only goes to 3 segments do you have any reservations about dropping the indexes on Seg4, Seg5, Seg6?
What about dropping the indexes on Seg1, Seg2, Seg3 and just combining into a wider index with all 3 segments to:
- Code: Select all
CREATE NONCLUSTERED INDEX [IX_MyIndexName] ON dbo.GL_Ledger (GLLedgerID) INCLUDE (Seg1, Seg2, Seg3)
or
- Code: Select all
CREATE NONCLUSTERED INDEX [IX_MyIndexName] ON dbo.GL_Ledger (GLLedgerID, Seg1, Seg2,Seg3)
Are those segments really used extensively (or at all) on your code to perform queries?
Have you done much research on performance gains over large databases with big transactional load (such as a file watcher with 500 files to import as sales orders, with a few other users already in the sales order module doing manual entry and saving of orders) using various indexes?
Cheers