Query execution time

Hi @balaji.ramaswamy
I have a performance issue when i run a simple query with INNER JOIN on a Partitioned Tables. the execution time is around 2 mins , while if i run the same query on Non-Partitioned Tables (same table , same data but on different DB) the execution time is around 30 secs.

I have attached the profile info for both databases (with_partition and without_partition)

2212b78c-d864-4f85-a910-1460044900ce-with_partition.zip (213.1 KB)
4db7c25f-1578-44e4-9476-9635598734f9-without_partitions.zip (25.5 KB)

@aabouraya The one that is running longer, the scan is only 3 threads versus the fast one is 90 threads. Also slow ran on only one executor versus fast ran on 10

Hi @balaji.ramaswamy
thanks for your reply
sorry may be i sent a wrong profile for the slow one , as you can see in the attached screen shot , this query uses 90 Thread for scan and 10 executers, i have attached the slow profile too.

7757983e-e434-4ad8-984a-9b354e916a7c-slowone.zip (216.8 KB)

@aabouraya It looks like there are 150K files to read, how small are these files?

@balaji.ramaswamy around 14K

@aabouraya That is extremely small, that is why you are seeing the wait time. I do not see cloud cache getting used. Have you configured?