Questions tagged [columnstore]
For questions about column-oriented storage and access mechanisms in databases. Although the term "columnstore" itself is used by Microsoft and MariaDB for their respective implementations, you should use this tag for questions about other vendors' technologies (e.g. IBM Db2 column-organized tables) and for questions about the technology in general, not specific to a particular vendor.
156
questions
1
vote
1
answer
473
views
What is the most performant way to group by and join high cardinality columns in column based table?
I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
CommunityBot
- 1
1
vote
1
answer
24
views
Should I use a single column with many dimensions with columnar data?
I'm using Apache Iceberg which performs queries on Parquet files. The data saves a lot of types of events and their respective numeric values. I'm hesitating whether it's more efficient to have one ...
- 32k
0
votes
0
answers
47
views
Nonclustered Columnstore Tuple Mover Merge Threshold
I'm learning about columnstore indexes in SQL Server and do some testing to better understand it. For the tests I'm using the StackOverlfow2013 database on SQL Server 2022.
I've created a copy of the ...
- 1,994
1
vote
1
answer
167
views
Deadlock with Reorganize Index
I was running alter index reorganize on some columnstore indexes in SQL Server 2016 and got a deadlock message.
Will reorganize always be the victim? Because I do not want to cause other jobs to fail ...
- 44.3k
8
votes
1
answer
446
views
Why are statistics slower to build on clustered columnstore?
Building statistics on a clustered columnstore table always seems to read the whole table, even if I ask for a small sample. Why is this?
- 80.3k
0
votes
1
answer
25
views
Synapse column store datetime encoding
I have a table in an Azure Synapse database. This table has a clustered columnstore index. It has a datetime column. The minimum value in this column is 2001-03-27 00:00:00 and the maximum is 2022-12-...
- 80.3k
4
votes
3
answers
449
views
Trim_reason = DICTIONARY_SIZE on Clustered ColumnStore index
On a partitioned table with 17 billion rows, I'm getting a very high number of row_groups.
I've heard that I should try to get 1.048.576 rows in each rowgroup.
How do I achieve that?
The trim_reason ...
- 80.3k
0
votes
2
answers
238
views
Why am I getting error 35353 when enabling CDC on a table with a CCI?
I'm trying to enable Change Data Capture on a table with a clustered columnstore index, a unique non-clustered index, and a non-unique non-clustered index in SQL Server Standard 2019. The command ...
CommunityBot
- 1
0
votes
0
answers
25
views
How to compact a whole Rocksdb database over muliple columns?
Simple question but I couldn’t find the answer. Given a multiple columns block based Rocksdb database and the fact the leveldb era ldb tool doesn’t support acting on multiple columns at the same time, ...
- 111
26
votes
1
answer
2k
views
Why MERGE doesn't insert more than 277 records into a table which is configured with temporal table and a non-clustered index on history table
I found again an issue with SQL Server and MERGE statement and need some confirmation.
I can reproduce my issue constantly on a Azure Database (but not on a on premise SQL Server 2017/2019).
Please ...
- 80.3k
0
votes
1
answer
17
views
How to make the first match in a group a different group
I need to group by a certain column and to make the first occurrence into another group.
select * from table;
id
currency
amount
1
USD
1
2
USD
2
3
EUR
3
4
IL
4
5
EUR
5
6
USD
6
So something ...
- 22.8k
14
votes
5
answers
5k
views
Does the order of columns in a columnstore index matter?
I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table.
Will there be any change in performance based on the order of columns that I use ...
0
votes
0
answers
50
views
Setting up an HTAP Environment
I've had success in the past with setting up a CIX/PK on an identity column and then setting up supporting NCIX and NCCsIX on tables to help support an HTAP environment.
I'm curious, going forward ...
3
votes
2
answers
2k
views
How Ola Hallengrens Index maintenance scripts handles columnstore indexes?
I'd like to know how Ola's script handles columnstore indexes. Couln't find any information about that, only in the version history it was mentioned that script does have a support.
My experience is ...
- 1,264
0
votes
1
answer
76
views
Error loading data in MariaDB Columnstore
I have a MariaDB Columnstore database running on the cloud in Linux. I'm trying to export a CSV file from my Windows machine to the database. I created the file with DBeaver, setting the new line as \...
7
votes
1
answer
727
views
Why is statistics update slower on a clustered columnstore than on a rowstore?
I've moved several large tables (each with >10^9 rows and a couple of dozens columns) from clustered rowstore to clustered columnstore indexes on a SQL Server 2014 instance and noticed that ...
- 80.3k
4
votes
1
answer
244
views
Columnstore index predicate is not used in index scan
We are using a clustered columnstore index table with ~50M records and experiencing big performance degradation when running on GCP cloud sql vs. running locally using the same db schema and data (...
- 80.3k
4
votes
1
answer
364
views
Why significantly worse Column Store performance on Read-only AG secondary DB?
We have a pair of SQL 2019 Enterprise instances hosted in identically spec'd and configured VMs in Azure.
They for a simple primary/secondary availability group array, containing a small number of ...
- 80.3k
0
votes
0
answers
46
views
Adding new column to clustered column store indexed table
I designing clustered column store indexed fact tables.
Later on many new columns might be added to these currently ~90 columns tables.
How adding new nullable column is affected by the size (number ...
- 266
0
votes
1
answer
94
views
How to change default compression mode in mariaDB Columnstore?
In mariaDB there is a way to disable columnstore compression in session level (and set the disable flag in each session). Is there any way to change the default compression mode?
- 74.8k
0
votes
0
answers
202
views
Is a columnstore index appropriate when all columns are (mostly) unique?
I have a longish table ( 60M rows ) where there are only 7 columns. One is a unique ID, two are datetimes, and two are notes and descriptions.
There the notes and descriptions are very regular, except ...
- 101
3
votes
2
answers
458
views
Columnstore: terrible execution plan - filter instead of seek
See the execution plan on https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise)
I have a datawarehouse table peak_reporting_data which tracks the activity per day and hour and ...
- 845
1
vote
1
answer
117
views
What does column family-oriented datastore mean in practice
I was reading this article about HBase. Within it, the storage format of HBase is described:
HBase is referred to as a column family-oriented data store. It’s also row-oriented: each row is indexed ...
CommunityBot
- 1
0
votes
0
answers
387
views
UPDATE on a clustered COLUMNSTORE table with 150 columns and 30 mln records just stuck
I have a table TABLE_A which is having 150 columns (with 5 lob type) and 30 millions. User were facing read performance issue, so I created CLUSTERED COLUMNSTORE on the table. After I converted the ...
- 1,922
6
votes
1
answer
2k
views
Clustered columnstore index space usage
I have a simple table with a clustered columnstore index:
ID INT NOT NULL,
Hash BINARY(20) NOT NULL
This table has some billion rows and according to sp_spaceused, sys.allocation_units and SSMS ...
- 178k
0
votes
1
answer
240
views
Clustered Column Store Index - does it have max row size limitation?
Updatable Clustered Column Store Index (SQL Server 2016+) - does it have max row size (8060) limitation, or you can put any row size you want in there ?
In other words, is it possible to get below ...
- 5,182
15
votes
1
answer
444
views
Columnstore Index in read_only filegroup prevents CheckDB
It appears setting a filegroup to read_only prevents dbcc checkdb for the entire database if the filegroup contains a columnstore index. When attempting to run checkdb or checkfilegroup (for any ...
- 80.3k
0
votes
1
answer
166
views
Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements
My Table tbl_FieldeData_ColStore Data Example:
/NonClustred Column Store Index on Table/
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore
(
PDID,FieldID
)
/Below Select was ...
- 32k
0
votes
2
answers
72
views
Columnar DBMS and issue understanding multiple where constraints
I read a lot about columnar DMBS and perfectly understand that they are great for researches on a column.
This question has a partial answer.
Now, say I have a DMBS with 3 columns A, B and C.
Doing a ...
- 80.3k
0
votes
0
answers
86
views
"Error opening column file" in MariaDB Columnstore data cpimport
I'm trying to import a large file, around 2GB, into MariaDB Columnstore. Other files that are smaller worked fine, so I suspect it's a size issue. I create the table with engine Columnstore with no ...
- 51
0
votes
0
answers
44
views
Database Page Compression and ColumnStore Archive - is data compressed when in buffer pool and when sent to client? [duplicate]
One of benefits of Page Compression or ColumnStore Archive is reduced storage space requirements
Here I have two questions:
When compressed data is read from disk to buffer pool, in the buffer pool ...
- 5,182
0
votes
0
answers
183
views
Clustered Columnstore Index alignment on multiple columns
I have a fact table which is a clustered columnstore index.
It is also:
-partitioned by a single data date column.
-has a nonclustered, aligned, composite unique index (PK) on 6 INT columns, plus the ...
- 266
1
vote
1
answer
560
views
Slow inserts in mariadb columnstore
I have just started on researching the feasibility of using MariaDB's columnstore for OLAP, and I find inserts are very slow. This is MariaDB 10.5 on a debian 10 system, just an elderly desktop with ...
- 24.3k
0
votes
2
answers
287
views
Storing Live Timeseries Data in SQL Server Azure Database
I have an SQL Server Azure Database and am trying to store time-series data that I get from multiple sensors.
Data Source:- 5-minute data is obtained via an API.
Current table structure: -
Timestamp |...
CommunityBot
- 1
1
vote
1
answer
568
views
MariaDB Column Store Suitability for Data Warehouse
We want to build a data warehouse to distribute valid information across the organization (reporting) and get some insights into our data (analytics). After some research we found MariaDB using Column ...
- 74.8k
0
votes
1
answer
561
views
Msg 823, Level 24, State 2 for Clustered Column Store Index in SQL Server 2014
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TempTable1 ON dbo.TempTable1 ON[PRIMARY]
After firing this query I found the error.
Msg 823, Level 24, State 2, Line 20
The operating system returned error 1(...
CommunityBot
- 1
0
votes
1
answer
79
views
Optimize BETWEEN Query in SQL Server Azure
I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:-
Timestamp | ComponenentID | Parameter1 | ...
- 3,703
0
votes
1
answer
142
views
Is columnstore a good option for UPDATES with WHERE clauses?
I am developing a database project on SQL Server and I am thinking whether using columnstore index is a good idea.
The project consists of a table (A) that will hold a large number of rows, with many ...
- 16.5k
2
votes
2
answers
798
views
How does a column-oriented DBMS filter on multiple columns?
I'm learning how column-oriented DBMS / "columnars" work for OLAP situations.
Let's say we have a log of millions of transactions with 3 columns: timestamp, shop, product, and we want to ...
- 24.3k
2
votes
2
answers
527
views
What is the correct and performant pattern for loading (and querying) a large clustered columnstore table?
I am doing some test with clustered columnstore tables - both partitioned and not.
In my research, I've seen flavors of this pattern:
-- step 1: make new table
SELECT TOP 0 * INTO [Fact].[Order_CCI] ...
- 69.1k
5
votes
2
answers
433
views
SQL Server 2019 column store indexes - maintenance
I have a clustered columned stored index on table used for logging - only inserts (but not bulk inserts).
The current table stats are:
3541 millions rows
6.6 GB reserved space
I see this morning the ...
- 44.3k
0
votes
1
answer
97
views
Foreign keys without any checking or index
I have a problem on SQL Server that led me to the question of whether it is possible to define a foreign key that is not enforced, not backed by an index, and does not need the referenced columns to ...
CommunityBot
- 1
5
votes
2
answers
785
views
Clustered columnstore index on small tables
Clustered column store indexed tables in general are useful for large tables. Ideally with million of rows.
And also useful with queries, which selects only the subset of available columns in such ...
- 32k
4
votes
1
answer
988
views
Table partitioning and future values
I have a clustered columnstore table with approximately 1.4 billion rows.
I figured it being a clustered columnstore would best suit my need for various aggregations on many different splits/columns.
...
- 69.1k
1
vote
1
answer
142
views
Could database with compat level 100 support columnstore index?
I have an instance which runs on SQL Server 2017 Developer Edition instance. I restored database ContosoRetailDW which had compatibility level 100. After restore operation, I run the following query:
...
- 16.5k
0
votes
1
answer
56
views
Does binary logging apply to ColumnStore?
When binary logging is enabled, does MariaDB's binary logging also record ColumnStore events, and not just InnoDB events? For example, does it log an UPDATE made to a ColumnStore table row?
I've ...
- 16.6k
6
votes
2
answers
723
views
Columnstore index and low selective columns
I have a table with almost 1T rows.
create table bigtable (
K1 int, K2 date, -- PK columns
C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ...
B1 bit, B2 bit, ........
- 80.3k
-1
votes
1
answer
324
views
Column store index for on premise Kimball Data Warehouses
Are column store tables now the vendor default method for Kimball Data Warehouse tables with Dim and Facts for On Premise?
I know in the cloud, Microsoft automatically recommends Azure Data warehouses ...
- 80.3k
1
vote
1
answer
313
views
Eliminate filter operator before columnstore index scan operator
I have a large fact table with millions of rows called MyLargeFactTable,
and its a clustered column store table.
There is a composite primary key constraint on it there as well
(customer_id,...
- 80.3k
8
votes
4
answers
943
views
Has the guidance on the setting for 'cost threshold for parallelism' changed with the advent of columnstore indexes?
First off, what I'm not asking. I'm not asking what my setting should be.
Many are recommending upping the value past the default, and I certainly understand why that's the case for B-Tree based ...
- 31.7k