Hi
Try using the code profiler to identify the problem. Mit freundlichen Grüßen / Best regards / Med venlig hilsen Jesper Jørgensen / Abt. NMT-XD Senior Product Manager ________________________________ "Der Inhalt dieser Nachricht oder eventueller Anlagen ist vertraulich und ausschließlich für den bezeichneten Adressaten bestimmt. Bitte stellen Sie sicher, dass die Information in dieser Nachricht ausschließlich an die adressierten Personen gelangt. Sollte diese Nachricht versehentlich an Sie gesendet worden sein, dann informieren Sie bitte umgehend den Absender und löschen Sie die Nachricht. Vielen Dank." "The information in this e-mail and any attachments is confidential. The information must only be held in areas that have controlled and limited access to the addressed persons. If this e-mail has been sent to you in error, please immediately notify the sender and delete the e-mail. Thanks." ________________________________ ________________________________ Von: development-axapta@yahoogroups.com [mailto:[EMAIL PROTECTED] Im Auftrag von James Flavell Gesendet: Donnerstag, 10. Januar 2008 09:06 An: development-axapta@yahoogroups.com Betreff: RE: [development-axapta] Bad response time on the CUSTINVOICEJOUR form Snap!!! Same probem here We have the same kind of number of records in the tables (FYI we have about 5 diff AX companies...do you have multi companies, not sure this has anything to do with the problem?) Also we found users who killed there AX session (thinking it was not responding) it actaully left the process in the SQL and it would not stop and took 100%!!! Look in your event viewer for things like AOS saying SPID xxx should be killed (i.e. AOS cannot kill it and you have to go into SQL yourself to do) We seemed to have fixed (just like 5 mins ago) for now but not sure, these are the steps we did for your info: 1) We added an index with invoicedate to custinvoicejour 2) Opened the form again and this time data appeared fine 3) We deleted the index and still the form is fine (this might be SQL caching of some kind I am not sure) Maybe you can first just test a compile of the form and then a full compile Still problem: Check in SQL do indexes exist on this table? (wondering if somehow the AOT indexes did not get to SQL) Do a sync from AOT for this table If these do not work then try creating an index on this table Anyone else having this problem? We thought it might be a AX system file corruption as we had some server problems a while back but it seems maybe it is more of a Standard AX problem since someone else has the problem Also I suggest you try putting the SQL statement in the SQL profiler to see what query path the SQL plans to use (maybe before you try any of the fixes) Looking forward to hearing from you Thanks James _____ From: development-axapta@yahoogroups.com <mailto:development-axapta%40yahoogroups.com> [mailto:development-axapta@yahoogroups.com <mailto:development-axapta%40yahoogroups.com> ] On Behalf Of pkpeterson652 Sent: Wednesday, January 09, 2008 8:32 PM To: development-axapta@yahoogroups.com <mailto:development-axapta%40yahoogroups.com> Subject: [development-axapta] Bad response time on the CUSTINVOICEJOUR form I have a response time problem with the CUSTINVOICEJOUR form. Accounts receivable \ Sales Orders \ Inquiries \ Invoices This form only has some minor modifications to it and when the mods are removed the same results are given. We are running Dynamics AX 4.0 at SP1, SQL Server 2005, and one AOS. The first time this option is selected it takes anywhere from 1 1/2 to 2 1/2 minutes to come up. Once the form has been opened up this form will come up in 1 to 2 seconds. I understand this because the necessary infomation is probably in the cache for the subsequent queries. I have run SQL trace and have isolated the offending query. I have taken the SQL statement shown in the SQL Trace log into the Database Engine tuning advisor, built and updated the recommended Statistics and built the recommend indexes. There are 166,506 records in the CUSINVOICEJOUR table across all companies and 108,388 records in the CUSTINOICESALESLINK table accross all companies. Any suggestions of what I should look at next? SQL Statement is as follows: SELECT A.CUSTGROUP,A.REFNUM,A.SALESID,A.ORDERACCOUNT,A.INVOICEACCOUNT, A.INVOICEDATE,A.DUEDATE,A.CASHDISC,A.CASHDISCDATE,A.QTY,A.VOLUME, A.WEIGHT,A.SUMLINEDISC,A.SALESBALANCE,A.ENDDISC,A.INVOICEAMOUNT, A.CURRENCYCODE,A.EXCHRATE,A.SALESADMINISTRATOR,A.INVOICEID,A.LEDGERVOU CHER,A.UPDATED,A.DIMENSION,A.DIMENSION2_,A.DIMENSION3_, A.ONACCOUNTAMOUNT,A.TAXPRINTONINVOICE,A.LISTCODE,A.DEL_PRINTED, A.DOCUMENTNUM,A.DOCUMENTDATE,A.INTRASTATDISPATCH,A.DELIVERYNAME, A.DELIVERYADDRESS,A.PURCHASEORDER,A.DLVTERM,A.DLVMODE,A.PAYMENT, A.CASHDISCCODE,A.INVOICEROUNDOFF,A.SUMMARKUP,A.COVSTATUS, A.RETURNITEMNUM,A.POSTINGPROFILE,A.BACKORDER,A.PREPAYMENT, A.DLVZIPCODE,A.DLVCOUNTY,A.DLVCOUNTRYREGIONID,A.DLVSTATE,A.TAXGROUP, A.TAXITEMGROUP,A.DEL_TAXSPECIFYTOTAL,A.TAXSPECIFYBYLINE, A.EINVOICELINESPECIFIC,A.DEL_CORRECTEDINVOICEID,A.ONETIMECUSTOMER, A.PAYMENTSCHED,A.SUMTAX,A.SALESTYPE,A.EINVOICEACCOUNTCODE,A.PARMID,A.E USALESLIST,A.EXCHRATESECONDARY,A.TRIANGULATION,A.CUSTOMERREF, A.VATNUM,A.NUMBERSEQUENCEGROUP,A.LANGUAGEID,A.INCLTAX,A.LOG, A.PAYMDAYID,A.INVOICINGNAME,A.INVOICINGADDRESS,A.INVZIPCODE, A.INVCOUNTY,A.INVCOUNTRYREGIONID,A.INVSTATE,A.GIROTYPE, A.CONTACTPERSONID,A.SALESORIGINID,A.BILLOFLADINGID,A.INVENTLOCATIONID, A.FIXEDDUEDATE,A.DELIVERYCITY,A.DELIVERYSTREET,A.INVOICECITY, A.INVOICESTREET,A.PRINTORIGINALS,A.PRINTCOPIES,A.INVOICEAMOUNTMST, A.INVOICEROUNDOFFMST,A.SUMMARKUPMST,A.SUMLINEDISCMST,A.ENDDISCMST, A.SALESBALANCEMST,A.SUMTAXMST,A.DEL_REFDLVZIPCODE,A.DEL_REFINVZIPCODE, A.INTERCOMPANYCOMPANYID,A.INTERCOMPANYPURCHID,A.PROFORMA, A.MILCHECKNUM,A.MILPAYMENTMETHOD,A.MILPAYMENTAMOUNT,A.CREATEDDATE, A.CREATEDTIME,A.CREATEDBY,A.RECVERSION,A.RECID, B.INVOICEID,B.INVOICEDATE,B.SALESID,B.ORIGSALESID,B.INVOICEACCOUNT, B.ORDERACCOUNT,B.DELIVERYNAME,B.DELIVERYADDRESS,B.PARMID, B.INVOICINGADDRESS,B.INVOICINGNAME,B.PURCHASEORDER,B.CUSTOMERREF, B.RECVERSION,B.RECID,A.DEL_CORRECTIVEREASON FROM CUSTINVOICEJOUR A,CUSTINVOICESALESLINK B WHERE ((A.DATAAREAID='100') AND (A.REFNUM=0)) AND ((B.DATAAREAID='100') AND ((((A.SALESID=B.SALESID) AND (A.INVOICEID=B.INVOICEID)) AND (A.INVOICEDATE=B.INVOICEDATE)) AND (B.ORIGSALESID='CSO144702'))) ORDER BY A.DATAAREAID,A.INVOICEACCOUNT,A.INVOICEDATE OPTION(FAST 1) Thanks Paul [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
