[JBoss JIRA] (TEIID-5903) Performance problems
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-5903?page=com.atlassian.jira.plugi... ]
Renat Eskenin commented on TEIID-5903:
--------------------------------------
Can we use wildfly embeded as container for teiid with connection pool?
> 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
> Priority: Major
>
> 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?
> We used
> {code}
> @Autowired
> private DataSource sfds;
> @Bean
> @Primary
> public NamedParameterJdbcTemplate getSfNamedParameterJdbcTemplate() {
> return new NamedParameterJdbcTemplate(sfds);
> }
> {code}
> And
> {code}
> @Autowired
> private NamedParameterJdbcTemplate sftemplate;
> long start = System.currentTimeMillis();
> SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, params);
> log.info("Time from {}:{}", sourceName, (((double) (System.currentTimeMillis() - start)) / 1000.0d));
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[JBoss JIRA] (TEIID-5903) Performance problems
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-5903?page=com.atlassian.jira.plugi... ]
Renat Eskenin commented on TEIID-5903:
--------------------------------------
Please speedup developing of connection pool, it is root of current problems with this cool product for salesforce connection.
Tnx! :)
> 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
> Priority: Major
>
> 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?
> We used
> {code}
> @Autowired
> private DataSource sfds;
> @Bean
> @Primary
> public NamedParameterJdbcTemplate getSfNamedParameterJdbcTemplate() {
> return new NamedParameterJdbcTemplate(sfds);
> }
> {code}
> And
> {code}
> @Autowired
> private NamedParameterJdbcTemplate sftemplate;
> long start = System.currentTimeMillis();
> SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, params);
> log.info("Time from {}:{}", sourceName, (((double) (System.currentTimeMillis() - start)) / 1000.0d));
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[JBoss JIRA] (TEIID-5903) Performance problems
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5903?page=com.atlassian.jira.plugi... ]
Steven Hawkins commented on TEIID-5903:
---------------------------------------
No, teiid embedded requires wiring everything on your own including connection pools. Only teiid wildfly has built-in support currently.
> 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
> Priority: Major
>
> 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?
> We used
> {code}
> @Autowired
> private DataSource sfds;
> @Bean
> @Primary
> public NamedParameterJdbcTemplate getSfNamedParameterJdbcTemplate() {
> return new NamedParameterJdbcTemplate(sfds);
> }
> {code}
> And
> {code}
> @Autowired
> private NamedParameterJdbcTemplate sftemplate;
> long start = System.currentTimeMillis();
> SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, params);
> log.info("Time from {}:{}", sourceName, (((double) (System.currentTimeMillis() - start)) / 1000.0d));
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[JBoss JIRA] (TEIID-5903) Performance problems
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIID-5903?page=com.atlassian.jira.plugi... ]
Renat Eskenin commented on TEIID-5903:
--------------------------------------
If i use teiid embed (without spring boot) this version have pool of connection?
> 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
> Priority: Major
>
> 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?
> We used
> {code}
> @Autowired
> private DataSource sfds;
> @Bean
> @Primary
> public NamedParameterJdbcTemplate getSfNamedParameterJdbcTemplate() {
> return new NamedParameterJdbcTemplate(sfds);
> }
> {code}
> And
> {code}
> @Autowired
> private NamedParameterJdbcTemplate sftemplate;
> long start = System.currentTimeMillis();
> SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, params);
> log.info("Time from {}:{}", sourceName, (((double) (System.currentTimeMillis() - start)) / 1000.0d));
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[JBoss JIRA] (TEIIDSB-163) Provide pooling for BaseConnectionFactory sources
by Renat Eskenin (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-163?page=com.atlassian.jira.plug... ]
Renat Eskenin commented on TEIIDSB-163:
---------------------------------------
When this functionality will be implemented? Current requests is very sloooow :(
> Provide pooling for BaseConnectionFactory sources
> -------------------------------------------------
>
> Key: TEIIDSB-163
> URL: https://issues.redhat.com/browse/TEIIDSB-163
> Project: Teiid Spring Boot
> Issue Type: Feature Request
> Components: datasource
> Reporter: Steven Hawkins
> Priority: Major
> Fix For: 1.4.0
>
>
> It will depend on the source and the cost of creating a connection, but in general we should provide pooling for connections rather than creating the for every operation. For example salesforce is a heavy weight connection.
> The pooling will need to be tied into spring security or jca like logic to properly manage connections by identity once we re-establish that logic.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[JBoss JIRA] (TEIID-5903) Performance problems
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5903?page=com.atlassian.jira.plugi... ]
Steven Hawkins edited comment on TEIID-5903 at 2/13/20 10:18 AM:
-----------------------------------------------------------------
makedep is not necessary here as this will pushdown as a single query. Your total time includes planning and establishing a connection. Teiid spring boot does not have pooling for non-jdbc connections TEIIDSB-163, so you will incur that connection cost on each query after TEIIDSB-158. You can eliminate the planning time (which should be very small) by using a prepared statement.
was (Author: shawkins):
makedep is not necessary here as this will pushdown as a single query. Your total time includes planning and establishing a connection. Teiid spring boot does not have pooling for non-jdbc connections TEIIDTSB-163, so you will incur that connection cost on each query after TEIIDSB-158. You can eliminate the planning time (which should be very small) by using a prepared statement.
> 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
> Priority: Major
>
> 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?
> We used
> {code}
> @Autowired
> private DataSource sfds;
> @Bean
> @Primary
> public NamedParameterJdbcTemplate getSfNamedParameterJdbcTemplate() {
> return new NamedParameterJdbcTemplate(sfds);
> }
> {code}
> And
> {code}
> @Autowired
> private NamedParameterJdbcTemplate sftemplate;
> long start = System.currentTimeMillis();
> SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, params);
> log.info("Time from {}:{}", sourceName, (((double) (System.currentTimeMillis() - start)) / 1000.0d));
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[JBoss JIRA] (TEIID-5903) Performance problems
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIID-5903?page=com.atlassian.jira.plugi... ]
Steven Hawkins resolved TEIID-5903.
-----------------------------------
Resolution: Explained
makedep is not necessary here as this will pushdown as a single query. Your total time includes planning and establishing a connection. Teiid spring boot does not have pooling for non-jdbc connections TEIIDTSB-163, so you will incur that connection cost on each query after TEIIDSB-158. You can eliminate the planning time (which should be very small) by using a prepared statement.
> 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
> Priority: Major
>
> 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?
> We used
> {code}
> @Autowired
> private DataSource sfds;
> @Bean
> @Primary
> public NamedParameterJdbcTemplate getSfNamedParameterJdbcTemplate() {
> return new NamedParameterJdbcTemplate(sfds);
> }
> {code}
> And
> {code}
> @Autowired
> private NamedParameterJdbcTemplate sftemplate;
> long start = System.currentTimeMillis();
> SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, params);
> log.info("Time from {}:{}", sourceName, (((double) (System.currentTimeMillis() - start)) / 1000.0d));
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[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:
---------------------------------------
So there are a couple of things to unpack here:
1. Is the creation of the dependent join from the local table to the remote an appropriate default plan by the optimizer - can you provide the debug plan and relevant ddl? We need to confirm what the planner sees as the source row count.
2. 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. - can you provide the sql you are executing?
3. The exception you are seeing from oracle seems to be related to the connection being closed and doesn't seem to be the root error. We could look more at finding the underlying problem.
> 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)
6 years, 1 month
[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:
---------------------------------------
> Sorry, but where is weekly builds?
They are in the oss central snapshot repo, the download link is on the downloads page under unstable: http://teiid.io/teiid_runtimes/teiid_wildfly/downloads/
> Yes of course i can build sources, but we need maven dependency
Once built you will have a local snapshot to test against.
> And additional question.teiid-spring-boot-starter updated in weekly builds too?
A new snapshot should be built with every commit.
> 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)
6 years, 1 month
[JBoss JIRA] (TEIIDSB-159) Test errors
by Steven Hawkins (Jira)
[ https://issues.redhat.com/browse/TEIIDSB-159?page=com.atlassian.jira.plug... ]
Steven Hawkins resolved TEIIDSB-159.
------------------------------------
Assignee: Steven Hawkins
Resolution: Done
Updated the logic to hold a reference to the root virtual file for the vdb.
> Test errors
> -----------
>
> Key: TEIIDSB-159
> URL: https://issues.redhat.com/browse/TEIIDSB-159
> Project: Teiid Spring Boot
> Issue Type: Bug
> Components: core
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 1.4.0
>
> Original Estimate: 5 hours
> Time Spent: 2 hours
> Remaining Estimate: 3 hours
>
> The Teiid spring boot tests are commonly erroring out in travis. It usually is reported as Failed To Load Application Context - but the underlying problem differs. Here: https://api.travis-ci.org/v3/job/638096554/log.txt the issue is likely that tests are executing concurrently that reference the same vdb. The current NioVirtualFile logic for Teiid expects only one mount for a given zip. If you open another, then the previous is closed.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month