SQL Server 2008 R2, Dynamics AX 2009 We have this ugly query
DECLARE @P1 NVARCHAR(5), @P2 INT, @P3 INT, @P4 NVARCHAR(11), @P5 INT, @P6 NVARCHAR(5), @P7 NVARCHAR(11)SELECT A.SALESID,
A.LINENUM,
A.ITEMID,
A.SALESSTATUS,
A.LEDGERACCOUNT,
A.NAME,
A.EXTERNALITEMID,
A.TAXGROUP,
A.QTYORDERED,
A.SALESDELIVERNOW,
A.REMAINSALESPHYSICAL,
A.REMAINSALESFINANCIAL,
A.COSTPRICE,
A.SALESPRICE,
A.CURRENCYCODE,
A.LINEPERCENT,
A.LINEDISC,
A.LINEAMOUNT,
A.CONFIRMEDDLV,
A.RESERVATION,
A.SALESUNIT,
A.DIMENSION,
A.DIMENSION2_,
A.DIMENSION3_,
A.PRICEUNIT,
A.PROJTRANSID,
A.INVENTTRANSID,
A.CUSTGROUP,
A.CUSTACCOUNT,
A.SALESQTY,
A.SALESMARKUP,
A.INVENTDELIVERNOW,
A.MULTILNDISC,
A.MULTILNPERCENT,
A.SALESTYPE,
A.BLOCKED,
A.COMPLETE,
A.REMAININVENTPHYSICAL,
A.TRANSACTIONCODE,
A.TAXITEMGROUP,
A.TAXAUTOGENERATED,
A.UNDERDELIVERYPCT,
A.OVERDELIVERYPCT,
A.BARCODE,
A.BARCODETYPE,
A.INVENTREFTRANSID,
A.INVENTREFTYPE,
A.INVENTREFID,
A.ITEMBOMID,
A.LINEHEADER,
A.SCRAP,
A.DLVMODE,
A.INVENTTRANSIDRETURN,
A.PROJCATEGORYID,
A.PROJID,
A.INVENTDIMID,
A.TRANSPORT,
A.STATPROCID,
A.PORT,
A.PROJLINEPROPERTYID,
A.RECEIPTDATEREQUESTED,
A.CUSTOMERLINENUM,
A.REMAININVENTFINANCIAL,
A.DELIVERYADDRESS,
A.DELIVERYNAME,
A.DELIVERYSTREET,
A.DELIVERYZIPCODE,
A.DELIVERYCITY,
A.DELIVERYCOUNTY,
A.DELIVERYSTATE,
A.DELIVERYCOUNTRYREGIONID,
A.DELIVERYTYPE,
A.CUSTOMERREF,
A.PURCHORDERFORMNUM,
A.RECEIPTDATECONFIRMED,
A.BLANKETREFTRANSID,
A.STATTRIANGULARDEAL,
A.SHIPPINGDATEREQUESTED,
A.SHIPPINGDATECONFIRMED,
A.ADDRESSREFRECID,
A.ADDRESSREFTABLEID,
A.EINVOICEACCOUNTCODE,
A.DELIVERYDATECONTROLTYPE,
A.ATPINCLPLANNEDORDERS,
A.ATPTIMEFENCE,
A.ACTIVITYNUMBER,
A.RETURNALLOWRESERVATION,
A.ITEMREPLACED,
A.RETURNDEADLINE,
A.EXPECTEDRETQTY,
A.RETURNSTATUS,
A.RETURNARRIVALDATE,
A.RETURNCLOSEDDATE,
A.RETURNDISPOSITIONCODEID,
A.CUSTOMSNAME_MX,
A.CUSTOMSDOCNUMBER_MX,
A.CUSTOMSDOCDATE_MX,
A.PROPERTYNUMBER_MX,
A.REFRETURNINVOICETRANS_W,
A.POSTINGPROFILE_RU,
A.PRICEAGREEMENTDATE_RU,
A.CORRECTIONDESC_PL,
A.CREDITNOTEINTERNALREF_PL,
A.DELIVERYFLAT_RU,
A.DELIVERYBUILDING_RU,
A.DELIVERYESTATE_RU,
A.DELIVERYSTREETID_RU,
A.DELIVERYSETTLEMENTID_RU,
A.DELIVERYTOWNID_RU,
A.RBOVARIANTID,
A.HHTHANDHELDUSERID,
A.HHTTRANSTIME,
A.HHTTRANSDATE,
A.ECC_PRICEGROUPID,
A.ECC_SALESPRICECALC,
A.ECC_CHEQUENUMBER,
A.ECC_CHEQUEROWNUMBER,
A.ECC_DOCUMENTCOMMENT,
A.ECC_SHOPASSISTANT,
A.ECC_SALEDATEFROMCOMISS,
A.ECC_CARDNUMBER,
A.ECC_ISPRESENTS,
A.ECC_ISHOPSALESID,
A.ECC_ISHOPPOSTPACKAGENUMBER,
A.ECC_ISHOPINVENTTRANSFERCREATED,
A.ECC_ISHOPINVENTTRANSFERLI50013,
A.ECC_DISTRIBUTIONRATING,
A.ECC_CASHNUMBER,
A.ECC_ISHOPRRN,
A.ECC_ISHOPSHIPPINGINVENTLO50017,
A.ECC_ISHOPINVENTTRANSFERRE50018,
A.ECC_ISHOPSALESLINESALECREATED,
A.ECC_ISHOPINTEGRATIONPAYMETHOD,
A.ECC_ISHOPPROCESSQTY,
A.ECC_ISHOPFINISHED,
A.ECC_RBOLOYALTYNUMBERCARD,
A.ECC_ISHOPBONUSESPOINTS,
A.ECC_ISHOPRATINGSPOINTS,
A.ECC_RBOLOYALTYPOINTSACCUM50026,
A.ECC_ISHOPTRANSDATETIME,
A.ECC_ISHOPTRANSDATETIMETZID,
A.ECC_ISHOPEXTRAPOINTSPAY,
A.ECC_ISHOPBONUSESPOINTSPAY,
A.ECC_ISHOPPICKUPINVENTLOCA50030,
A.ECC_ISHOPSALESRESERVE,
A.ECC_RBONETAMOUNT,
A.ECC_RBOTAXAMOUNT,
A.MODIFIEDDATETIME,
A.CREATEDDATETIME,
A.RECVERSION,
A.RECID,
B.INVENTCOLORID,
B.INVENTSIZEID,
B.INVENTLOCATIONID,
B.ECC_BUSINESSUNITID,
B.ECC_SEASONID,
B.INVENTDIMID,
B.RECVERSION,
101090 FROM SALESLINE A,
INVENTDIM B WHERE((A.DATAAREAID = @P1)
AND ((((A.COMPLETE = @P2)
AND (A.ECC_ISHOPSALESRESERVE = @P3))
AND NOT((A.ECC_ISHOPSALESID = @P4)))
AND (A.ECC_ISHOPFINISHED = @P5)))
AND ((B.DATAAREAID = @P6)
AND ((B.INVENTLOCATIONID = @P7)
AND (A.INVENTDIMID = B.INVENTDIMID))) ORDER BY A.DATAAREAID DESC,
A.INVENTDIMID DESC OPTION(FAST 1, LOOP JOIN, FORCE ORDER);
I think the query options that are used here are not what we actually should do with this specific query to try to make it faster. It goes very slow on our system. The plan is here: QUERY PLAN Will appreciate any help
