[teiid-issues] [JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
Christoph John (Jira)
issues at jboss.org
Fri Mar 22 21:30:00 EDT 2019
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13712411#comment-13712411 ]
Christoph John commented on TEIID-5680:
---------------------------------------
CREATE FOREIGN TABLE Account (
idProfile long NOT NULL AUTO_INCREMENT OPTIONS (ANNOTATION '', NAMEINSOURCE '`idProfile`', NATIVE_TYPE 'BIGINT'),
uuidUser string(36) NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`uuidUser`', NATIVE_TYPE 'CHAR'),
CONSTRAINT "PRIMARY" PRIMARY KEY(idProfile),
CONSTRAINT idProfile_UNIQUE UNIQUE(idProfile),
CONSTRAINT uuidUser_UNIQUE UNIQUE(uuidUser)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Account`', UPDATABLE TRUE, CARDINALITY 2);
CREATE FOREIGN TABLE Avatar (
fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`fkProfile`', NATIVE_TYPE 'BIGINT'),
AvatarImg blob(65535) OPTIONS (ANNOTATION '', NAMEINSOURCE '`AvatarImg`', NATIVE_TYPE 'BLOB'),
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),
CONSTRAINT fkProfile_UNIQUE UNIQUE(fkProfile),
CONSTRAINT fkAvatarToAccount FOREIGN KEY(fkProfile) REFERENCES Account (idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Avatar`', UPDATABLE TRUE, CARDINALITY 0);
CREATE FOREIGN TABLE BodyWeight (
idBodyWeight long NOT NULL AUTO_INCREMENT OPTIONS (ANNOTATION 'We need a surrogate key here as Teiid requires a primary key on each table. fkProfile is not unique here as we can will have multiple weight measurements per person. Moreover, combining it with WeightMeasurementDateTime makes the index to compute intensive.', NAMEINSOURCE '`idBodyWeight`', NATIVE_TYPE 'BIGINT'),
fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`fkProfile`', NATIVE_TYPE 'BIGINT'),
WeightMeasurementDateTime timestamp NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`WeightMeasurementDateTime`', NATIVE_TYPE 'DATETIME'),
Weight float NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`Weight`', NATIVE_TYPE 'FLOAT UNSIGNED'),
CONSTRAINT "PRIMARY" PRIMARY KEY(idBodyWeight),
CONSTRAINT idBodyWeight_UNIQUE UNIQUE(idBodyWeight),
CONSTRAINT fkBodyWeightToAccount FOREIGN KEY(fkProfile) REFERENCES Account (idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`BodyWeight`', UPDATABLE TRUE, CARDINALITY 1);
CREATE FOREIGN TABLE ConfigOptions (
fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`fkProfile`', NATIVE_TYPE 'BIGINT'),
MUnitWeight string(2) DEFAULT 'kg' OPTIONS (ANNOTATION '', NAMEINSOURCE '`MUnitWeight`', CHAR_OCTET_LENGTH 8, NATIVE_TYPE 'ENUM'),
MUnitLength string(2) DEFAULT 'cm' OPTIONS (ANNOTATION '', NAMEINSOURCE '`MUnitLength`', CHAR_OCTET_LENGTH 8, NATIVE_TYPE 'ENUM'),
MUnitEnergy string(4) DEFAULT 'kcal' OPTIONS (ANNOTATION '', NAMEINSOURCE '`MUnitEnergy`', CHAR_OCTET_LENGTH 16, NATIVE_TYPE 'ENUM'),
MUnitLiquids string(2) DEFAULT 'ml' OPTIONS (ANNOTATION '', NAMEINSOURCE '`MUnitLiquids`', CHAR_OCTET_LENGTH 8, NATIVE_TYPE 'ENUM'),
MUnitTime string(3) DEFAULT '24h' OPTIONS (ANNOTATION '', NAMEINSOURCE '`MUnitTime`', CHAR_OCTET_LENGTH 12, NATIVE_TYPE 'ENUM'),
EnableDrinkReminder boolean DEFAULT '0' OPTIONS (ANNOTATION '', NAMEINSOURCE '`EnableDrinkReminder`', NATIVE_TYPE 'BIT'),
PreferredCountryCodeForSearch string(2) DEFAULT 'de' OPTIONS (ANNOTATION '', NAMEINSOURCE '`PreferredCountryCodeForSearch`', CHAR_OCTET_LENGTH 8, NATIVE_TYPE 'VARCHAR'),
EnableInternationalSearch boolean DEFAULT '1' OPTIONS (ANNOTATION '', NAMEINSOURCE '`EnableInternationalSearch`', NATIVE_TYPE 'BIT'),
DaySpecificDietConfig boolean DEFAULT '0' OPTIONS (ANNOTATION '', NAMEINSOURCE '`DaySpecificDietConfig`', NATIVE_TYPE 'BIT'),
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),
CONSTRAINT fkProfile_UNIQUE UNIQUE(fkProfile),
CONSTRAINT fkConfigOptionsToAccount FOREIGN KEY(fkProfile) REFERENCES Account (idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`ConfigOptions`', UPDATABLE TRUE, CARDINALITY 2);
CREATE FOREIGN TABLE Diary (
idDiaryEntry long NOT NULL AUTO_INCREMENT OPTIONS (ANNOTATION '', NAMEINSOURCE '`idDiaryEntry`', NATIVE_TYPE 'BIGINT'),
fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`fkProfile`', NATIVE_TYPE 'BIGINT'),
AddedDateTime timestamp NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`AddedDateTime`', NATIVE_TYPE 'DATETIME'),
fkIdProductCode long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`fkIdProductCode`', NATIVE_TYPE 'BIGINT UNSIGNED'),
DatabaseID short NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`DatabaseID`', NATIVE_TYPE 'TINYINT UNSIGNED'),
MealNumber string(1) NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`MealNumber`', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'ENUM'),
AmountInG double NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`AmountInG`', NATIVE_TYPE 'DOUBLE'),
CONSTRAINT "PRIMARY" PRIMARY KEY(idDiaryEntry),
CONSTRAINT fkDiaryToFDBProducts FOREIGN KEY(fkIdProductCode) REFERENCES FDBProducts (idCode),
CONSTRAINT fkDiaryToAccount FOREIGN KEY(fkProfile) REFERENCES Account (idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Diary`', UPDATABLE TRUE, CARDINALITY 4);
CREATE FOREIGN TABLE FDBProducts (
idCode long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`idCode`', NATIVE_TYPE 'BIGINT UNSIGNED'),
lc string(5) OPTIONS (ANNOTATION '', NAMEINSOURCE '`lc`', CHAR_OCTET_LENGTH 20, NATIVE_TYPE 'CHAR'),
product_name string(256) OPTIONS (ANNOTATION '', NAMEINSOURCE '`product_name`', CHAR_OCTET_LENGTH 1024, NATIVE_TYPE 'VARCHAR'),
origins string(512) OPTIONS (ANNOTATION '', NAMEINSOURCE '`origins`', CHAR_OCTET_LENGTH 2048, NATIVE_TYPE 'VARCHAR'),
brands string(384) OPTIONS (ANNOTATION '', NAMEINSOURCE '`brands`', CHAR_OCTET_LENGTH 1536, NATIVE_TYPE 'VARCHAR'),
quantity string(256) OPTIONS (ANNOTATION '', NAMEINSOURCE '`quantity`', CHAR_OCTET_LENGTH 1024, NATIVE_TYPE 'VARCHAR'),
nova_group string(1) OPTIONS (ANNOTATION '', NAMEINSOURCE '`nova_group`', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'CHAR'),
nutrition_grade_fr string(1) OPTIONS (ANNOTATION '', NAMEINSOURCE '`nutrition_grade_fr`', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'CHAR'),
energy_100g double OPTIONS (ANNOTATION '', NAMEINSOURCE '`energy_100g`', NATIVE_TYPE 'DOUBLE'),
carbohydrates_100g double OPTIONS (ANNOTATION '', NAMEINSOURCE '`carbohydrates_100g`', NATIVE_TYPE 'DOUBLE'),
sugars_100g double OPTIONS (ANNOTATION '', NAMEINSOURCE '`sugars_100g`', NATIVE_TYPE 'DOUBLE'),
proteins_100g double OPTIONS (ANNOTATION '', NAMEINSOURCE '`proteins_100g`', NATIVE_TYPE 'DOUBLE'),
fat_100g double OPTIONS (ANNOTATION '', NAMEINSOURCE '`fat_100g`', NATIVE_TYPE 'DOUBLE'),
saturated_fat_100g double OPTIONS (ANNOTATION '', NAMEINSOURCE '`saturated_fat_100g`', NATIVE_TYPE 'DOUBLE'),
saturated_fat_modifier string(3) OPTIONS (ANNOTATION '', NAMEINSOURCE '`saturated_fat_modifier`', CHAR_OCTET_LENGTH 12, NATIVE_TYPE 'CHAR'),
salt_100g double OPTIONS (ANNOTATION '', NAMEINSOURCE '`salt_100g`', NATIVE_TYPE 'DOUBLE'),
sodium_100g double OPTIONS (ANNOTATION '', NAMEINSOURCE '`sodium_100g`', NATIVE_TYPE 'DOUBLE'),
CONSTRAINT "PRIMARY" PRIMARY KEY(idCode),
CONSTRAINT code_UNIQUE UNIQUE(idCode)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`FDBProducts`', UPDATABLE TRUE, CARDINALITY 626285);
CREATE FOREIGN TABLE MealConfigs (
fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`fkProfile`', NATIVE_TYPE 'BIGINT'),
Weekday string(1) NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`Weekday`', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'ENUM'),
MealNumber string(1) NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`MealNumber`', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'ENUM'),
WeekdaySortOrder long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`WeekdaySortOrder`', NATIVE_TYPE 'INT UNSIGNED'),
MealName string(45) DEFAULT 'strMeal' OPTIONS (ANNOTATION '', NAMEINSOURCE '`MealName`', CHAR_OCTET_LENGTH 180, NATIVE_TYPE 'VARCHAR'),
MealEnabled boolean OPTIONS (ANNOTATION '', NAMEINSOURCE '`MealEnabled`', NATIVE_TYPE 'BIT'),
MealStartTime time OPTIONS (ANNOTATION '', NAMEINSOURCE '`MealStartTime`', NATIVE_TYPE 'TIME'),
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile, Weekday, MealNumber),
CONSTRAINT fkMealConfigToAccount FOREIGN KEY(fkProfile) REFERENCES Account (idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`MealConfigs`', UPDATABLE TRUE, CARDINALITY 2);
CREATE FOREIGN TABLE Profile (
fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`fkProfile`', NATIVE_TYPE 'BIGINT'),
BodyHeight float DEFAULT '0.000' OPTIONS (ANNOTATION '', NAMEINSOURCE '`BodyHeight`', NATIVE_TYPE 'FLOAT UNSIGNED'),
GoalWeight float DEFAULT '0.000' OPTIONS (ANNOTATION '', NAMEINSOURCE '`GoalWeight`', NATIVE_TYPE 'FLOAT UNSIGNED'),
Gender string(1) DEFAULT 'm' OPTIONS (ANNOTATION '', NAMEINSOURCE '`Gender`', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'ENUM'),
BirthDate date OPTIONS (ANNOTATION '', NAMEINSOURCE '`BirthDate`', NATIVE_TYPE 'DATE'),
ActivityLevel string(17) DEFAULT 'strActive' OPTIONS (ANNOTATION '', NAMEINSOURCE '`ActivityLevel`', CHAR_OCTET_LENGTH 68, NATIVE_TYPE 'ENUM'),
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),
CONSTRAINT fkProfile_UNIQUE UNIQUE(fkProfile),
CONSTRAINT fkProfileToAccount FOREIGN KEY(fkProfile) REFERENCES Account (idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Profile`', UPDATABLE TRUE, CARDINALITY 2);
> Improve performance of odata expand operations
> ----------------------------------------------
>
> Key: TEIID-5680
> URL: https://issues.jboss.org/browse/TEIID-5680
> Project: Teiid
> Issue Type: Enhancement
> Components: OData
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
> Attachments: logfile.txt, test2.txt
>
>
> Hello Ramesh and Steven,
> this is a follow up regarding an observation in the discussion from TEIID-5643. I thought I open an extra issue for the topic as this seems not to be related to TEIID-5500.
> As you already know, I am using SAPUI5 as frontend for ODATA requests. SAPUI5 supports binding of a user interface control group (like a list with its list items) to a single ODATA path at a time only. If the control group items require additional information which is stored in a different table in the database, I have to expand those parameters in the odata query.
> When doing so, I am running in a serious performance issue with TEIID, which would render the approach of using sapui5 with Teiid infeasible if we cannot find a way to speedup the issue. At the moment I have a small table with entries (table Diary with about 20 records) for which the query extracts several items (just a single one in the example given below). Now the filtered item is expanded with data from a larger table in the database (FDBProducts with about 680.000 records). The whole query takes about 15s to be processed. The query is given as:
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=AmountInG,idDiaryEntry&$expand=fkDiaryToFDBProducts($select=brands,energy_100g,idCode,product_name)&$filter=AddedDateTime%20ge%202019-03-06T00:00:00%2B01:00%20and%20AddedDateTime%20le%202019-03-07T00:00:00%2B01:00%20and%20MealNumber%20eq%20%270%27&$skip=0&$top=100
> I checked the output when using
> <logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
> This shows the problem. It seems the join operation is not pushed down to the database but the data are rather joined within Teiid. Teiid therefore downloads the entire dataset of the large FDBProducts table, which makes the expand approach infeasible for real world datasets with a certain size. So my question is, if you can modify Teiid to push down the entire join operation to the underlaying database (I assume this would be the most efficient approach), or alternatively query just the items from the table to be joined which where filtered from the first table if the first option is not possible?
> Thanks for your help.
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
More information about the teiid-issues
mailing list