[JBoss JIRA] (TEIID-5700) Dependent join back-off max ndv set to independent ndv estimate
by Steven Hawkins (Jira)
Steven Hawkins created TEIID-5700:
-------------------------------------
Summary: Dependent join back-off max ndv set to independent ndv estimate
Key: TEIID-5700
URL: https://issues.jboss.org/browse/TEIID-5700
Project: Teiid
Issue Type: Bug
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
The search being used to find a max ndv for the independent side is just setting the result to be the original ndv estimate for the independent side. This results in unnecessary back-offs of performing the dependent join.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 1 month
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Christoph John updated TEIID-5680:
----------------------------------
Attachment: svc-vdb.xml
> 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, svc-vdb.xml, 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=Amount...
> 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)
5 years, 1 month
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5680:
---------------------------------------
[^svc-vdb.xml]
> 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, svc-vdb.xml, 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=Amount...
> 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)
5 years, 1 month
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
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=Amount...
> 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)
5 years, 1 month
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5680:
---------------------------------------
Can you provide your VDB/DDL? There is a log message in the logfile that shows the dependent join is rejected at runtime for a very low number of rows - this wouldn't be expected unless the cardinality of the larger table were off.
> 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=Amount...
> 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)
5 years, 1 month
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Christoph John updated TEIID-5680:
----------------------------------
Attachment: logfile.txt
> 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=Amount...
> 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)
5 years, 1 month
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5680:
---------------------------------------
Hello Steven,
now some more details to the problem. In the meantime I have switch to Teiid 12.1 and also to Teiid-Designer 11.2.1. However, the problem with the query plan stays. My previous comment was not correct. The query which results into the issue is different and looks as follows:
GET Diary?$select=idDiaryEntry&$expand=fkDiaryToFDBProducts($select=idCode,product_name)&$skip=0&$top=100
If have attached the logfile with the following log level added:
<logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
Does this help you to see the problem or do you need further information?
[^logfile.txt]
> 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=Amount...
> 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)
5 years, 1 month
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5680 at 3/22/19 7:31 PM:
----------------------------------------------------------------
Hello Steven,
now some more details to the problem. In the meantime I have switched to Teiid 12.1 and also to Teiid-Designer 11.2.1. However, the problem with the query plan stays. My previous comment was not correct. The query which results into the issue is different and looks as follows:
GET Diary?$select=idDiaryEntry&$expand=fkDiaryToFDBProducts($select=idCode,product_name)&$skip=0&$top=100
If have attached the logfile with the following log level added:
<logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
Does this help you to see the problem or do you need further information?
[^logfile.txt]
was (Author: cjohn001):
Hello Steven,
now some more details to the problem. In the meantime I have switch to Teiid 12.1 and also to Teiid-Designer 11.2.1. However, the problem with the query plan stays. My previous comment was not correct. The query which results into the issue is different and looks as follows:
GET Diary?$select=idDiaryEntry&$expand=fkDiaryToFDBProducts($select=idCode,product_name)&$skip=0&$top=100
If have attached the logfile with the following log level added:
<logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
Does this help you to see the problem or do you need further information?
[^logfile.txt]
> 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=Amount...
> 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)
5 years, 1 month
[JBoss JIRA] (TEIID-5648) Hide metadata over odata
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5648?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5648:
---------------------------------------
> I thought you are trying to just hide the SYS and SYSADMIN schemas with this effort?
That was done with TEIID-5640
> I remember a conversation, in OData they explicitly avoided hiding the metadata from full form on a given schema.
This is to make the metadata visibility rules consistent between jdbc/pg and odata. In the other systems you can't even see schema/tables/procedures etc. that you don't have permission to do anything with. However this is not a good fit with the way the olingo code is structured and with the assumption that another layer will actually secure things and be responsible for exposing the relevant metadata.
So I'm introducing a formal permission to see all metadata, which will be of use for other use cases like our internal group that wants to have a "browser" role that can't actually query anything.
> Hide metadata over odata
> ------------------------
>
> Key: TEIID-5648
> URL: https://issues.jboss.org/browse/TEIID-5648
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.2
>
>
> All schemas, not marked as hidden, will be visible over odata. This includes all schema objects. Via the other access mechanisms permission is now required for visibility - TEIID-5516 and TEIID-2476.
> Alternatively there could also be an option to still expose the metadata by default for non-odata access even if the user is not permissioned.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 1 month