More Web Proxy on the site http://driver.im/

research-article

SQL server column store indexes

Authors:

Per-Åke Larson,

Eric N. Hanson,

Susan L. Price,

Srikumar Rangarajan,

Aleksandras Surna,

Qingqing ZhouAuthors Info & Claims

SIGMOD '11: Proceedings of the 2011 ACM SIGMOD International Conference on Management of data

Pages 1177 - 1184

https://doi.org/10.1145/1989323.1989448

Published: 12 June 2011 Publication History

Abstract

The SQL Server 11 release (code named "Denali") introduces a new data warehouse query acceleration feature based on a new index type called a column store index. The new index type combined with new query operators processing batches of rows greatly improves data warehouse query performance: in some cases by hundreds of times and routinely a tenfold speedup for a broad range of decision support queries. Column store indexes are fully integrated with the rest of the system, including query processing and optimization. This paper gives an overview of the design and implementation of column store indexes including enhancements to query processing and query optimization to take full advantage of the new indexes. The resulting performance improvements are illustrated by a number of example queries.

References

[1]

Abadi, D.J., Madden, S.R., and Ferreira, M.: Integrating compression and execution in column-oriented database systems. SIGMOD, 2006, 671--682.

Digital Library

[2]

Abadi, D.J., Myers, D.S., DeWitt, D.J., and Madden, S.R.: Materialization strategies in a column-oriented DBMS. ICDE, 2007, 466--475.

[3]

Abadi, D.J., Madden, S.R., and Hachem, N.: Column-stores vs. row-stores: how different are they really? SIGMOD, 2008, 981--992.

Digital Library

[4]

Batory, D. S.: On searching transposed files. ACM Trans. Database Syst. 4, 4 (1979), 531--544.

Digital Library

[5]

Copeland, G.P., Khoshafian, S.N.: A Decomposition Storage Model. In Proc. SIGMOD, 1985, 268--279.

Digital Library

[6]

Harizopoulos, S., Liang, V., Abadi, D.J., and Madden, S.: Performance tradeoffs in read-optimized databases. VLDB, 2006, 487--498.

Digital Library

[7]

Jeffrey A. Hoffer, Dennis G. Severance: The Use of Cluster Analysis in Physical Data Base Design. VLDB 1975: 69--8.

Digital Library

[8]

S. Padmanabhan, T. Malkemus, R. Agarwal, and A. Jhingran. Block oriented processing of relational database operations in modern computer architectures. ICDE, 2001, 567--574.

Digital Library

[9]

M. Stonebraker et al. C-Store: A Column-oriented DBMS. VLDB, 2005, 553--564.

Digital Library

[10]

TPC Benchmark DS (Decision Support), Draft Specification, Version 32, available at http://tpc.org/tpcds.

[11]

Aster Data, http://www.asterdata.co.

[12]

ExaSolution, http://www.exasol.co.

[13]

Greenplum Database, http://www.greenplum.co.

[14]

InfoBright, http://www.infobright.co.

[15]

Ingres VectorWise, http://www.ingres.com/products/vectorwis.

[16]

MonetDB, http://monetdb.cwi.n.

[17]

ParAccel Analytic Database, http://paraccel.co.

[18]

SAND CDBMS, http://www.sand.co.

[19]

Sybase IQ Columnar database, http://www.sybase.com/products/datawarehousing/sybasei.

[20]

Vertica, http://www.vertica.com.

Cited By

Pamucar DSimic VGörçün ÖKüçükönder H(2024)Selection of the best Big Data platform using COBRAC-ARTASI methodology with adaptive standardized intervalsExpert Systems with Applications: An International Journal10.1016/j.eswa.2023.122312239:COnline publication date: 1-Apr-2024
https://dl.acm.org/doi/10.1016/j.eswa.2023.122312
Song HZhou WCui HPeng XLi F(2024)A survey on hybrid transactional and analytical processingThe VLDB Journal10.1007/s00778-024-00858-933:5(1485-1515)Online publication date: 4-Jun-2024
https://doi.org/10.1007/s00778-024-00858-9
KUZNETSOV SVELIKHOV PFu Q(2023)REAL-TIME ANALYTICS: BENEFITS, LIMITATIONS, AND TRADEOFFSПрограммирование10.31857/S0132347423010053(3-31)Online publication date: 1-Jan-2023
https://doi.org/10.31857/S0132347423010053
Show More Cited By

Index Terms

SQL server column store indexes
1. Information systems
  1. Data management systems
2. Theory of computation
  1. Theory and algorithms for application domains
    1. Database theory
      1. Database query processing and optimization (theory)

Recommendations

Enhancements to SQL server column stores
SIGMOD '13: Proceedings of the 2013 ACM SIGMOD International Conference on Management of Data

SQL Server 2012 introduced two innovations targeted for data warehousing workloads: column store indexes and batch (vectorized) processing mode. Together they greatly improve performance of typical data warehouse queries, routinely by 10X and in some ...
Real-time analytical processing with SQL server
Proceedings of the 41st International Conference on Very Large Data Bases, Kohala Coast, Hawaii

Over the last two releases SQL Server has integrated two specialized engines into the core system: the Apollo column store engine for analytical workloads and the Hekaton in-memory engine for high-performance OLTP workloads. There is an increasing demand ...
Set Predicates in SQL: Enabling Set-Level Comparisons for Dynamically Formed Groups

In data warehousing and OLAP applications, scalar-level predicates in SQL become increasingly inadequate to support a class of operations that require set-level comparison semantics, i.e., comparing a group of tuples with multiple values. Currently, ...

Comments

Please enable JavaScript to view thecomments powered by Disqus.

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '11: Proceedings of the 2011 ACM SIGMOD International Conference on Management of data

June 2011

1364 pages

ISBN:9781450306614

DOI:10.1145/1989323

General Chair:
Timos Sellis
IMIS/RC Athena
,
Program Chair:
Renée J. Miller
University of Toronto
,
Publications Chairs:
Anastasios Kementsietsidis
IBM T.J. Watson Research Center
,
Yannis Velegrakis
University of Trento

Copyright © 2011 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 12 June 2011

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

SIGMOD/PODS '11

Sponsor:

SIGMOD

SIGMOD/PODS '11: International Conference on Management of Data

June 12 - 16, 2011

Athens, Greece

Acceptance Rates

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

102
Total Citations
View Citations
1,656
Total Downloads

Downloads (Last 12 months)100
Downloads (Last 6 weeks)3

Reflects downloads up to 19 Dec 2024

Other Metrics

View Author Metrics

Citations

Cited By

Pamucar DSimic VGörçün ÖKüçükönder H(2024)Selection of the best Big Data platform using COBRAC-ARTASI methodology with adaptive standardized intervalsExpert Systems with Applications: An International Journal10.1016/j.eswa.2023.122312239:COnline publication date: 1-Apr-2024
https://dl.acm.org/doi/10.1016/j.eswa.2023.122312
Song HZhou WCui HPeng XLi F(2024)A survey on hybrid transactional and analytical processingThe VLDB Journal10.1007/s00778-024-00858-933:5(1485-1515)Online publication date: 4-Jun-2024
https://doi.org/10.1007/s00778-024-00858-9
KUZNETSOV SVELIKHOV PFu Q(2023)REAL-TIME ANALYTICS: BENEFITS, LIMITATIONS, AND TRADEOFFSПрограммирование10.31857/S0132347423010053(3-31)Online publication date: 1-Jan-2023
https://doi.org/10.31857/S0132347423010053
Wang JLi TSong HYang XZhou WLi FYan BWu QLiang YYing CWang YChen BCai CRuan YWeng XChen SYin LYang CCai XXing HYu NChen XHuang DSun J(2023)PolarDB-IMCI: A Cloud-Native HTAP Database System at AlibabaProceedings of the ACM on Management of Data10.1145/35897851:2(1-25)Online publication date: 20-Jun-2023
https://dl.acm.org/doi/10.1145/3589785
Kuschewski MSauerwein DAlhomssi ALeis V(2023)BtrBlocks: Efficient Columnar Compression for Data LakesProceedings of the ACM on Management of Data10.1145/35892631:2(1-26)Online publication date: 20-Jun-2023
https://doi.org/10.1145/3589263
Kuznetsov SVelikhov PFu Q(2023)Real-Time Analytics: Benefits, Limitations, and TradeoffsProgramming and Computer Software10.1134/S036176882301005X49:1(1-25)Online publication date: 27-Mar-2023
https://doi.org/10.1134/S036176882301005X
Sirin UAilamaki A(2020)Micro-architectural analysis of OLAPProceedings of the VLDB Endowment10.14778/3380750.338075513:6(840-853)Online publication date: 11-Mar-2020
https://dl.acm.org/doi/10.14778/3380750.3380755
Noll STeubner JMay NBöhm APorobic DNeumann T(2020)Analyzing memory accesses with modern processorsProceedings of the 16th International Workshop on Data Management on New Hardware10.1145/3399666.3399896(1-9)Online publication date: 15-Jun-2020
https://dl.acm.org/doi/10.1145/3399666.3399896
Semmler NRost MSmaragdakis GFeldmann ADing AMortier R(2020)Edge replication strategies for wide-area distributed processingProceedings of the Third ACM International Workshop on Edge Systems, Analytics and Networking10.1145/3378679.3394532(1-6)Online publication date: 27-Apr-2020
https://dl.acm.org/doi/10.1145/3378679.3394532
Nam YHan DKim MMaier DPottinger RDoan ATan WAlawini ANgo H(2020)SPRINTER: A Fast n-ary Join Query Processing Method for Complex OLAP QueriesProceedings of the 2020 ACM SIGMOD International Conference on Management of Data10.1145/3318464.3380565(2055-2070)Online publication date: 11-Jun-2020
https://doi.org/10.1145/3318464.3380565
Show More Cited By

View Options

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents