Good morning and a great start of the week! Fresh project Data Processing Guidelines (eg Data Import

Denis Turkov

Good morning and a great start of the week!
Fresh project Data Processing Guidelines (eg Data Import, Export, P&S) were recently published: https://documentation.spryker.com/docs/data-processing-guidelines
You are familiar with most of the content, so consider it as an additional conceptual refresh. 😉
If you would like to see any additional section there or faced pitfalls that should be addressed, please let me know ✏

Alberto Reyer

In the https://documentation.spryker.com/docs/data-processing-guidelines#common-table-expressions part there are CTE for insert and update which is suboptimal performance wise, because CTE’s will be written to the filesystem at least in Postgres (https://medium.com/@hakibenita/be-careful-with-cte-in-postgresql-fca5e24d2119).
I assume this is done to generate the id for inserted rows.

The same can be achieved using COALESCE and upsert.
We reduced the runtime of a few heavy importers a lot by this change, sorry that I don’t have concrete numbers, but give it a try for your own plain SQL based importers 😉

e.g.:

WITH records AS (
    SELECT
        COALESCE(t.id_<table>, nextval('<table>_pk_seq')) AS id_<table>,
        input."<field>" AS "<field>",
    FROM (
             SELECT
                 unnest(? :: VARCHAR []) AS "<field>"
         ) input
             LEFT JOIN <table> AS t ON (t."<field>" = input."field")
)
INSERT INTO <table> (
    id_<table>,
    "<field>"
) SELECT
      records.id_<table>,
      records."<field>
FROM records
ON CONFLICT ("<key>") DO UPDATE SET
  "<field>" = EXCLUDED."<field>"
RETURNING id_<table>;