Dynamics Community Forum Thread Details

Smart Lists Running slow after Year End Close

User reports that smart lists are running slow after year close. I discovered that an index that was somehow dropped in the process. I recreated it but that did not fix the isssue. The smartlist starts off fast but the numbers refresh slower as time goes on.

This keeps getting invoked.

SELECT TOP 25 HSTYEAR, JRNENTRY, RCTRXSEQ, SOURCDOC, REFRENCE, DSCRIPTN, TRXDATE, ACTINDX, TRXSORCE, POLLDTRX, LASTUSER, LSTDTEDT, USWHPSTD, ORGNTSRC, ORGNATYP, QKOFSET, SERIES, ORTRXTYP, ORCTRNUM, ORMSTRID, ORMSTRNM, ORDOCNUM, ORPSTDDT, ORTRXSRC, OrigDTASeries, OrigSeqNum, SEQNUMBR, DTA_GL_Status, DTA_Index, CURNCYID, CURRNIDX, RATETPID, EXGTBLID, XCHGRATE, EXCHDATE, TIME1, RTCLCMTD, NOTEINDX, ICTRX, ORCOMID, ORIGINJE, PERIODID, DEBITAMT, CRDTAMNT, ORDBTAMT, ORCRDAMT, DOCDATE, PSTGNMBR, PPSGNMBR, DENXRATE, MCTRXSTT, CorrespondingUnit, VOIDED, Back_Out_JE, Back_Out_JE_Year, Correcting_JE, Correcting_JE_Year, Original_JE, Original_JE_Seq_Num, Original_JE_Year, Ledger_ID, Adjustment_Transaction, APRVLUSERID, APPRVLDT, DEX_ROW_TS, DEX_ROW_ID FROM .GL30000 WHERE HSTYEAR = @HSTYEAR_RS AND TRXDATE BETWEEN @TRXDATE_RS AND @TRXDATE_RE AND ACTINDX BETWEEN @ACTINDX_RS AND @ACTINDX_RE AND ( HSTYEAR = @HSTYEAR AND TRXDATE = @TRXDATE AND ACTINDX = @ACTINDX AND DEX_ROW_ID > @DEX_ROW_ID OR HSTYEAR = @HSTYEAR AND TRXDATE = @TRXDATE AND ACTINDX > @ACTINDX OR HSTYEAR = @HSTYEAR AND TRXDATE > @TRXDATE OR HSTYEAR > @HSTYEAR ) ORDER BY HSTYEAR ASC, TRXDATE ASC, ACTINDX ASC, DEX_ROW_ID ASC

Missing Index.

CREATE NONCLUSTERED INDEX [ix_SmartList] ON [dbo].[GL30000]
(
[TRXDATE] ASC
)
INCLUDE ( [HSTYEAR],
[JRNENTRY],
[RCTRXSEQ],
[REFRENCE],
[SOURCDOC],
[DSCRIPTN],
[ACTINDX],
[SEQNUMBR],
[PERIODID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Has anyone seen this issue?

Categories:

Microsoft Dynamics GP

Quick Links

Leaderboard