urgent - synapse partition table no performance difference?
one of synapse table we've 300 million rows and keep increasing. Every row as status column i.e active_row either 0 or 1. Active_row is int datatype. Users only query based active_row = 1 which has only 28 million row and rest of data i.e 270 million is inactive.
To increase the performance and avoid to full tablescan on active_row, i've converted the table in partition table on active_row as below
CREATE TABLE [repo].[STXXXXX]
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED INDEX (
[ID] ASC
),
PARTITION
(
active_Row RANGE LEFT FOR VALUES (0,1)
)
)
as
select * from repo.nonptxx;
Users reported there is no performance improvement after moving to partition table. when i checked the below query i.e partition vs non-partition i don't see any difference in query explain plain interms of estimated sub tree, operation etc and all stats remain same figure. From sys.dm_pdw_nodes_db_partition_stats i can see 3 partition created on partition 1 having 270 million data spilt in 60 nodes and partition 2 of 60 nodes 30 million spilted and partition 3 of 60 nodes is empty.
> select * from [repo].[STXXXXX] where active_row =1
vs
> select * from repo.nonptxx where active_row =1
Please advise what's wrong and why there is no improvement after moving into partition table and how to tune it?