Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

  • batchUploadOperationId in ds.xml to define the operationBinding to use with BatchUploader OR automatic outputs="primaryKeyfieldName" for this query for performace reasons

    Hi Isomorphic,

    the idea comes from here, but now I have another important reason for it.

    My suggestion was/is: Could you add an attribute (now named batchUploadOperationId) that is only used for the Text-to-ID lookup query of the BatchUploader.
    The use case was to limit e.g. the language for a user to uploaded to enabled languages only.
    Or the country for a customer to enabled countries only. Here I needed this in order to inject another WHERE-condition in the lookup SQL.

    While I still think this would be a very good feature, I now also have a performance problem here:

    I have a case where I'll need to upload ~23000 entries to an easy table every week from a csv (after purging the table, contains updated data for this masterdata table).
    The data also includes a country (meaning name->ID lookup).
    My country table itself has many includeFrom fields (currently linking to two tables, soon three tables). A lookup, that should basically only do
    Code:
    SELECT id FROM country WHERE name='Germany'; --(id is primaryKey of the country table and linked as foreignKey from somewhere else)
    is in case:
    Code:
    SELECT
        t_country.id,
        t_country.tenant_id,
        t_country.created_by,
        t_country.created_at,
        t_country.modified_by,
        t_country.modified_at,
        t_country.available,
        t_country.post_de,
        t_country.fullname_orig,
        t_country.iso_3166_1_alpha_2,
        t_country.iso_3166_1_alpha_2_ccplus,
        t_country.iso_3166_1_alpha_3,
        t_country.lp_codes,
        t_country.callingcode,
        t_country.name,
        name
        || ' (+'
        || callingcode
        || ')' AS name_ccplus,
        translation.gettranslation(t_country.tenant_id,'T_COUNTRY',t_country.id,'NAME','it') AS name_translated,
        translation.gettranslation(t_country.tenant_id,'T_COUNTRY',t_country.id,'NAME','it')
        || ' (+'
        || callingcode
        || ')' AS name_translated_ccplus,
        t_country.fullname,
        translation.gettranslation(t_country.tenant_id,'T_COUNTRY',t_country.id,'NAME','de') AS name_de,
        translation.gettranslation(t_country.tenant_id,'T_COUNTRY',t_country.id,'NAME','en') AS name_en,
        user_created_by.gender_shortname AS created_by_gender_shortname,
        user_created_by.givenname AS created_by_givenname,
        user_created_by.surname AS created_by_surname,
        user_created_by.fullname_initials_givenname AS created_by_name,
        user_created_by.fullname AS created_by_fullname,
        user_created_by.fullname_legalentityname AS created_by_name_tooltip,
        user_created_by.legalentityname AS created_by_legalentityname,
        user_created_by.email_use AS created_by_email,
        user_created_by.emailentry_use AS created_by_emailentry,
        user_modified_by.fullname_initials_givenname AS modified_by_name,
        user_modified_by.fullname_legalentityname AS modified_by_name_tooltip,
        user_modified_by.legalentityname AS modified_by_legalentityname
    FROM
        t_country
        JOIN v_user_data user_created_by ON t_country.created_by = user_created_by.id
        JOIN v_user_data user_modified_by ON t_country.modified_by = user_modified_by.id
    WHERE
        (
            lower(t_country.iso_3166_1_alpha_2) = 'ch'
        )
    As you can see it includes MANY fields that make sense in a GUI (the modified by/at), but not in a text-to-ID lookup.
    In reality I'd only need the id-field and no joins. This would it obviously also make it easier for the DB to use existing indexes and perhaps answer that query index-only.
    While this does not matter for a single GUI fetchMissingValueReply, it does matter for 23k queries.

    As of today I'd have to modify the default fetch for this, which definitely is not a good idea.

    I do see two possible solutions:
    1. The suggestion from above where I define an operationBinding for this and set outputs="id", getting rid of all the unnecessary joins and fields (and possible a WHERE clause, as originally intended)
    2. You do an automatic outputs="primaryKeyFieldname" for the lookup query

    Of course, I still favor the 1st solution, as this has also the benefit of an improvement of my application (the additional WHERE clause). What do you think?

    Best regards
    Blama

  • #2
    It seems like the problem here is really that you've customized the default fetch to be something rather expensive. This isn't an area where we think we need new framework features. Feature Sponsorship is still possible here for this little micro-feature if you are really in a bind.

    Comment


    • #3
      Hi Isomorphic,

      yes, I'd really appreciate if you could build batchUploadOperationId. Can this go into 6.1? Can you send me a quote for this?

      Thank you & Best regards
      Blama

      Comment

      Working...
      X