[JBoss JIRA] (TEIID-5903) Performance problems
by Renat Eskenin (Jira)
Renat Eskenin created TEIID-5903:
------------------------------------
Summary: Performance problems
Key: TEIID-5903
URL: https://issues.redhat.com/browse/TEIID-5903
Project: Teiid
Issue Type: Enhancement
Components: Salesforce Connector
Affects Versions: 13.0.2
Reporter: Renat Eskenin
Assignee: Steven Hawkins
If we call SOQL
{code:sql}
SELECT Account.BillingCountry__c, Account.Name from Contact LIMIT 1
{code}
We have time in salesforce workbench (UI for SOAP): Returned records 1 - 1 of 1 total record in 0.947 seconds:
If we call SQL over teiid spring salesforce example:
{code:sql}
SELECT Account.BillingCountry__c, Account.Name from Contact LEFT OUTER JOIN /*+ MAKEDEP */ Account
on Contact.AccountId = Account.id
LIMIT 1
{code}
we have request time: Time from sf:3.561s
How we can optimize request time?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months
[JBoss JIRA] (TEIID-5860) Salesforce translator should support self joins
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-5860?page=com.atlassian.jira.plugi... ]
Renat Eskenin commented on TEIID-5860:
--------------------------------------
And additional question.teiid-spring-boot-starter updated in weekly builds too?
> Salesforce translator should support self joins
> -----------------------------------------------
>
> Key: TEIID-5860
> URL: https://issues.redhat.com/browse/TEIID-5860
> Project: Teiid
> Issue Type: Enhancement
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 13.1
>
> Original Estimate: 3 hours
> Time Spent: 5 hours, 30 minutes
> Remaining Estimate: 0 minutes
>
> In this query
> {code}
> SELECT originalLicense.Id,originalLicense.Status__c,originalLicense.ReplacementLicenseID__c,originalLicense.LicenseId__c,originalLicense.vlic_Contract_Number__c,replacementLicense.LicenseId__c as replacementLicenseId ,replacementLicense.LicenseId__c as replacementLicenseLicenseId FROM Product_License__c originalLicense LEFT OUTER JOIN /*+ MAKEDEP */ Product_License__c replacementLicense ON originalLicense.ReplacementLicenseID__c = replacementLicense.Id where originalLicense.vlic_Contract_Number__c = :contractNumber
> {code}
> We have two requests to salesforce, but could generate a single SOQL query.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months
[JBoss JIRA] (TEIID-5860) Salesforce translator should support self joins
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-5860?page=com.atlassian.jira.plugi... ]
Renat Eskenin commented on TEIID-5860:
--------------------------------------
Sorry, but where is weekly builds? Yes of course i can build sources, but we need maven dependency :)
Tnx!
> Salesforce translator should support self joins
> -----------------------------------------------
>
> Key: TEIID-5860
> URL: https://issues.redhat.com/browse/TEIID-5860
> Project: Teiid
> Issue Type: Enhancement
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 13.1
>
> Original Estimate: 3 hours
> Time Spent: 5 hours, 30 minutes
> Remaining Estimate: 0 minutes
>
> In this query
> {code}
> SELECT originalLicense.Id,originalLicense.Status__c,originalLicense.ReplacementLicenseID__c,originalLicense.LicenseId__c,originalLicense.vlic_Contract_Number__c,replacementLicense.LicenseId__c as replacementLicenseId ,replacementLicense.LicenseId__c as replacementLicenseLicenseId FROM Product_License__c originalLicense LEFT OUTER JOIN /*+ MAKEDEP */ Product_License__c replacementLicense ON originalLicense.ReplacementLicenseID__c = replacementLicense.Id where originalLicense.vlic_Contract_Number__c = :contractNumber
> {code}
> We have two requests to salesforce, but could generate a single SOQL query.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months
[JBoss JIRA] (TEIID-5900) query with local and remote pushes too much to remote
by Ken Geis (Jira)
[ https://issues.redhat.com/browse/TEIID-5900?page=com.atlassian.jira.plugi... ]
Ken Geis commented on TEIID-5900:
---------------------------------
I made the local table dependent with the MAKEDEP option, and that works. It does not make it dependent if I use the MAKEDEP hint in the FROM clause; only if I put it in the OPTION clause.
> query with local and remote pushes too much to remote
> -----------------------------------------------------
>
> Key: TEIID-5900
> URL: https://issues.redhat.com/browse/TEIID-5900
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.3.1
> Reporter: Ken Geis
> Assignee: Steven Hawkins
> Priority: Major
>
> I have a table "A" in an Oracle database. I have a date dimension "B" in Teiid, which is a materialized view of a recursive query. The date dimension has all dates from 1950 to 2050, 36890 rows in total.
> I run the following query:
> {code:sql}
> SELECT *
> FROM a
> JOIN b
> ON a.date_column = b.date_key
> {code}
> Teiid sends a query to Oracle, which I will paraphrase as:
> {code:sql}
> SELECT <columns>
> FROM a
> WHERE date_column IN (to_date('1950-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
> <36899 other date values!>)
> {code}
> Oracle breaks either with the length of the query or the number of options in the IN clause.
> I thought that MAKEDEP/MAKENOTDEP/MAKEIND could help, but they didn't seem to change anything.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months
[JBoss JIRA] (TEIID-5860) Salesforce translator should support self joins
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5860?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIID-5860:
---------------------------------------
There is a weekly snapshot build. The job runs on Wednesdays. It looks like it already ran before this commit was in. You can always build from source as well.
> Salesforce translator should support self joins
> -----------------------------------------------
>
> Key: TEIID-5860
> URL: https://issues.redhat.com/browse/TEIID-5860
> Project: Teiid
> Issue Type: Enhancement
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 13.1
>
> Original Estimate: 3 hours
> Time Spent: 5 hours, 30 minutes
> Remaining Estimate: 0 minutes
>
> In this query
> {code}
> SELECT originalLicense.Id,originalLicense.Status__c,originalLicense.ReplacementLicenseID__c,originalLicense.LicenseId__c,originalLicense.vlic_Contract_Number__c,replacementLicense.LicenseId__c as replacementLicenseId ,replacementLicense.LicenseId__c as replacementLicenseLicenseId FROM Product_License__c originalLicense LEFT OUTER JOIN /*+ MAKEDEP */ Product_License__c replacementLicense ON originalLicense.ReplacementLicenseID__c = replacementLicense.Id where originalLicense.vlic_Contract_Number__c = :contractNumber
> {code}
> We have two requests to salesforce, but could generate a single SOQL query.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months
[JBoss JIRA] (TEIID-5900) query with local and remote pushes too much to remote
by Ken Geis (Jira)
[ https://issues.redhat.com/browse/TEIID-5900?page=com.atlassian.jira.plugi... ]
Ken Geis commented on TEIID-5900:
---------------------------------
I have not overridden any settings.
I am using the [19.3.0.0 driver|https://search.maven.org/artifact/com.oracle.ojdbc/ojdbc8/19.3.0.0...].
You're right. It's creating 37 OR branches with 1000 values in each. I get this error:
{noformat}
2020-02-12 20:11:21.451 WARN 1 --- [ssorQueue766010] com.zaxxer.hikari.pool.ProxyConnection : HikariPool-1 - Connection oracl
e.jdbc.driver.T4CConnection@7f87b585 marked as broken because of SQLSTATE(08000), ErrorCode(17410)
java.sql.SQLRecoverableException: No more data to read from socket
{noformat}
I'd like to prevent it from pushing down these criteria. They are unnecessary because they will match all rows of the target table.
> query with local and remote pushes too much to remote
> -----------------------------------------------------
>
> Key: TEIID-5900
> URL: https://issues.redhat.com/browse/TEIID-5900
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.3.1
> Reporter: Ken Geis
> Assignee: Steven Hawkins
> Priority: Major
>
> I have a table "A" in an Oracle database. I have a date dimension "B" in Teiid, which is a materialized view of a recursive query. The date dimension has all dates from 1950 to 2050, 36890 rows in total.
> I run the following query:
> {code:sql}
> SELECT *
> FROM a
> JOIN b
> ON a.date_column = b.date_key
> {code}
> Teiid sends a query to Oracle, which I will paraphrase as:
> {code:sql}
> SELECT <columns>
> FROM a
> WHERE date_column IN (to_date('1950-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
> <36899 other date values!>)
> {code}
> Oracle breaks either with the length of the query or the number of options in the IN clause.
> I thought that MAKEDEP/MAKENOTDEP/MAKEIND could help, but they didn't seem to change anything.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months
[JBoss JIRA] (TEIID-5860) Salesforce translator should support self joins
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-5860?page=com.atlassian.jira.plugi... ]
Renat Eskenin commented on TEIID-5860:
--------------------------------------
Hi! Thanks! Can i find this commit builded in maven as dependency?
> Salesforce translator should support self joins
> -----------------------------------------------
>
> Key: TEIID-5860
> URL: https://issues.redhat.com/browse/TEIID-5860
> Project: Teiid
> Issue Type: Enhancement
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 13.1
>
> Original Estimate: 3 hours
> Time Spent: 5 hours, 30 minutes
> Remaining Estimate: 0 minutes
>
> In this query
> {code}
> SELECT originalLicense.Id,originalLicense.Status__c,originalLicense.ReplacementLicenseID__c,originalLicense.LicenseId__c,originalLicense.vlic_Contract_Number__c,replacementLicense.LicenseId__c as replacementLicenseId ,replacementLicense.LicenseId__c as replacementLicenseLicenseId FROM Product_License__c originalLicense LEFT OUTER JOIN /*+ MAKEDEP */ Product_License__c replacementLicense ON originalLicense.ReplacementLicenseID__c = replacementLicense.Id where originalLicense.vlic_Contract_Number__c = :contractNumber
> {code}
> We have two requests to salesforce, but could generate a single SOQL query.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months
[JBoss JIRA] (TEIID-5900) query with local and remote pushes too much to remote
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5900?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIID-5900:
---------------------------------------
What oracle driver version and what exactly is the error message?
The oracle translator defaults to supporting 1000 values per IN (MaxInCriteriaSize), and up to 50 IN predicates (MaxDependentInPredicates). Unless you have overriden it's settings, it should not construct a single IN of over 1000 values.
> query with local and remote pushes too much to remote
> -----------------------------------------------------
>
> Key: TEIID-5900
> URL: https://issues.redhat.com/browse/TEIID-5900
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.3.1
> Reporter: Ken Geis
> Assignee: Steven Hawkins
> Priority: Major
>
> I have a table "A" in an Oracle database. I have a date dimension "B" in Teiid, which is a materialized view of a recursive query. The date dimension has all dates from 1950 to 2050, 36890 rows in total.
> I run the following query:
> {code:sql}
> SELECT *
> FROM a
> JOIN b
> ON a.date_column = b.date_key
> {code}
> Teiid sends a query to Oracle, which I will paraphrase as:
> {code:sql}
> SELECT <columns>
> FROM a
> WHERE date_column IN (to_date('1950-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
> <36899 other date values!>)
> {code}
> Oracle breaks either with the length of the query or the number of options in the IN clause.
> I thought that MAKEDEP/MAKENOTDEP/MAKEIND could help, but they didn't seem to change anything.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months
[JBoss JIRA] (TEIID-5860) Salesforce translator should support self joins
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5860?focusedWorklogId=12450095&pag... ]
Steven Hawkins logged work on TEIID-5860:
-----------------------------------------
Author: Steven Hawkins
Created on: 12/Feb/20 2:15 PM
Start Date: 12/Feb/20 2:15 PM
Worklog Time Spent: 2 hours, 30 minutes
Issue Time Tracking
-------------------
Time Spent: 5 hours, 30 minutes (was: 3 hours)
Worklog Id: (was: 12450095)
> Salesforce translator should support self joins
> -----------------------------------------------
>
> Key: TEIID-5860
> URL: https://issues.redhat.com/browse/TEIID-5860
> Project: Teiid
> Issue Type: Enhancement
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 13.1
>
> Original Estimate: 3 hours
> Time Spent: 5 hours, 30 minutes
> Remaining Estimate: 0 minutes
>
> In this query
> {code}
> SELECT originalLicense.Id,originalLicense.Status__c,originalLicense.ReplacementLicenseID__c,originalLicense.LicenseId__c,originalLicense.vlic_Contract_Number__c,replacementLicense.LicenseId__c as replacementLicenseId ,replacementLicense.LicenseId__c as replacementLicenseLicenseId FROM Product_License__c originalLicense LEFT OUTER JOIN /*+ MAKEDEP */ Product_License__c replacementLicense ON originalLicense.ReplacementLicenseID__c = replacementLicense.Id where originalLicense.vlic_Contract_Number__c = :contractNumber
> {code}
> We have two requests to salesforce, but could generate a single SOQL query.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months
[JBoss JIRA] (TEIID-5860) Salesforce translator should support self joins
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5860?page=com.atlassian.jira.plugi... ]
Steven Hawkins resolved TEIID-5860.
-----------------------------------
Resolution: Done
Changed the visitor and execution logic to be aware of the difference between the left and right (parent / child) tables. Generally this means using an updated version of NamedTable that supports equality checks, rather than just the Table metadata record.
> Salesforce translator should support self joins
> -----------------------------------------------
>
> Key: TEIID-5860
> URL: https://issues.redhat.com/browse/TEIID-5860
> Project: Teiid
> Issue Type: Enhancement
> Components: Salesforce Connector
> Reporter: Renat Eskenin
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 13.1
>
> Original Estimate: 3 hours
> Time Spent: 3 hours
> Remaining Estimate: 0 minutes
>
> In this query
> {code}
> SELECT originalLicense.Id,originalLicense.Status__c,originalLicense.ReplacementLicenseID__c,originalLicense.LicenseId__c,originalLicense.vlic_Contract_Number__c,replacementLicense.LicenseId__c as replacementLicenseId ,replacementLicense.LicenseId__c as replacementLicenseLicenseId FROM Product_License__c originalLicense LEFT OUTER JOIN /*+ MAKEDEP */ Product_License__c replacementLicense ON originalLicense.ReplacementLicenseID__c = replacementLicense.Id where originalLicense.vlic_Contract_Number__c = :contractNumber
> {code}
> We have two requests to salesforce, but could generate a single SOQL query.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
4 years, 10 months