Announcements
185
Hi all,
I have this script to fill scrolling window
fill window window1 table RM_OPEN;
i have range on RM_OPEN which has records less than 20 records
it is taking more than 10 mins to fill that scrolling window.
*This post is locked for comments
You're welcome mate! Glad you got your problem fixed.
I have created Index on that table RM20201 ,now everything is working fine
Thank you all for your support.
So, you have a massive table scan going on and it seems you are missing an index. Given that I am not seeing that on my query execution plan, it would seem that your RM table somehow had an index deleted by accident. Compare the indexes on that table to the table definition in Dexterity to make sure all the indexes are present. Also, what version of Microsoft Dynamics GP are you running?
Hi Mariano,
Here is my execution plan of the query
As suggested by David and you it is SQL Issue
This is now a SQL optimization issue and not a Dexterity issue.
David
Do you have any triggers on this table? How many records are in either table? This query runs fast enough on my computer, but then again I don't have as much data.
I ran an execution plan on that query and it uses indexes all the way, perhaps you need to fine tune your indexes?
I have found the problem, one of my select query is taking too long
I am trying to replicate sales apply document window, I am trying to fill the scrolling window with documents of current customer.
Here is the Range where script
range clear table RM_OPEN;
range table RM_OPEN where CH_LEFTPAREN + physicalname('Customer Number' of table RM_OPEN)
+ CH_SPACE + "=" + CH_SPACE + SQL_FormatStrings('IRF Customer Number' of window IRF_RM_Apply_Document)
+ CH_SPACE + "AND" + CH_SPACE + physicalname('RM Document Type-All' of table RM_OPEN)
+ CH_SPACE + "NOT IN" + CH_SPACE + CH_LEFTPAREN + "7,8,9" + CH_RIGHTPAREN
+ CH_SPACE + "AND" + CH_SPACE + physicalname('Current Trx Amount' of table RM_OPEN)
+ CH_SPACE + ">0" + CH_RIGHTPAREN
+ CH_SPACE + "OR" + CH_SPACE + physicalname('Document Number' of table RM_OPEN)
+ CH_SPACE + "IN" + CH_LEFTPAREN + CH_SPACE + "SELECT" + CH_SPACE + physicalname('Apply To Document Number' of table RM_Applied_OPEN)
+ CH_SPACE + "FROM" + CH_SPACE + 'Intercompany ID' of globals + CH_PERIOD + SQL_DEFAULT_OWNER + CH_PERIOD + physicalname(table RM_Applied_OPEN)
+ CH_SPACE + "WHERE" + CH_SPACE + physicalname('Apply From Document Number' of table RM_Applied_OPEN)
+ CH_SPACE + "=" + SQL_FormatStrings('IRF Document Number' of window IRF_RM_Apply_Document) + ")";
Equivalent SQL query which i got from Script profiler is
/* Date: 11/21/2017 Time: 3:30:31
stmt(164104896):*/
SELECT TOP 25 CUSTNMBR,CPRCSTNM,DOCNUMBR,CHEKNMBR,BACHNUMB,BCHSOURC,TRXSORCE,RMDTYPAL,CSHRCTYP,CBKIDCRD,CBKIDCSH,CBKIDCHK,DUEDATE,DOCDATE,POSTDATE,PSTUSRID,GLPOSTDT,LSTEDTDT,LSTUSRED,ORTRXAMT,CURTRXAM,SLSAMNT,COSTAMNT,FRTAMNT,MISCAMNT,TAXAMNT,COMDLRAM,CASHAMNT,DISTKNAM,DISAVAMT,DISAVTKN,DISCRTND,DISCDATE,DSCDLRAM,DSCPCTAM,WROFAMNT,TRXDSCRN,CSPORNBR,SLPRSNID,SLSTERCD,DINVPDOF,PPSAMDED,GSTDSAMT,DELETE1,AGNGBUKT,VOIDSTTS,VOIDDATE,TAXSCHID,CURNCYID,PYMTRMID,SHIPMTHD,TRDISAMT,SLSCHDID,FRTSCHID,MSCSCHID,NOTEINDX,Tax_Date,APLYWITH,SALEDATE,CORRCTN,SIMPLIFD,Electronic,ECTRX,BKTSLSAM,BKTFRTAM,BKTMSCAM,BackoutTradeDisc,Factoring,DIRECTDEBIT,ADRSCODE,EFTFLAG,DEX_ROW_TS,DEX_ROW_ID FROM IRFAN.dbo.RM20101 WHERE ((CUSTNMBR = '1000079' AND RMDTYPAL NOT IN (7,8,9) AND CURTRXAM >0) OR DOCNUMBR IN( SELECT APTODCNM FROM IRFAN.dbo.RM20201 WHERE APFRDCNM ='193642-857534')) ORDER BY CUSTNMBR ASC ,RMDTYPAL ASC ,DOCNUMBR ASC
/* Date: 11/21/2017 Time: 3:37:18
stmt(164101280):*/
{ CALL DYNAMICS.dbo.zDP_SY01402SS_1 ( 'sa', 3 ) }
it is taking almost 7 mins to execute that query
i have executed the query in SQL Management studio , it takes the same time.
Ok, now, do you have any fill scripts on the scrolling window? What operation is taking too long? This would come from your script profiler report.
Hi Mariano,
I made a warning it shows only 4 records still takes long time to fill that window.
Irfan,
Whenever you are experiencing performance issues, you can use the Script Profiler to identify the problem as previously suggested by David. In this particular case, you will want to check the scrolling window to see if you have any associated fill script.
I would also suggest you confirm if effectively the range contains 20 records as you mentioned. You can do this by adding a simple countrecords() to check the amount of records in the range:
local long n_recs; . . range start table RM_OPEN by number <someKey>; . . . range end table RM_OPEN by number <someKey>; n_recs = countrecords(table RM_OPEN);
If the range is set correctly, then you will be able to verify that effectively you are getting back the 20 records.
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
