I am using dremio 4.6 with Microstrategy installed on UNIX CENTOS 7
When I run a query on dremio from Microstrategy, using JDBC connection
the query take 3m:43s
When I run the same query on dremio from Microstrategy using ODBC connection
the query takes 41m:07s and got canceled !
here is the two queries profiles
profile_jdbc.zip (1,3 Mo) profile_odbc.zip (1,3 Mo)
so I am wondering why is it fast with JDBC connection and very slow with ODBC CONNECTION ?!
is it a Microstrategy problem when working with dremio ODBC connection ?!
it can’t be a network bottleneck because so two queries are issued from the same host
is my ODBC connection settings (bellow in ODBC.ini) for dremio connections not sufficient ?
for ODBC connection, I am using the version dremio-odbc-1.5
for JDBC connection, I am using the version dremio-jdbc-driver-11.0
both come with dremio 4.6
In my Microstrategy server ODBC.ini file, I have this settings for dremio odbc connection
Hope you can help me
If you see the job profile for the ODBC run, even though it was still running at 40 minutes, the query execution on the Dremio side took < 2 minutes, the rest of the time ,Dremio was waiting on the ODBC client to consume the records, see phase 0 blocked on downstream, see below image (you can also see the record processing rate on the Dremio is fast)
Next, if you scroll down below the operators table and expand the SCREEN operator then you can see that Dremio is trying to send 3 GB of data and the ODBC client or the network is too slow, see attached image
On the other hand if you see the JDBC phase 0 metrics, although it is receiving more or less same number of records, the consumption rate is much faster and hence the blocked on downstream is significantly less, see attached image
The screen operator suggests the same, see attached image
In fact if you run the query via the UI, it should run even faster
Kindly check if the JDBC client is on a faster network path or probably consuming the records faster
Another thing to note is that a typical use case would be to first process the data in Dremio using Dremio’s execution engine under a Semantic layer (aggregated), then send only aggregated data back to the dashboard (ODBC), now at times we want to get details which one or two queries might get slower but 95% of your queries will be vey quick as it would return less records and accelerated by an aggregate reflection
Kindly let me know if you have any other questions