Indexed View Technique to Speed-up Data Warehouse Query Processing

M. J. Al Taleb, Talib; Hasan, Sami; Yousif Mahdi, Yaqoob

doi:10.22042/isecure.2021.271059.615

Indexed View Technique to Speed-up Data Warehouse Query Processing

Document Type : Research Article

Authors

Talib M. J. Al Taleb

Sami Hasan

Yaqoob Yousif Mahdi

College of Information Engineering, Al-Nahrain University, Baghdad, Iraq.

https://doi.org/10.22042/isecure.2021.271059.615

Abstract

The data warehouse size and query complexity may cause an unacceptable delay in decision support queries. A basic condition for the success of a data warehouse is the capability to supply decision-makers with both precise information and best response time. For this purpose, the concept of indexed views is used. Indexed views help to speed-up query processing and reduce the response time for tracing queries, especially for queries about past histories. Indexed views may significantly by reducing the amount of work that SQL Server to return the required data and im- prove query performance and solve the problem of decision support workloads. this article is applicable to data warehouse de-signed by using SQL Server. It is not applicable if the data warehouse was designed by using another software like Oracle, DB2 and Informix. For many years, Mi- Microsoft SQL Server has supported the ability to create virtual tables known as views.

Keywords

Data Warehouse (Star Schema)

Indexed View

Query Processing

20.1001.1.20082045.2020.12.3.11.1

[1] Ralph Kimball, Margy Ross, Warren Thornthwaite, Joy Mundy, and Bob Becker. The data warehouse lifecycle toolkit. John Wiley & Sons, 2008.

[2] Paulraj Ponniah. Data warehousing fundamentals: a comprehensive guide for IT professionals. John Wiley & Sons, 2004.

[3] Lekha Narra, Tony Sahama, and Peta Stapleton. Clinical data warehousing: A business analytics approach for managing health data. In Proceedings of the 8th Australasian Workshop on Health Informatics and Knowledge Management [Conferences in Research and Practice in Information Technology (CRPIT), Volume 164], pages 101– 104. Australian Computer Society, 2015.

[4] Osama El-Sayed Sheta and Ahmed Nour Eldeen. Building a health care data warehouse for cancer diseases. arXiv preprint arXiv:1211.4371, 2012.

[5] A Patel and J Patel. Data modeling techniques for data warehouse. International Journal of Multidisciplinary Research, 2(2):240–246, 2012.

[6] Ross Mistry and Stacia Misner. Introducing Microsoft SQL Server 2014. Microsoft Press, 2014.

[7] Andrew Brust and Leonard G Lobel. Programming Microsoft SQL Server 2012. Pearson Education, 2012.

[8] Goetz Graefe. Sorting and indexing with partitioned b-trees. In CIDR, volume 3, pages 5–8, 2003.

[9] Jason Strate and Ted Krueger. Expert performance indexing for SQL server 2012. Apress, 2012.

[10] Itzik Ben-Gan, Lubor Kollar, Dejan Sarka, and Steve Kass. Inside Microsoft SQL Server 2008 T-SQL Querying: T-SQL Querying. Microsoft Press, 2009.

[11] Gang Gou, Jeffrey Xu Yu, and Hongjun Lu. A/sup*/search: an efficient and flexible approach to materialized view selection. IEEE Transactions on Systems, Man, and Cybernetics, Part C (Applications and Reviews), 36(3):411–425, 2006.