{"id":52488,"date":"2023-01-26T11:14:15","date_gmt":"2023-01-26T11:14:15","guid":{"rendered":"https:\/\/arquiconsult.com\/?p=52488"},"modified":"2024-10-23T10:43:14","modified_gmt":"2024-10-23T09:43:14","slug":"bc-datatransfer","status":"publish","type":"post","link":"https:\/\/arquiconsult.com\/en\/bc-datatransfer\/","title":{"rendered":"#033 BC DATATRANSFER"},"content":{"rendered":"<div class=\"services_vertical horizontal_services row\" style=\"background-color: #f7f7f7; padding: 25px; border-radius: 5px; text-align: justify;\">\n<p>Since Microsoft Dynamics 365 Business Central version 21 there is a new AL type to use when transferring data in bulk is needed.<\/p>\n<p>DataTransfer is a new AL type that allows to transfer data from fields or copy all rows from one table to another.<\/p>\n<p>The usage of this data type produces SQL code that operates on sets (direct operations in SQL), and thereby, this behavior will improve performance.<\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><strong>How to use? <\/strong><\/p>\n<p>In the example below there are two tables:<\/p>\n<ul>\n<li>\u201cFrom Table\u201d, containing 10.000 records.<\/li>\n<li>\u201cTo Table\u201d, containing 0 records.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" class=\"size-medium wp-image-52492 aligncenter\" src=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/How-to-use-640x366.png\" alt=\"How to use\" width=\"640\" height=\"366\" srcset=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/How-to-use-640x366.png 640w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/How-to-use-768x439.png 768w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/How-to-use-320x183.png 320w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/How-to-use-480x275.png 480w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/How-to-use-800x458.png 800w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/How-to-use.png 1365w\" sizes=\"(max-width: 640px) 100vw, 640px\" title=\"-\"><\/p>\n<p>&nbsp;<\/p>\n<p>To demonstrate the performance improvements using DataTransfer, two functions were developed to transfer data from one table to the other:<\/p>\n<ul>\n<li>CopyRowsUsingDataTransfer()<\/li>\n<li>CopyRowsUsingOldMethod().<\/li>\n<\/ul>\n<p><img loading=\"lazy\" class=\"size-medium wp-image-52495 aligncenter\" src=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/To-demonstrate-the-performance-improvements-using-DataTransfer-505x480.png\" alt=\"To demonstrate the performance improvements using DataTransfer\" width=\"505\" height=\"480\" srcset=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/To-demonstrate-the-performance-improvements-using-DataTransfer-505x480.png 505w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/To-demonstrate-the-performance-improvements-using-DataTransfer-320x304.png 320w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/To-demonstrate-the-performance-improvements-using-DataTransfer-480x456.png 480w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/To-demonstrate-the-performance-improvements-using-DataTransfer.png 635w\" sizes=\"(max-width: 505px) 100vw, 505px\" title=\"-\"><\/p>\n<p>&nbsp;<\/p>\n<p>The execution time of each function is shown below:<\/p>\n<p><img loading=\"lazy\" class=\" wp-image-52498 aligncenter\" src=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/The-execution-time-of-each-function-is-shown-below-640x127.png\" alt=\"The execution time of each function is shown below\" width=\"615\" height=\"122\" srcset=\"https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/The-execution-time-of-each-function-is-shown-below-640x127.png 640w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/The-execution-time-of-each-function-is-shown-below-768x152.png 768w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/The-execution-time-of-each-function-is-shown-below-320x63.png 320w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/The-execution-time-of-each-function-is-shown-below-480x95.png 480w, https:\/\/arquiconsult.com\/wp-content\/uploads\/2023\/03\/The-execution-time-of-each-function-is-shown-below.png 774w\" sizes=\"(max-width: 615px) 100vw, 615px\" title=\"-\"><\/p>\n<p>&nbsp;<\/p>\n<p>Using DataTransfer, the data was transferred in 2 seconds. As for the old method, row by row, it took 16 seconds.<\/p>\n<p>This multiplied for thousands of records, is a major difference in performance.<\/p>\n<p><strong>\u00a0<\/strong><\/p>\n<p><strong>Limitations <\/strong><\/p>\n<ul>\n<li>Only allowed in upgrade code (Upgrade Codeunits)<\/li>\n<li>It will not work for the following table types:\n<ul>\n<li>Non-SQL tables<\/li>\n<li>System and virtual tables<\/li>\n<li>Audited tables (as destination table)<\/li>\n<li>Obsoleted (as destination table)<\/li>\n<\/ul>\n<\/li>\n<li>It will not work for the following field types:\n<ul>\n<li>System and Timestamp fields (as destination field)<\/li>\n<li>Media and MediaSet<\/li>\n<li>Calculated fields<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<!--themify_builder_content-->\n<div id=\"themify_builder_content-52488\" data-postid=\"52488\" class=\"themify_builder_content themify_builder_content-52488 themify_builder tf_clear\">\n    \t<!-- module_row -->\n\t<div  data-lazy=\"1\" class=\"module_row themify_builder_row tb_nus1581 tb_first tf_clearfix\">\n\t    \t\t<div class=\"row_inner col_align_top col-count-1 tf_box tf_w tf_rel\">\n\t\t\t<div  data-lazy=\"1\" class=\"module_column tb-column col-full first tb_cai8581 tf_box\">\n\t\t\t    \t        <div class=\"tb-column-inner tf_box tf_w\">\n\t\t    <!-- module text -->\n<div  class=\"module module-text tb_9tkr581 arquitps-data  \" data-lazy=\"1\">\n        <div  class=\"tb_text_wrap\">\n    <p><script>\njQuery(\".tbp_post_month:contains(Janeiro)\").html(\"January\");\n\njQuery(\".tbp_post_month:contains(Fevereiro)\").html(\"February\");\n\n\njQuery(\".tbp_post_month:contains(Mar\u00e7o)\").html(\"March\");\n\n\njQuery(\".tbp_post_month:contains(Abril)\").html(\"April\");\n\n\njQuery(\".tbp_post_month:contains(Maio)\").html(\"May\");\n\n\njQuery(\".tbp_post_month:contains(Junho)\").html(\"June\");\n\n\njQuery(\".tbp_post_month:contains(Julho)\").html(\"July\");\n\njQuery(\".tbp_post_month:contains(Agosto)\").html(\"August\");\n\njQuery(\".tbp_post_month:contains(Setembro)\").html(\"September\");\n\njQuery(\".tbp_post_month:contains(Outubro)\").html(\"October\");\n\njQuery(\".tbp_post_month:contains(Novembro)\").html(\"November\");\n\njQuery(\".tbp_post_month:contains(Dezembro)\").html(\"December\");\t\n\n\n\n<\/script><\/p>    <\/div>\n<\/div>\n<!-- \/module text -->\t        <\/div>\n\t    \t<\/div>\n\t\t    <\/div>\n\t    <!-- \/row_inner -->\n\t<\/div>\n\t<!-- \/module_row -->\n\t\t<!-- module_row -->\n\t<div  data-anchor=\"sucesso\" data-hide-anchor=\"1\" data-lazy=\"1\" class=\"module_row themify_builder_row sucesso repeat tb_has_section tb_section-sucesso tb_wdfw185 tf_clearfix\" >\n\t    \t\t<div class=\"row_inner col_align_top col-count-1 tf_box tf_w tf_rel\">\n\t\t\t<div  data-lazy=\"1\" class=\"module_column tb-column col-full first tb_6hs3185 tf_box\">\n\t\t\t    \t        <div class=\"tb-column-inner tf_box tf_w\">\n\t\t    <!-- module buttons -->\n<div  class=\"module module-buttons tb_pnmr185 buttons-horizontal transparent popmake-24705 large circle tf_textc\" data-lazy=\"1\">\n    \t<div class=\"module-buttons-item tf_inline_b\">\n\t\t\t\t\t\t\t<a href=\"#\" class=\"ui builder_button tb_default_color\" >\n\t\t\t\t\t\t\t\t\t\t\t<span class=\"tf_inline_b tf_vmiddle\">Give as a Feedback and Suggest Questions<\/span>\n\t\t\t\t\t\t\t\t\t\t\t<\/a>\n\t\t\t    \t<\/div>\n\t<\/div>\n<!-- \/module buttons -->\n\t        <\/div>\n\t    \t<\/div>\n\t\t    <\/div>\n\t    <!-- \/row_inner -->\n\t<\/div>\n\t<!-- \/module_row -->\n\t<\/div>\n<!--\/themify_builder_content-->\n","protected":false},"excerpt":{"rendered":"<p>Since Microsoft Dynamics 365 Business Central version 21 there is a new AL type to use when transferring data in bulk is needed. DataTransfer is a new AL type that allows to transfer data from fields or copy all rows from one table to another. The usage of this data type produces SQL code that [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":52503,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2099,2124],"tags":[2076,2324,1859,1173],"acf":[],"_links":{"self":[{"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/posts\/52488"}],"collection":[{"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/comments?post=52488"}],"version-history":[{"count":0,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/posts\/52488\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/media\/52503"}],"wp:attachment":[{"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/media?parent=52488"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/categories?post=52488"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/arquiconsult.com\/en\/wp-json\/wp\/v2\/tags?post=52488"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}