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
is in case:
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:
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
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)
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'
)
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:
- 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)
- 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
Comment