The query that take over an hour.. I know we even added an index to GL40200. We can add one to GL20000, but since that table wll have about 10 million records in it at the end o the year, I am concerned about the impact of posting to that table or adding a nonstandard index. We post thousands of transaction a day and the GL40200 has ten years o data. The ChangeTable function itself can be slow returning values when I query it by itself. I noticed the the majority of what is being tracked in Change Tracking is unposted, and we never report on signposted items.
Can we turn off Change Tracking for Gl10000, and GL00001? Will that help performance?
Can we safely add and index to GL20000 and not have it impact posting to the table? In the past when I attempted to dd indexes to PM302000, it caused batches to get stuck and not post.
C
Select count(distinct a.GLTransactionID) from ( SELECT seg1.ACTNUMBR_1, seg1def.DEX_ROW_ID as ACTNUMBRID_1, seg2.ACTNUMBR_2, seg2def.DEX_ROW_ID as ACTNUMBRID_2, seg3.ACTNUMBR_3, seg3def.DEX_ROW_ID as ACTNUMBRID_3, seg1.ACTDESCR, GL102.ACCATDSC as Category, GL102.DEX_ROW_ID as CategoryID,
GL105.ACTNUMST, GL105.ACTINDX, GL0.PERIODID, GL0.TRXDATE, GL0.SOURCDOC, GL0.REFRENCE,
seg1.ACCATNUM, GL0.TRXSORCE, GL0.XCHGRATE, GL0.ORTRXSRC, GL0.SERIES, GL0.VOIDED,
GL0.Adjustment_Transaction, GL401.Ledger_Name, GL0.JRNENTRY, GL0.CURRNIDX, MC4.FUNCRIDX, GL0.DEX_ROW_ID as GLTransactionID, GL0.DSCRIPTN,
GL0.ORDOCNUM, GL0.ORMSTRID, GL0.ORMSTRNM, GL0.ORTRXTYP, GL0.DEBITAMT as DebitAmount, GL0.CRDTAMNT as CreditAmount,
GL0.ORDBTAMT as OrigDebitAmount, GL0.ORCRDAMT as OrigCreditAmount,GL0.SEQNUMBR, (rtrim(cast(GL0.DEX_ROW_ID as nvarchar(30)))) as keyString, SY401.BBF, OPENYEAR as YEAR1, 'GL20000' KeyTable FROM GL20000 GL0
join GL00105 GL105 on GL0.ACTINDX = GL105.ACTINDX
join GL40001 GL401 on GL401.Ledger_ID = GL0.Ledger_ID
cross join MC40000 MC4 JOIN GL00100 seg1 on seg1.ACTINDX = GL105.ACTINDX
left join GL40200 seg1def on seg1def.SGMNTID = seg1.ACTNUMBR_1 and seg1def.SGMTNUMB = 1 JOIN GL00100 seg2 on seg2.ACTINDX = GL105.ACTINDX
left join GL40200 seg2def on seg2def.SGMNTID = seg2.ACTNUMBR_2 and seg2def.SGMTNUMB = 2 JOIN GL00100 seg3 on seg3.ACTINDX = GL105.ACTINDX
left join GL40200 seg3def on seg3def.SGMNTID = seg3.ACTNUMBR_3 and seg3def.SGMTNUMB = 3 left join GL00102 GL102 on GL102.ACCATNUM = seg1.ACCATNUM join
(select 0 BBF, YEAR1, PERIODID, PERIODDT, PERDENDT from SY40100 where FORIGIN = 1 and PERIODID = 0
union
select 1 BBF, YEAR1, PERIODID, PERIODDT, PERDENDT from SY40100 where FORIGIN = 1 and PERIODID != 0) SY401
on GL0.OPENYEAR = SY401.YEAR1
and ((SY401.BBF = 0 and GL0.TRXDATE = SY401.PERIODDT and GL0.SOURCDOC in ('BBF', 'P/L')) OR (SY401.BBF = 1 and GL0.TRXDATE <= SY401.PERDENDT and GL0.TRXDATE >= SY401.PERIODDT)) INNER JOIN CHANGETABLE(CHANGES GL20000, 7149664) GL0_CT ON GL0.DEX_ROW_ID = GL0_CT.DEX_ROW_ID AND GL0_CT.SYS_CHANGE_VERSION <= 7224285 ) a
