Databases are designed in different ways. Most of the time we can use “school examples”: normalize the database and everything will work just fine. But there are situations that will require another approach. We can remove references to gain more flexibility. But what if we have to improve performance when everything was done by the book? In that case, denormalization is a technique that we should consider. In this article, we’ll discuss the benefits and disadvantages of denormalization and what situations may warrant it.
Introduced in SQL 2012, ColumnStore indexes differ greatly from standard row-based indexes. Intended for OLAP systems, these indexes store data in a highly compressed, segmented fashion with the column as the basis (rather than typical row-based indexes). This type of column-based index allows for great performance gains in data warehouses where table scans, rather than seeks, are performed.ColumnStore indexes have evolved significantly over the last few SQL Server versions:
This article reviews optimal placement of clustered and nonclustered indexes on OLTP databases, and explains how filtered indexes can be used to improve performance.Clustered IndexesBy default, SQL Server will create the table’s clustered index during the creation of the primary key:This can be overridden by specifying the NONCLUSTERED keyword during creation:The HEAP exists because the table does not have a clustered index defined.It’s a common misconception that the primary key and clustered index are bound together; the primary key is a special type of unique constraint (it does not allow NULL values, while normal unique constraints do). The Clustered index is a strictly-ordered B-tree intended to maximize scan performance. Only one clustered index per table is permitted.
The SQL Language seems easy to learn – the commands follow a simple syntax and don’t describe the specific algorithms used to retrieve data. The simplicity might, however, be deceptive. Not all the database functions operate with the same efficiency. Two very similar queries can vary significantly in terms of the computation time. This article presents some of the best practices that can greatly boost your SQL queries.1. Learn How to Create Indexes Properly
The concept of views and function-based indexes has been known for many years. One of the brand new solutions is a virtual column – a feature introduced in Oracle 11g. Apart from database giant, some well known DB vendors, like MariaDB and SQL Server, support the idea of computed columns. So let’s give virtual columns a try and examine their basic usage.Generally, there are two kinds of virtual columns:Virtualvirtual columnPersistentvirtual column
Sooner or later there comes a moment when database performance is no longer satisfactory. One of the very first things you should turn to when that happens is database indexing. This article will give you a general overview on what indexes are without digging into too much detail. We’ll discuss additional database index topics in future articles.In general, a database index is a data structure used to improve queries execution time. To explain what an index is, we need to say a few words on how the data stored in tables is organized.
I remember my own confusion about who was responsible for database indexing when I was a junior programmer some years ago. At one of my very first commercial projects, software architects created a database structure, developers wrote the code, and browser magicians made it look outstanding. The final product was deployed to servers and champagne corks popped. Our good mood didn’t last for too long…After three months or so it turned out that one of the main features, a search engine, started choking. A rather simple search query took a few seconds to run. The time for optimization came very quickly. After some fast profiling with the production data and the cause of the slowdown became obvious – it was the DB that consumed too much time.
The concept ofmaterialized views (MVs)is almost 15 years old; Oracle first introduced these views in the 8i version of its DBMS. However, some well known DB vendors (like MySQL) still don’t support MVs or have added this functionality only quite recently (it’s available in PostgreSQL since version 9.3, which was released just a year ago). In this article I’ll try to give you some tips about when you should use MVs in OLTP systems.
Some time ago, the Vertabelo Team participated in the PostgreSQL Conference Europe 2013 . Some of the talks were really nice. One of them stuck in my head for quite a long time. It was Markus Winand’s lecture titled“Indexes: The neglected performance all-rounder.”Although I had had a solid background in databases, this 50 minutes long talk showed me that not everything concerning indexes was as clear to me as I had thought. This was the kind of lecture I like the most – when you sit and say to yourself “damn, I didn’t know that!”