Dynamics Community Forum Thread Details

Why Kernel changes the where clause value from navarchar to Number this make query slow .

Hello There,

While doing some research I found that the sales id used in the where clause is converted to a Number in SQL query. If we can send it as Nvarchar the query will result faster. I am looking for

1. Why does AX changes it to Number ?

2. is there a way to change it back to varchar?

Select  wmsPickingRoute where wmsPickingRoute.expeditionStatus ==  WMSExpeditionStatus::Complete
                && wmsPickingRoute.transRefId == SalesTable.SalesID;

SQL for above

SELECT T1.PICKINGROUTEID,
	T1.SHIPMENTID,
	T1.EXPEDITIONSTATUS,
	T1.CUSTOMER,
	T1.TRANSTYPE,
	T1.TRANSREFID,
	T1.HANDLINGTYPE,
	T1.INVENTLOCATIONID,
	T1.VOLUME,
	T1.OPTIMIZEDPICKING,
	T1.EXPECTEDEXPEDITIONTIME,
	T1.PICKINGAREAID,
	T1.PRIORITY,
	T1.STARTDATETIME,
	T1.STARTDATETIMETZID,
	T1.ENDDATETIME,
	T1.ENDDATETIMETZID,
	T1.ACTIVATIONDATETIME,
	T1.ACTIVATIONDATETIMETZID,
	T1.INTERCOMPANYPOSTED,
	T1.SHIPMENTTYPE,
	T1.DLVMODEID,
	T1.DLVTERMID,
	T1.DLVDATE,
	T1.DELIVERYNAME,
	T1.OPERATORWORKER,
	T1.AUTODECREASEQTY,
	T1.PRINTMGMTSITEID,
	T1.PARMID,
	T1.DELIVERYPOSTALADDRESS,
	T1.MCRPICKINGWAVEREF,
	T1.MCRPACKINGBOXNAME,
	T1.MODIFIEDDATETIME,
	T1.MODIFIEDBY,
	T1.MODIFIEDTRANSACTIONID,
	T1.CREATEDDATETIME,
	T1.CREATEDBY,
	T1.CREATEDTRANSACTIONID,
	T1.RECVERSION,
	T1.PARTITION,
	T1.RECID 
FROM WMSPICKINGROUTE T1 
WHERE (((PARTITION=5637144576) 
	AND (DATAAREAID='JM')) 
	AND ((EXPEDITIONSTATUS=10) 
	AND (TRANSREFID=7002109664)))

instead of (TRANSREFID=7002109664))) in where clause

(TRANSREFID='7002109664'))) the query is faster .. how can achieve this?

Quick Links