You’re offline. This is a read only version of the page.
Recently I checked in sql server, running sp_whoisactive command and found this query had been running for hours:
SELECT T1.ITEMID,T1.ITEMTYPE,T1.PURCHMODEL,T1.HEIGHT,T1.WIDTH,T1.SALESMODEL,T1.COSTGROUPID,T1.REQGROUPID,T1.EPCMANAGER,T1.PRIMARYVENDORID,T1.NETWEIGHT,T1.DEPTH,T1.UNITVOLUME,T1.BOMUNITID,T1.ITEMPRICETOLERANCEGROUPID,T1.DENSITY,T1.COSTMODEL,T1.USEALTITEMID,T1.ALTITEMID,T1.MATCHINGPOLICY,T1.INTRACODE,T1.PRODFLUSHINGPRINCIP,T1.PBAITEMAUTOGENERATED,T1.WMSARRIVALHANDLINGTIME,T1.BOMMANUALRECEIPT,T1.PHANTOM,T1.INTRAUNIT,T1.BOMLEVEL,T1.BATCHNUMGROUPID,T1.AUTOREPORTFINISHED,T1.ORIGCOUNTRYREGIONID,T1.STATISTICSFACTOR,T1.ALTCONFIGID,T1.STANDARDCONFIGID,T1.PRODPOOLID,T1.PROPERTYID,T1.ABCTIEUP,T1.ABCREVENUE,T1.ABCVALUE,T1.ABCCONTRIBUTIONMARGIN,T1.COMMISSIONGROUPID,T1.SALESPERCENTMARKUP,T1.SALESCONTRIBUTIONRATIO,T1.SALESPRICEMODELBASIC,T1.NAMEALIAS,T1.PRODGROUPID,T1.PROJCATEGORYID,T1.GROSSDEPTH,T1.GROSSWIDTH,T1.GROSSHEIGHT,T1.SORTCODE,T1.SERIALNUMGROUPID,T1.ITEMBUYERGROUPID,T1.TAXPACKAGINGQTY,T1.ORIGSTATEID,T1.WMSPICKINGQTYTIME,T1.TARAWEIGHT,T1.PACKAGINGGROUPID,T1.SCRAPVAR,T1.SCRAPCONST,T1.STANDARDINVENTCOLORID,T1.STANDARDINVENTSIZEID,T1.ITEMDIMCOSTPRICE,T1.ALTINVENTSIZEID,T1.ALTINVENTCOLORID,T1.FORECASTDMPINCLUDE,T1.PRODUCT,T1.PALLETTAGGING,T1.ITEMTAGGINGLEVEL,T1.DEFAULTDIMENSION,T1.BOMCALCGROUPID,T1.PBAITEMCONFIGURABLE,T1.PBAINVENTITEMGROUPID,T1.PBAHIDEDIALOG,T1.PBAHIDEAPPROVAL,T1.PBAAUTOSTART,T1.PBAMANDATORYCONFIG,T1.PDSCWWMSSTANDARDPALLETQTY,T1.PDSCWWMSMINIMUMPALLETQTY,T1.PDSCWWMSQTYPERLAYER,T1.ALTINVENTSTYLEID,T1.BATCHMERGEDATECALCULATIONMETHOD,T1.PDSBASEATTRIBUTEID,T1.PDSBESTBEFORE,T1.PDSFREIGHTALLOCATIONGROUPID,T1.PDSITEMREBATEGROUPID,T1.PDSPOTENCYATTRIBRECORDING,T1.PDSSHELFADVICE,T1.PDSSHELFLIFE,T1.PDSTARGETFACTOR,T1.PDSVENDORCHECKITEM,T1.PMFPLANNINGITEMID,T1.PMFPRODUCTTYPE,T1.PMFYIELDPCT,T1.STANDARDINVENTSTYLEID,T1.AGENCYID,T1.ITEMCLASSIFICATION,T1.CROSSDOCK,T1.HSUOMDESC,T1.HSPRODUCTHIERARCHYL1,T1.HSPRODUCTHIERARCHYL2,T1.HSPRODUCTHIERARCHYL3,T1.HSPRODUCTHIERARCHYL4,T1.HSCOMBINATIONITEM,T1.BPC_OLDSYSTEMCODE,T1.BPC_ROUNDINGTYPE,T1.MODIFIEDDATETIME,T1.DEL_MODIFIEDTIME,T1.MODIFIEDBY,T1.CREATEDDATETIME,T1.DEL_CREATEDTIME,T1.CREATEDBY,T1.RECVERSION,T1.PARTITION,T1.RECID,T2.UOMSEQGROUPID,T2.MAXPICKQTY,T2.ITEMID,T2.RFDESCRIPTION1,T2.RFDESCRIPTION2,T2.PACKSIZECATEOGRYID,T2.FILTERCODE,T2.FILTERCODE2_,T2.FILTERCODE3_,T2.FILTERCODE4_,T2.FILTERGROUP,T2.FILTERGROUP2_,T2.FILTERCHANGED,T2.PRODQTY,T2.PHYSDIMID,T2.PACKAGECLASSID,T2.PICKWCNEG,T2.ALLOWMATERIALOVERPICK,T2.MODIFIEDDATETIME,T2.MODIFIEDBY,T2.RECVERSION,T2.PARTITION,T2.RECID,T3.ITEMDATAAREAID,T3.MODELGROUPID,T3.ITEMID,T3.MODELGROUPDATAAREAID,T3.RECVERSION,T3.PARTITION,T3.RECID,T4.ITEMID,T4.ITEMDATAAREAID,T4.ITEMGROUPID,T4.ITEMGROUPDATAAREAID,T4.RECVERSION,T4.PARTITION,T4.RECID,T5.TRACKINGDIMENSIONGROUP,T5.ITEMID,T5.ITEMDATAAREAID,T5.RECVERSION,T5.PARTITION,T5.RECID,T6.STORAGEDIMENSIONGROUP,T6.ITEMID,T6.ITEMDATAAREAID,T6.RECVERSION,T6.PARTITION,T6.RECID,T7.PRODUCTMASTER,T7.RETAITOTALWEIGHT,T7.VARIANTCONFIGURATIONTECHNOLOGY,T7.RETAILCOLORGROUPID,T7.RETAILSIZEGROUPID,T7.RETAILSTYLEGROUPID,T7.ISPRODUCTVARIANTUNITCONVERSIONENABLED,T7.INSTANCERELATIONTYPE,T7.DISPLAYPRODUCTNUMBER,T7.SEARCHNAME,T7.PRODUCTTYPE,T7.PDSCWPRODUCT,T7.ISCALAPRODUCTCODE,T7.MODIFIEDDATETIME,T7.MODIFIEDBY,T7.CREATEDDATETIME,T7.CREATEDBY,T7.RECVERSION,T7.RELATIONTYPE,T7.PARTITION,T7.RECID,T8.BRANDID,T8.BRANDNAME,T8.COUNTRYREGIONID,T8.EANOLD,T8.ENERGYSAVING,T8.ETIMCLASSID,T8.ETIMCLASSNAME,T8.ETIMGROUPID,T8.ETIMGROUPNAME,T8.MANUFACTURERNAME,T8.MANUFACTURERPARTNUMBER,T8.OAKWELLPRODUCTDIVISION,T8.PIMARTICLENUMBER,T8.PRODUCT,T8.REPORTINGFAMILYID,T8.REPORTINGFAMILYNAME,T8.REQUESTORID,T8.REQUESTORNAME,T8.UPCOLD,T8.EAN,T8.UPC,T8.MODIFIEDBY,T8.CREATEDBY,T8.RECVERSION,T8.PARTITION,T8.RECID,T9.ITEMID,T9.MODULETYPE,T9.UNITID,T9.PRICE,T9.PRICEUNIT,T9.MARKUP,T9.LINEDISC,T9.MULTILINEDISC,T9.ENDDISC,T9.TAXITEMGROUPID,T9.MARKUPGROUPID,T9.PRICEDATE,T9.PRICEQTY,T9.ALLOCATEMARKUP,T9.OVERDELIVERYPCT,T9.UNDERDELIVERYPCT,T9.SUPPITEMGROUPID,T9.INTERCOMPANYBLOCKED,T9.PDSPRICINGPRECISION,T9.TAXGSTRELIEFCATEGORY_MY,T9.MODIFIEDDATETIME,T9.DEL_MODIFIEDTIME,T9.MODIFIEDBY,T9.CREATEDDATETIME,T9.DEL_CREATEDTIME,T9.CREATEDBY,T9.RECVERSION,T9.PARTITION,T9.RECID,T10.ITEMID,T10.MODULETYPE,T10.UNITID,T10.PRICE,T10.PRICEUNIT,T10.MARKUP,T10.LINEDISC,T10.MULTILINEDISC,T10.ENDDISC,T10.TAXITEMGROUPID,T10.MARKUPGROUPID,T10.PRICEDATE,T10.PRICEQTY,T10.ALLOCATEMARKUP,T10.OVERDELIVERYPCT,T10.UNDERDELIVERYPCT,T10.SUPPITEMGROUPID,T10.INTERCOMPANYBLOCKED,T10.PDSPRICINGPRECISION,T10.TAXGSTRELIEFCATEGORY_MY,T10.MODIFIEDDATETIME,T10.DEL_MODIFIEDTIME,T10.MODIFIEDBY,T10.CREATEDDATETIME,T10.DEL_CREATEDTIME,T10.CREATEDBY,T10.RECVERSION,T10.PARTITION,T10.RECID,T11.ITEMID,T11.MODULETYPE,T11.UNITID,T11.PRICE,T11.PRICEUNIT,T11.MARKUP,T11.LINEDISC,T11.MULTILINEDISC,T11.ENDDISC,T11.TAXITEMGROUPID,T11.MARKUPGROUPID,T11.PRICEDATE,T11.PRICEQTY,T11.ALLOCATEMARKUP,T11.OVERDELIVERYPCT,T11.UNDERDELIVERYPCT,T11.SUPPITEMGROUPID,T11.INTERCOMPANYBLOCKED,T11.PDSPRICINGPRECISION,T11.TAXGSTRELIEFCATEGORY_MY,T11.MODIFIEDDATETIME,T11.DEL_MODIFIEDTIME,T11.MODIFIEDBY,T11.CREATEDDATETIME,T11.DEL_CREATEDTIME,T11.CREATEDBY,T11.RECVERSION,T11.PARTITION,T11.RECID,T12.ITEMID,T12.COUNTGROUPID,T12.WMSSTOREZONEID,T12.WMSPICKINGLOCATION,T12.WMSLOCATIONIDDEFAULTISSUE,T12.WMSLOCATIONIDDEFAULTRECEIPT,T12.INVENTDIMID,T12.USEWMSORDER,T12.PICKINGLOCATIONREFILLMIN,T12.PDSCWPICKINGLOCATIONREFILLMIN,T12.PDSCWPICKINGLOCATIONMAXQTY,T12.RECVERSION,T12.PARTITION,T12.RECID,T13.DISPLAYPRODUCTNUMBER,T13.RECVERSION,T13.RECID,T13.INSTANCERELATIONTYPE,T14.NAME,T14.RECVERSION,T14.RECID,T15.NAME,T15.RECVERSION,T15.RECID,T16.PRODUCTDIMENSIONGROUP,T16.PRODUCT,T16.MODIFIEDBY,T16.RECVERSION,T16.PARTITION,T16.RECID,T17.DESCRIPTION,T17.NAME,T17.PRODUCT,T17.LANGUAGEID,T17.MODIFIEDDATETIME,T17.MODIFIEDBY,T17.CREATEDDATETIME,T17.RECVERSION,T17.PARTITION,T17.RECID,T18.PRODUCTMASTER,T18.ISVARIANTGENERATIONENABLED,T18.ISREUSECONFIGURATIONENABLED,T18.RECVERSION,T18.PARTITION,T18.RECID,T19.NAME,T19.RECVERSION,T19.RECID FROM INVENTTABLE T1 LEFT OUTER JOIN WHSINVENTTABLE T2 ON (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'kvc')) AND (T1.ITEMID=T2.ITEMID)) LEFT OUTER JOIN INVENTMODELGROUPITEM T3 ON ((T3.PARTITION=5637144576) AND ((T1.ITEMID=T3.ITEMID) AND (T1.DATAAREAID=T3.ITEMDATAAREAID))) LEFT OUTER JOIN INVENTITEMGROUPITEM T4 ON ((T4.PARTITION=5637144576) AND ((T1.ITEMID=T4.ITEMID) AND (T1.DATAAREAID=T4.ITEMDATAAREAID))) LEFT OUTER JOIN ECORESTRACKINGDIMENSIONGROUPITEM T5 ON ((T5.PARTITION=5637144576) AND ((T1.ITEMID=T5.ITEMID) AND (T1.DATAAREAID=T5.ITEMDATAAREAID))) LEFT OUTER JOIN ECORESSTORAGEDIMENSIONGROUPITEM T6 ON ((T6.PARTITION=5637144576) AND ((T1.ITEMID=T6.ITEMID) AND (T1.DATAAREAID=T6.ITEMDATAAREAID))) CROSS JOIN ECORESPRODUCT T7 CROSS JOIN ECORESPRODUCTRELEASEDPIM T8 CROSS JOIN INVENTTABLEMODULE T9 CROSS JOIN INVENTTABLEMODULE T10 CROSS JOIN INVENTTABLEMODULE T11 CROSS JOIN INVENTITEMLOCATION T12 LEFT OUTER JOIN ECORESPRODUCT T13 ON (((T13.PARTITION=5637144576) AND (T13.INSTANCERELATIONTYPE IN (@P1,@P2,@P3) )) AND (T1.PRODUCT=T13.RECID)) LEFT OUTER JOIN ECORESTRACKINGDIMENSIONGROUP T14 ON ((T14.PARTITION=5637144576) AND (T5.TRACKINGDIMENSIONGROUP=T14.RECID)) LEFT OUTER JOIN ECORESSTORAGEDIMENSIONGROUP T15 ON ((T15.PARTITION=5637144576) AND (T6.STORAGEDIMENSIONGROUP=T15.RECID)) LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUPPRODUCT T16 ON ((T16.PARTITION=5637144576) AND (T7.RECID=T16.PRODUCT)) LEFT OUTER JOIN ECORESPRODUCTTRANSLATION T17 ON ((T17.PARTITION=5637144576) AND (((T17.LANGUAGEID=@P4) OR (T17.LANGUAGEID=@P5)) AND (T7.RECID=T17.PRODUCT))) LEFT OUTER JOIN ECORESPRODUCTMASTERMODELINGPOLICY T18 ON ((T18.PARTITION=5637144576) AND (T7.RECID=T18.PRODUCTMASTER)) LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUP T19 ON ((T19.PARTITION=5637144576) AND ((T16.PRODUCTDIMENSIONGROUP=T19.RECID) AND (T16.PRODUCTDIMENSIONGROUP=T19.RECID))) WHERE (((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'kvc')) AND ((T7.ISCALAPRODUCTCODE LIKE @P6 ESCAPE '\' ) AND (((((((((T1.ITEMID=@P7) AND (T7.DISPLAYPRODUCTNUMBER=@P8)) AND (T7.RECID=@P9)) AND (T17.PRODUCT=@P10)) AND (T17.LANGUAGEID>=@P11)) OR ((((T1.ITEMID=@P12) AND (T7.DISPLAYPRODUCTNUMBER=@P13)) AND (T7.RECID=@P14)) AND (T17.PRODUCT>@P15))) OR (((T1.ITEMID=@P16) AND (T7.DISPLAYPRODUCTNUMBER=@P17)) AND (T7.RECID>@P18))) OR ((T1.ITEMID=@P19) AND (T7.DISPLAYPRODUCTNUMBER>@P20))) OR (T1.ITEMID>@P21)))) AND (((T7.PARTITION=5637144576) AND (T7.INSTANCERELATIONTYPE IN (@P22,@P23,@P24) )) AND (T1.PRODUCT=T7.RECID)) AND (((T8.PARTITION=5637144576) AND (T8.DATAAREAID=N'kvc')) AND (T7.RECID=T8.PRODUCT)) AND (((T9.PARTITION=5637144576) AND (T9.DATAAREAID=N'kvc')) AND ((T9.MODULETYPE=@P25) AND (T9.ITEMID=T1.ITEMID))) AND (((T10.PARTITION=5637144576) AND (T10.DATAAREAID=N'kvc')) AND ((T10.MODULETYPE=@P26) AND (T10.ITEMID=T1.ITEMID))) AND (((T11.PARTITION=5637144576) AND (T11.DATAAREAID=N'kvc')) AND ((T11.MODULETYPE=@P27) AND (T11.ITEMID=T1.ITEMID))) AND (((T12.PARTITION=5637144576) AND (T12.DATAAREAID=N'kvc')) AND ((T12.INVENTDIMID=@P28) AND (T1.ITEMID=T12.ITEMID))) ORDER BY T1.ITEMID,T7.DISPLAYPRODUCTNUMBER,T7.RECID,T17.PRODUCT,T17.LANGUAGEID OPTION(FAST 2)
May I know which process in AX is involving this query?
Thanks and Regards,
Lieu
*This post is locked for comments
I have the same question (0)
Hi Lieu,
This might be related to the product details form. But I'm not quite sure. If you have (performance) issues with this query, you can enable SQL logging within the AX client for several users. Then the query together with usage will be available for analysis in the system administration menu. See also: https://technet.microsoft.com/en-us/library/aa582474.aspx
You can also use e.g. the Trace parser to find out what is happening.
Try to see if indexes are maintained on SQL server. If you have fragmented indexes, the queries will become very slow.
Share
Report
All responses (
Answers (
301,146
