Obtener consulta T-SQL desde X++

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).

Para ayudarnos cuando queramos comprobar datos en SQL Server podemos usar el método getSQLStatement de la clase xRecord.

Si ejecutamos este código en Dynamics 365 obtendremos la consulta de SQL en la variable sql. Atentos al modificador generateonly, sin él no se generará la consulta SQL!

Esta es la consulta que obtenemos:

He quitado algunos campos para mejorar la legibilidad, pero con el método getSQLstatement siempre obtendremos todos los campos en la consulta de SQL, incluso si seleccionamos un campo en AX. Como véis tenemos un ? como parámetro en la cláusula where en vez del valor. Si queremos mostrar también el valor tendremos que usar forceliterals en el select de D365:

Y obtendremos esto:

Hay que tener en cuenta que si usamos el generateonly en nuestro select no tendremos datos en el buffer de la tabla:

Esto quiere decir que esta funcionalidad es únicamente para testeo y debugar, si os lo dejáis cuando hagáis el check in de vuestro código… bueno, os lo podéis imaginar.

Operaciones «set-based» lentas?

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.

La recomendación de Microsoft es usar siempre las operaciones set-based, si es posible, como se puede ver en la sesión Implementation Best Practices for Dynamics 365: Performance best practices for a successful Dynamics 365 Finance and Operations implementation del pasado Business Applications Summit de junio.

¿Por qué?

Set-based Vs. 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:

 

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 operaciones set-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:

Existen tres operaciones set-based en MSDyn365FO, update_recordset para actualizar, insert_recordset para crear registros y delete_from para borrarlos de forma masiva. Además de poder hacer inserciones masivas con las listas de tipo RecordSortedList y RecordInsertList.

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?

Bueno, depende de con qué datos estemos trabajando. Hay un post magnífico de Denis Trunin llamado «Blocking in D365FO(and why you shouldn’t always follow MS recommendations)» que explica un ejemplo perfecto de cuándo las operaciones set-based podrían ser perjudiciales.

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.