You can translate the question and the replies:

why the SQL's execution time is slower in Denodo than the time in Oracle?

Hi support team, We have a VQL which execution time is more than 10 seconds in VQL shell, while it's generated native SQL can be run in Oracle client in only 3 to 4 seconds. In VQL shell, the "Execution Trace" shows "Virtual Plan" takes more than 10 seconds. As I know in online document, "Virtual Plan: node that appears in the execution trace when the execution of a derived view is entirely pushed down to the data source." Could you please explain this issue? Is there any way we can tune the VQL? Thanks a lot.
user
22-10-2019 02:59:21 -0400

1 Answer

Hi, There are multiple things to consider while comparing the execution times from both the servers. * There can be network overhead, for example, the VDP server does not take much time to get the results, but while returning the results to the client application it might take a bit longer. An easy way to avoid such overhead is by using the CONTEXT clause cache_return_query_results SELECT * FROM oracle_customer CONTEXT('return_query_results' = 'false’). So you can execute the query with this context clause check the execution time. * Another thing to check will be where the Denodo VDP server geographically located, For example, the Oracle client may execute faster as it is run in the same server as the Database, whereas the Denodo server is a remote server here. Therefore, to have an unbiased comparison of the execution times it is necessary that both the clients are in same location. * Additionally, it will also depend on how many rows are you fetching in your base view. For example if you have 250,000 rows of data and if you keep the default fetch size as 1000 then it will take 250 network trips to get approximately 250,000 rows. Thus, if you set the fetch size of 2000, then it will take around 125 trips which are still not a significant change to reduce the execution time. You can change the fetch size to 5000 or 10000 which will reduce the network trips to around 25. But you must take into consideration the number of rows the database can support to be fetched at one time. You may have to do a trial and error to see which fetch size suits you the most. * Another thing to consider is if the oracle client is adding any LIMIT to the query, for example, sometimes the client fetches only 1000 rows of the query and Denodo fetches all the records. Hope this helps!
Denodo Team
24-10-2019 22:00:37 -0400
You must sign in to add an answer. If you do not have an account, you can register here