April 26, 2012 at 7:28 am
Hi - We have a user complaining about a specific action, which I have managed to capture, please see he code below:
However when I run this on the SQL backend it is very quick and the execution plan doesn't seem to be throwing up anything immediately wrong. I have been doing a bit of research into API cursors, could this be the cause of the problem inside the application, if so how can I prove it? Its Microsoft Dynamics AX that we use, and I don't get involved in the development of it really.
Any ideas?
Thanks
declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int
set @p5=28688
declare @p6 int
set @p6=8193
declare @p7 int
set @p7=2
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 nvarchar(5),@P2 nvarchar(11),@P3 nvarchar(5),@P4 nvarchar(5),@P5 int,@P6 int,@P7 int,@P8 int,@P9 int',N'SELECT
A.JOURNALNUM,A.LINENUM,A.ACCOUNTTYPE,A.ACCOUNTNUM,A.COMPANY,A.TXT,A.AMOUNTCURDEBIT,A.CURRENCYCODE,A.EXCHRATE,A.TAXGROUP,A.QTY,A.DIMENSION,A.DIMENSION2_,A.DIMENSION3_,A.BANKNEGINSTRECIPIENTNAME,A.ENTERPRISENUMBER,A.PRICE,A.INTERCODIMENSION,A.INTERCODIMENSION2_,A.INTERCODIMENSION3_,A.SETTLEVOUCHER,A.POSTINGPROFILE,A.VOUCHER,A.AMOUNTCURCREDIT,A.PAYMENTSTATUS,A.CASHDISCAMOUNT,A.VENDTRANSID,A.CUSTTRANSID,A.REMAINAMOUNT,A.TAXCODE,A.OFFSETACCOUNT,A.BANKDEPOSITVOUCHER,A.PAYMREFERENCE,A.BANKDEPOSITNUM,A.PAYMSPEC,A.PAYMMODE,A.MARKEDINVOICE,A.OFFSETACCOUNTTYPE,A.BANKTRANSTYPE,A.OFFSETCOMPANY,A.OFFSETTXT,A.PAYMENTNOTES,A.MARKEDINVOICERECID,A.FOREIGNVOUCHER,A.FOREIGNCOMPANY,A.TRANSFERRED,A.CANCEL,A.NOEDIT,A.INVISIBLE,A.TRANSDATE,A.TRANSACTIONTYPE,A.DOCUMENTDATE,A.DOCUMENTNUM,A.APPROVED,A.APPROVEDBY,A.PAYMID,A.FREQVALUE,A.FREQCODE,A.DUE,A.DATECASHDISC,A.TRANSFERREDBY,A.LOADINGDATE,A.FILECREATED,A.PAYMENTACCOUNT,A.INVOICE,A.TRANSFERREDTO,A.TRANSFER,A.LASTTRANSFERRED,A.TRANSFERREDON,A.PURCHLEDGERPOSTING,A.BANKCHEQUENUM,A.PAYMENT,A.CASHDISCCODE,A.EXCHRATESECOND,A.TRIANGULATION,A.TAXITEMGROUP,A.VATNUMJOURNAL,A.LISTCODE,A.FURTHERPOSTINGTYPE,A.PURCHIDRANGE,A.POOLRECID,A.PREPAYMENT,A.IMPORTDATE,A.BANKCENTRALBANKPURPOSECODE,A.REASONREFRECID,A.BANKCENTRALBANKPURPOSETEXT,A.CUSTVENDBANKACCOUNTID,A.PAYMENTSEQUENCENUM,A.ERRORCODEPAYMENT,A.FURTHERPOSTINGRECID,A.TAXDIRECTIONCONTROL,A.CUSTVENDNEGINSTPROTESTREASON,A.BANKREMITTANCETYPE,A.BANKREMITTANCEFILEID,A.BANKACCOUNTID,A.BANKPROMISSORYNOTENUM,A.BANKBILLOFEXCHANGENUM,A.NEGINSTID,A.TAXWITHHOLDGROUP,A.REVERSEENTRY,A.REVERSEDATE,A.REVRECID,A.BANKRECONCILEACCOUNTATPOST,A.BANKCHEQUEDEPOSITTRANSREFRECID,A.MARKEDINVOICECOMPANY,A.RELEASEDATE,A.RELEASEDATETZID,A.MARQUERYCODE,A.MARQUERYDATE,A.MARQUERYBY,A.MARCISDEFCALC,A.MARCISMATERIALS,A.MARCISLABOUR,A.MARREFPROJTRANSID,A.MARREVERSALTRANS,A.MARTOUCHED,A.MARONBASEID,A.MARPURCHIMSASSIGNEDTO,A.MARPURCHIMQUERYDUEDATE,A.MARAUTOUPDATED,A.MARPURCHIMSURGENT,A.MARSUPPLIER,A.MARORDER,A.MARINVOICE,A.MARINVOICEDATE,A.MARINVENTSITEID,A.MARBLANKETSALESID,A.MARREFRECID,A.MARORIGINALCOMPANYID,A.MARPURCHINVDESPATCHNOTENBR,A.MAROBIMAGEVALUE,A.RECVERSION,A.RECID,B.REFRECID,B.ASSETID,B.BOOKID,B.TRANSTYPE,B.COMPANY,B.BUDGETMODEL,B.BUDGETPOSTINGSTATUS,B.CONSUMPTIONQUANTITY,B.DEPRECIATIONTIME,B.REFASSETID,B.RESERVETRANSID,B.REVALUATIONAMOUNT,B.REVALUATIONTRANS,B.MAROWNWORKCAPITALISED,B.MARTRANSFERCOMPANY,B.RECVERSION,B.RECID,C.NEGATIVENETBOOKVALUE,C.EXCEEDINGNETBOOKVALUE,C.RECID,C.ASSETID,C.BOOKID FROM LEDGERJOURNALTRANS A,LEDGERJOURNALTRANS_ASSET B,ASSETBOOK C WHERE ((A.DATAAREAID=@P1) AND (A.JOURNALNUM=@P2)) AND ((B.DATAAREAID=@P3) AND (A.RECID=B.REFRECID)) AND ((C.DATAAREAID=@P4) AND (((((C.ASSETID=A.OFFSETACCOUNT) AND (C.BOOKID=B.BOOKID)) AND (A.OFFSETACCOUNTTYPE=@P5)) AND ((B.TRANSTYPE<>@P6) AND (B.TRANSTYPE<>@P7))) AND ((C.NEGATIVENETBOOKVALUE=@P8) OR (C.EXCEEDINGNETBOOKVALUE=@P9)))) ORDER BY A.DATAAREAID,A.OFFSETACCOUNT,B.DATAAREAID,B.BOOKID',@p5 output,@p6 output,@p7 output,N'bfl',N'GBFL020438',N'bfl',N'bfl',5,8,9,0,0
select @p1, @p2, @p5, @p6, @p7
April 26, 2012 at 7:37 am
AX 2009 is a tricky beast to trace as its all mostly written in APICursors
The attached RAR file has a couple of documents in it in relation to planning your DB setup, might be worth checking if these have been done and also one on troubleshooting AX which may help.
There is also the intelligent data management framework in there which is downloadable from Microsoft which will allow you to do archiving and index maintenance etc through a semi-AX front end due to how it manages the database schema via the AOT.
April 26, 2012 at 8:00 am
wow thats great I will check it out, thanks very much.
April 26, 2012 at 8:07 am
if you can (which will probably be a long shot) try and push for an upgrade to AX2012.
I've not had any experience with 2012 but from what I have heard (and this may not be correct) the majority of the FETCHAPI calls have been replaced with direct SQL calls so its not cursor based anymore.
Again going of what I have heard AX was originally designed for Oracle which handles cursors well and the company who wrote AX bought out by MS and since then MS have slowly been trying to get it more friendly with SQL.
April 26, 2012 at 8:35 am
We are looking at building a 2012 test environment so fingers crossed.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply