Desde el pasado octubre hemos podido probar la preview de la Database Movement API que nos permite listar y descargar copias de las DBs que tenemos en LCS y lanzar refrescos de datos usando una API REST.
Si queréis uniros a la preview primero necesitáis ser parte del Insider Program donde podéis uniros al «Dynamics 365 for Finance and Operations Insider Community«. Una vez invitados a la organización de Yammer podéis pedir acceso al grupo «Self-Service Database Movement / DataALM» donde recibiréis toda la info necesaria para uniros a la preview y activar la funcionalidad en LCS.
Como sabréis, la capa de acceso a datos de MSDyn365FO es un poco diferente del T-SQL. Esto quiere decir que si copias una consulta en AX y la pegas en el SSMS no será válida en el 99% de los casos (y el 1% restante será un select * from tabla).
En Microsoft Dynamics 365 for Finance and Operations podemos ejecutar operaciones CRUD en el código de dos formas, operaciones set-based (que podríamos traducir como basadas en conjuntos más o menos) y registro a registro.
Cuando ejecutamos una query en MSDyn365FO usando la capa de acceso a datos lo que ocurre es que nuestra consulta termina convertida en un comando SQL. Podemos ver las diferencias usando el método getSQLStatement de la clase xRecord con un generateonly en la query (y un forceliterals para mostrar los valores de los parámetros) para obtener la consulta de SQL. Por ejemplo si ejecutamos el siguiente código:
Obtenemos esta consulta de SQL:
SELECT TOP 1 T1.PAYMTERMID,T1.LINEDISC,T1.TAXWITHHOLDGROUP_TH,T1.PARTYCOUNTRY,T1.ACCOUNTNUM,T1.ACCOUNTSTATEMENT,
T1.AFFILIATED_RU,T1.AGENCYLOCATIONCODE,T1.BANKACCOUNT,T1.BANKCENTRALBANKPURPOSECODE,T1.BANKCENTRALBANKPURPOSETEXT
,T1.BANKCUSTPAYMIDTABLE,T1.BIRTHCOUNTYCODE_IT,T1.BIRTHPLACE_IT,T1.BLOCKED,T1.CASHDISC,T1.CASHDISCBASEDAYS,
T1.CCMNUM_BR,T1.CLEARINGPERIOD,T1.CNAE_BR,T1.CNPJCPFNUM_BR,T1.COMMERCIALREGISTER,T1.COMMERCIALREGISTERINSETNUMBER,
T1.COMMERCIALREGISTERSECTION,T1.COMMISSIONGROUP,T1.COMPANYCHAINID,T1.COMPANYIDSIRET,T1.COMPANYNAFCODE,T1.COMPANYTYPE_MX,
T1.CONSDAY_JP,T1.CONTACTPERSONID,T1.CREDITCARDADDRESSVERIFICATION,T1.CREDITCARDADDRESSVERIFICATIONLEVEL,T1.CREDITCARDADDRESSVERIFICATIONVOID,
T1.CREDITCARDCVC,T1.CREDITMAX,T1.CREDITRATING,T1.CURP_MX,T1.CURRENCY,T1.CUSTCLASSIFICATIONID,T1.CUSTEXCLUDECOLLECTIONFEE,
T1.CUSTEXCLUDEINTERESTCHARGES,T1.CUSTFINALUSER_BR,T1.CUSTGROUP,T1.CUSTITEMGROUPID,T1.CUSTTRADINGPARTNERCODE,T1.CUSTWHTCONTRIBUTIONTYPE_BR,
T1.DEFAULTDIMENSION,T1.DEFAULTDIRECTDEBITMANDATE,T1.DEFAULTINVENTSTATUSID,T1.DESTINATIONCODEID,T1.DLVMODE,T1.DLVREASON,T1.DLVTERM,
T1.EINVOICE,T1.EINVOICEATTACHMENT,T1.EINVOICEEANNUM,T1.ENDDISC,T1.ENTRYCERTIFICATEREQUIRED_W,T1.EXPORTSALES_PL,T1.EXPRESSBILLOFLADING,
T1.FACTORINGACCOUNT,T1.FEDERALCOMMENTS,T1.FEDNONFEDINDICATOR,T1.FINECODE_BR,T1.FISCALCODE,T1.FISCALDOCTYPE_PL,T1.FORECASTDMPINCLUDE,
T1.FOREIGNRESIDENT_RU,T1.FREIGHTZONE,T1.GENERATEINCOMINGFISCALDOCUMENT_BR,T1.GIROTYPE,T1.GIROTYPEACCOUNTSTATEMENT,T1.GIROTYPECOLLECTIONLETTER,
T1.GIROTYPEFREETEXTINVOICE,T1.GIROTYPEINTERESTNOTE,T1.GIROTYPEPROJINVOICE,T1.ICMSCONTRIBUTOR_BR,T1.IDENTIFICATIONNUMBER,T1.IENUM_BR,
T1.INCLTAX,T1.INSSCEI_BR,T1.INTBANK_LV,T1.INTERCOMPANYALLOWINDIRECTCREATION,T1.INTERCOMPANYAUTOCREATEORDERS,T1.INTERCOMPANYDIRECTDELIVERY,
T1.INTERESTCODE_BR,T1.INVENTLOCATION,T1.INVENTPROFILEID_RU,T1.INVENTPROFILETYPE_RU,T1.INVENTSITEID,T1.INVOICEACCOUNT,T1.INVOICEADDRESS,
T1.INVOICEPOSTINGTYPE_RU,T1.IRS1099CINDICATOR,T1.ISRESIDENT_LV,T1.ISSUEOWNENTRYCERTIFICATE_W,T1.ISSUERCOUNTRY_HU,T1.LINEOFBUSINESSID,
T1.LVPAYMTRANSCODES,T1.MAINCONTACTWORKER,T1.MANDATORYCREDITLIMIT,T1.MANDATORYVATDATE_PL,T1.MARKUPGROUP,T1.MCRMERGEDPARENT,
T1.MCRMERGEDROOT,T1.MULTILINEDISC,T1.NIT_BR,T1.NUMBERSEQUENCEGROUP,T1.ONETIMECUSTOMER,T1.ORDERENTRYDEADLINEGROUPID,
T1.ORGID,T1.OURACCOUNTNUM,T1.PACKAGEDEPOSITEXCEMPT_PL,T1.PACKMATERIALFEELICENSENUM,T1.PARTY,T1.PARTYSTATE,T1.PASSPORTNO_HU,T1.PAYMDAYID,
T1.PAYMENTREFERENCE_EE,T1.PAYMIDTYPE,T1.PAYMMODE,T1.PAYMSCHED,T1.PAYMSPEC,T1.PDSCUSTREBATEGROUPID,T1.PDSFREIGHTACCRUED,
T1.PDSREBATETMAGROUP,T1.PRICEGROUP,T1.RESIDENCEFOREIGNCOUNTRYREGIONID_IT,T1.RFC_MX,T1.SALESCALENDARID,T1.SALESDISTRICTID,
T1.SALESGROUP,T1.SALESPOOLID,T1.SEGMENTID,T1.SERVICECODEONDLVADDRESS_BR,T1.STATEINSCRIPTION_MX,T1.STATISTICSGROUP,T1.SUBSEGMENTID,
T1.SUFRAMA_BR,T1.SUFRAMANUMBER_BR,T1.SUFRAMAPISCOFINS_BR,T1.SUPPITEMGROUPID,T1.TAXGROUP,T1.TAXLICENSENUM,T1.TAXPERIODPAYMENTCODE_PL,
T1.TAXWITHHOLDCALCULATE_IN,T1.TAXWITHHOLDCALCULATE_TH,T1.UNITEDVATINVOICE_LT,T1.USECASHDISC,T1.USEPURCHREQUEST,T1.VATNUM,
T1.VENDACCOUNT,T1.WEBSALESORDERDISPLAY,T1.AUTHORITYOFFICE_IT,T1.EINVOICEREGISTER_IT,T1.FOREIGNERID_BR,T1.PRESENCETYPE_BR,
T1.TAXGSTRELIEFGROUPHEADING_MY,T1.FOREIGNTAXREGISTRATION_MX,T1.CUSTWRITEOFFREFRECID,T1.ISEXTERNALLYMAINTAINED,T1.SATPAYMMETHOD_MX,
T1.SATPURPOSE_MX,T1.CFDIENABLED_MX,T1.FOREIGNTRADE_MX,T1.WORKFLOWSTATE,T1.USEORIGINALDOCUMENTASFACTURE_RU,T1.COLLECTIONLETTERCODE,
T1.BLOCKFLOORLIMITUSEINCHANNEL,T1.AXZMODEL182LEGALNATURE,T1.AXZCRMGUID,T1.MODIFIEDDATETIME,T1.MODIFIEDBY,T1.CREATEDDATETIME,T1.RECVERSION,
T1.PARTITION,T1.RECID,T1.MEMO
FROM CUSTTABLE T1
WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'usmf')) AND (ACCOUNTNUM=N'0001'))
Podemos ver que se seleccionan todos los campos y que los filtros en el where incluyen la cuenta que hemos filtrado (además del DataAreaId y Partition).
Cuando se ejecuta un while select en MSDyn365FO, lo que ocurre en SQL Server es que se ejecuta una consulta por cada vuelta del while. Lo mismo ocurre si se ejecuta un update o delete dentro del bucle. Esto se conoce como operación registro a registro.
Imaginad que queréis actualizar la nota de todos los clientes que sean del grupo de clientes 10. Podríamos hacerlo con un while select de esta manera:
Esto ejecutaría tantas consultas en SQL como clientes del grupo 10 existan, una por cada vuelta del bucle. O podríamos usar operacionesset-based:
Esto ejecutará la actualización de todos los clientes del grupo 10 en una única consulta de SQL Server en vez de una por cliente:
UPDATE CUSTTABLE
SET MEMO = 'Special customer'
WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'usmf')) AND (CUSTGROUP=N'10'))
Ejecutar estos métodos en vez de while selects debería ser por lo general más rápido porque se lanza una sola consulta SQL. Pero…
¿Por qué son lentas mis operaciones set-based?
Existen algunos escenarios documentados en los que las operaciones set-based se convierten en operaciones registro a registro como podemos ver en la siguiente tabla:
DELETE_FROM
UPDATE_RECORDSET
INSERT_RECORDSET
ARRAY_INSERT
Use … to override
Non-SQL tables
Yes
Yes
Yes
Yes
Not applicable
Delete actions
Yes
No
No
No
skipDeleteActions
Database log enabled
Yes
Yes
Yes
No
skipDatabaseLog
Overridden method
Yes
Yes
Yes
Yes
skipDataMethods
Alerts set up for table
Yes
Yes
Yes
No
skipEvents
ValidTimeStateFieldType property not equal to None on a table
Yes
Yes
Yes
Yes
Not applicable
En el ejemplo, si el método update de la CustTable está sobreescrito (que lo está) la operación del update_recordset se ejecutará como si fuera un while select, actualizando registro a registro.
En el caso del update_recordset esto se puede solucionar llamando al método skipDataMethods antes de ejecutar el update:
Esto hace que no se ejecute el método update (o insert si se hace un insert_recordset), más o menos como si se hiciera un doUpdate en el loop. El resto de los métodos también se pueden saltar con el método correspondiente que aparece en la última columna de la tabla.
Así que, para actualizaciones masivas siempre deberíamos usar operaciones set-based, y activar esto también en las data entities con la propiedad EnableSetBasedSqlOperations.
Y ahora viene otro pero.
¿Debería usar siempre operaciones set-based para actualizaciones masivas?
Como siempre, desarrollar en un ERP es bastante delicado, y escenarios parecidos pueden tener soluciones totalmente diferentes. Simplemente hay que analizar y tomar la decisión.