What are the Slack Archives?

It’s a history of our time together in the Slack Community! There’s a ton of knowledge in here, so feel free to search through the archives for a possible answer to your question.

Because this space is not active, you won’t be able to create a new post or comment here. If you have a question or want to start a discussion about something, head over to our categories and pick one to post in! You can always refer back to a post from Slack Archives if needed; just copy the link to use it as a reference..

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

Denis Turkov
Denis Turkov VP Architecture Sprykee Posts: 39 🏛 - Council (mod)

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 ✏

Comments

  • Alberto Reyer
    Alberto Reyer Lead Spryker Solution Architect / Technical Director Posts: 690 🪐 - Explorer
    edited July 2020

    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>;