You can translate the question and the replies:

Slow performance when joining tables from oracle database

Two issues related to vql performance. I am connecting to a oracle 12c database and trying to query a record from huge table. Denodo is not returning records . I am testing joining two oracle tables using the base views . The performance of the query is very slow in denodo express. If i execute the sqls above from an sql client its very fast . Is denodo trying to bring all data into DV layer and filtering? Or does it use the oracle parser and only brings the filtered record ?
user
18-11-2014 07:39:03 -0500

3 Answers

Hi! I have been able to successfully connect and execute queries on Oracle 12c using JDBC datasource and Oracle 11g Database Adapter. Is that what you are doing? Having a data source with huge number of records will not affect performance of your query because Denodo is able to push down filters to the underlying datas ource. This means that the Platform would bring the filtered record from Oracle. Can you paste here the exact query you are trying to execute? That might help. One possible reason for the execution time you are seeing for the second query has to do with the execution plan Denodo is using for the JOIN. Denodo supports ANSI standard SQL JOIN syntax, i.e, SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id; Are you using ANSI SQL or non ANSI SQL? In Oracle syntax, it is very common to have JOINS written as follows: SELECT * FROM table1, table2 WHERE table1.id=table2.id Which is non standard. When you execute such query in Denodo, the platform will execute it as a cartesian product. Try changing the SQL statement. Hope it helps!
Denodo Team
18-11-2014 10:36:59 -0500
The sql thats slow in execution in oracle 12c is SELECT * FROM customer_master WHERE customer_key = '1000011323' CONTEXT ('i18n'='gb') records are returned faster when the where condition is not there. This is a table loaded into memory in 12c. The join of the tables is created by denodo as inner join. i checked the vql and its inner join. is there any setup to mention that only filtered records to be passed to denodo layer.
user
23-11-2014 12:53:33 -0500
Can you take a look at the exact SQL query that Denodo is executing in the Oracle database in each case (with and without the filter)? To do so, you can check the instructions in the following KB document "Getting the SQL query delegated by a derived view": https://community.denodo.com/kb/view/document/Combining%20Data/Getting%20the%20SQL%20query%20delegated%20by%20a%20derived%20view On the other hand, the VQL will not tell you the exact execution plan that Denodo is using for the second query. I recommend you to take a look at the execution trace to have more details about it. You can do this in two ways: 1. Selecting (double click) the element where you want to perform the query (i.e. a base view) -> Click on "Execute" -> Make sure that the check-box "Execute with TRACE" is selected and click on "Execute" again-> Click on "View execution trace". 2. Using the VQL Shell (Tools -> VQL Shell). Write the query into the white box and write the reserved word TRACE at the end (example: "select * from my_view trace"). After the query is executed, the button "View execution trace" is enabled, so you can click on it and see the execution trace. In this way you can check the exact query that is being sent to Oracle and the processing (if any) that is being executed at the Denodo layer.
Denodo Team
28-11-2014 05:48:16 -0500
You must sign in to add an answer. If you do not have an account, you can register here