Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Creating SQL table ind...
Microsoft Dynamics GP (Archived)
Suggested answer

Creating SQL table indexes to improve performance

(0) ShareShare
ReportReport
Posted on by mjose261 16
Hello, what are the Pros and Cons to adding them (for GP 18.5)?
Anyone experience any issues?
 
fyi we just upgraded GP so another upgrade is unlikely for years
 
 
CREATE INDEX [idx_iic_GL30000_001] ON [IIC01].[dbo].[GL30000] ([ACTINDX]) INCLUDE ([JRNENTRY], [SOURCDOC], [REFRENCE], [DSCRIPTN], [TRXDATE], [ORGNATYP], [SERIES], [DEBITAMT], [CRDTAMNT])
CREATE INDEX [idx_iic_IV00200_001] ON [IIC01].[dbo].[IV00200] ([SERLNMBR], [SERLNSLD])
CREATE INDEX [idx_iic_SOP10104_001] ON [IIC01].[dbo].[SOP10104] ([SOPNUMBE], [DELETE1],[PRCHLDID]) INCLUDE ([USERID], [HOLDDATE], [TIME1])
CREATE INDEX [idx_iic_SOP10201_001] ON [IIC01].[dbo].[SOP10201] ([SOPNUMBE]) INCLUDE ([LNITMSEQ], [SERLTNUM], [DATERECD], [BIN])
CREATE INDEX [idx_iic_SOP10201_002] ON [IIC01].[dbo].[SOP10201] ([SOPTYPE]) INCLUDE ([SOPNUMBE], [LNITMSEQ], [SERLTNUM])
CREATE INDEX [idx_iic_SOP30200_001] ON [IIC01].[dbo].[SOP30200] ([DOCDATE]) INCLUDE ([SOPTYPE], [SOPNUMBE], [DOCID], [QUOEXPDA], [ReqShipDate], [BCHSOURC], [BACHNUMB], [CUSTNMBR], [CUSTNAME], [CSTPONBR], [PROSPECT], [PRSTADCD], [DOCAMNT], [SLPRSNID], [VOIDSTTS], [ALLOCABY], [CURNCYID], [SOPSTATUS], [SHIPCOMPLETE], [WorkflowPriorityCreditLm], [WorkflowPriorityQuote])
CREATE INDEX [idx_iic_SOP60100_001] ON [IIC01].[dbo].[SOP60100] ([SOPNUMBE], [LNITMSEQ]) INCLUDE ([SOPTYPE], [GLPOSTDT])
 
CREATE INDEX [idx_iic_Trigger_001] ON [ManagementReporter].[Scheduling].[Trigger] ([StartBoundary]) INCLUDE ([Id], [Type], [UnitOfMeasure], [IsEnabled], [EndBoundary], [RecurrenceLimit], [Interval], [DaysOfWeek], [DayOfMonth], [WeekOfMonth], [MonthOfYear], [OnlyWeekday], [TimeZoneId], [RunImmediately], [Version])
Categories:
Development / Customization / SDK
Management Reporter
Reporting and BI
  • Community member Profile Picture
    Community member 6 on at
    Creating SQL table indexes to improve performance
    Where is the registration of that expense located?
  • Suggested answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,781 Super User on at
    Creating SQL table indexes to improve performance
    Without spending the time analysing the columns you have the indexes on....
     
    Adding indexes is allowed for GP tables, but they usually would need to be reapplied after an upgrade.
     
    Pros: Correct crafted additional indexes can improve performance when accessing the data using where clauses containing columns not in the existing indexes.
     
    Cons: The additional indexes will add a small overhead when adding/updating records and will make the database itself larger (depending on the columns in the index and the number of records).
     
    Usually performance is more important than space.
     
    Regards
     
    David 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 284,876 Super User

#2
Ludwig Reinhard Profile Picture

Ludwig Reinhard Microsoft Employee

#3
Martin Dráb Profile Picture

Martin Dráb 225,425 Super User

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans