I have over 500 million records in Azure SQL Data Warehouse. I'm trying to do some benchmark in order to understand in which way keep the records. Rowstore or Columnstore. I'll not join table with other tables, it's not an analytical fact table. Both tables are distributed as a Round Robin and both of them contains 17 partitions. And both of them has 45 columns. When I query to sum two columns, I expect Columnstore table perform much better than rowstore , however the reality is that I get my sum result from Rowstore somewhere around 2.5 min and for columnstore around 10 min. I don't use any filter or group by. On the other hand , when i query count(*) , columnar table performs much much better than rowstore.
EDIT
Though I can't share all the details with you because its private, here is some of them just to have understanding what's going on. I run queries on smallrc and 100DWU. Table is loaded one CTAS and contains pre joined information from several tables and is going to serve queries over custom defined protocol(sort/group/filter/paging) from our internal application. The domain is gambling and from 45 Columns we have 43 could be used as filter. The output set usually contains 3 to 4 columns plus two sum columns with no more than 1000 row per query. I partitioned both tables monthly via EventDate assuming to have each month a new partition. Mostly my queries contains EventDate as a filter. My Rowstroe table contains EventDate as a clustered index in addition to partitions which are the same as for columnstore. Adding EventDate as a secondary index for columnstore gave some improvement but performance still far behind rowstore. EventDate is in int format and values pattern are yyyyMMdd (20180101).
Every DW optimized for elasticity has 60 distributions while the lower skews for DW optimzied for compute also have 60 distributions.
SQL Server's columnstore creates row groups based on row count (as opposed to Parquet for example, where row groups are created based on disk size). Row groups should ideally have 1M rows (see the link that @GregGalloway added), but row groups can get COMPRESSED if they have at least 100k rows loaded in a single bulk load. When a row group is not compressed it is stored in row format in delta stores (they are regular B-trees, with a MD/access overhead since they are part of the columnstore index. Note that you cannot specify the indexing, since they are part of the clustered columnstore index).
I assume that you have 500M rows in 60 distributions, that is 8.3M rows per distribution; assuming your partitioning is homogeneous with 17 partitions you'd have ~490k rows per partition.
When bulk loading into partitioned table you need to be careful about the memory requirements/resource class you're loading with, since the sort iterator on top of the bulk load is not spilling so it will feed the bulk load only that many rows that it can sort.
Ensure that your index has good quality. If you'll do only aggregates over the table without much filtering then 1 partition is ideal, even if you do filtering remember that columnstore does segment elimination so if your data is loaded in the right order you'll be fine.
You should ensure that you have at least a few million rows per partition and that you have COMPRESSED row groups to have good performance. Given your scan results you have most if not all of your columnstore data in OPEN row groups (delta stores).
What do you mean by much better performance in case of count(*)?
Also were these runs cold or warm? If it's a warm run for count(*) CS might be just grabbing the row group MD and up the row count - though in both cases the compiled plans show full table scan.