Dremio slower than SQL Developer?

1

I translated my query on my Oracle DB to Dremio expecting that it would be faster (or at least as fast), but in SQL developer it takes 33 seconds and in Dremio 3 minutes. Wat could be the problem?

2

Hi @h4nneke,

Can you provide a profile for the query in question? This would help use understand where the time is spent.

3

This is the query profile.

10c68e90-246a-4725-8060-79bab0a88995.zip (98.7 KB)

4

@h4nneke,

Rather than pushing the whole query down into Oracle, Dremio is breaking it up into 5 separate pushdowns and doing some of the joins itself. The majority of the those 3+ minutes is spent in 2 particular pushdown which can be seen at 08-05 and 07-03 of the “Final Physical Plan” of the “Planning” tab. Why those particular queries would take more time is a bit of mystery. You can cut and paste them from the query profile and run them in SQL Developer to see what the times are there.

As for why Dremio doesn’t push down the whole query, I will have to take a deeper look and get back to you.

5

@h4nneke,

To get a more detailed understanding of why Dremio does not pushdown the whole query, you could stop the server, add the following logger to logback.xml, resart:

<logger name=“com.dremio.exec.store.jdbc”>
   <level value=“${dremio.log.level:-debug}“/>
 </logger>

Then, rerun the query and attach server.log here. This will give more verbose messaging for the JDBC plugin.

If you try this, you will want to remove the logger when your done to avoid spamming your server.log with debug messages.

6

You can also try creating Data Reflections to see if that is faster than a push down.

Here are the docs: https://docs.dremio.com/acceleration/reflections.html

And here’s a tutorial: https://www.dremio.com/tutorials/getting-started-with-data-reflections/

Also, there’s a new course on university.dremio.com for Data Reflections.

7

@kelly @ben Any learnings from this issue?

8

@unni I cannot make any specific claims unless I see a Dremio profile and the same job executed in SQL Developer. SQL Developer probably has a multithreaded read from the RDBMS while Dremio is single threaded, so that may cause the difference in performance.

1 Like