Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / What is this query about?
Microsoft Dynamics AX (Archived)

What is this query about?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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

  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,253 Super User 2024 Season 2 on at
    RE: What is this query about?

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans