Hi! I am using Dremio software community edition v23.0 in my EKS cluster. I have a virtual dataset that uses a complex query that involves 6 joins of tables with millions of rows. Every time I try to query it takes like 20 minutes to load so I wanted to create reflections to speed it up, but my reflections are never used.
This is how my query looks like:
SELECT distinct colum1, colum2,…colum50
FROM (
SELECT * FROM
Space.“virtual_dataset_name_1”
left join (SELECT * FROM Space.“virtual_dataset_name_2”
inner join Space.“virtual_dataset_name_3”
on column1 = column2)
…
left join Space.“virtual_dataset_name_6”
on aipd.column6 = column7
) nested_0
I have tried creating a raw reflection of the whole virtual dataset but that doesn’t use the reflection, also I tried creating a raw reflection of the virtual dataset Space.“virtual_dataset_name_2” which is queried inside the complex query of the virtual dataset I want to speed up. Lastly I tried creating a reflection of the physical dataset of one of the joins.But the reflection is never used. What am I doing wrong?
@jbaranda Can you please send job profiles for
- Query that ran before reflection was created
- REFRESH REFLECTION job
- Query that ran after reflection was created but reflection was not leveraged
@jbaranda If you are having issues with reflection matching on v23 especially with VARCHAR data types, then you should upgrade to v24.
Hi! Here I attach all the jobs. Here you can see that the reflection is not used. I am sorry for the late reply, I didn’t saw the message replies.
query_profile_after_reflection.zip (326.0 KB)
reflection_profile.zip (335.2 KB)
@jbaranda I see the reflection (RAW) is on VDS STP.meta_denormalized_optimized
This VDS does not appear in your plan. Is the logic inside this VDS same as the query or one of the VDS’s used in the query so we expect Dremio to match?
Hi! Yes the reflection is on VDS STP.meta_denormalized_optimized. What do you mean by the logic inside the VDS? I created the raw reflection of the VDS that was created with the same query of STP.meta_denormalized_optimized. This query at the same time is querying other VDS which do not have RAW reflections themselves. In my first post (message) you can see how my query looks and what things I have tried.
@jbaranda As I said before, reflection matching is broken on v23 CE edition. You need to upgrade to v24 which is out already. Please don’t use v23 CE edition with reflections.