Hi If you send a text representation of the query plan that you're getting from this statement and details of any non-standard indexes that are defined on either table I'll see if an obvious solution springs to mind.
Regards Malcolm Burtt Touchstone People, Partnerships, Solutions From: development-axapta@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of pkpeterson652 Sent: 09 January 2008 16:32 To: development-axapta@yahoogroups.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]
