SQL ‐ Db Concepts | Optimization
read
Optimizing SQL for data sets is relevant no matter the size of the data set, but it’s especially crucial when dealing with large unstructured data.
Ignoring optimization can lead to issues like slow query execution, high resource consumption and potential data loss. By optimizing SQL, you manage the performance of your database in terms of speed and space efficiency, allowing faster data retrieval from larger tables within shorter time frames.
During a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations and can be resource intensive.
Well-designed indexes can reduce disk I/O operations and consume fewer system resources. Therefore, these indexes improve query performance. Indexes can be helpful for various queries that contain SELECT, UPDATE, DELETE, or MERGE statements.
When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table. Unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.
The query optimizer typically selects the most efficient method when executing queries. However, if no indexes are available, the query optimizer must use a table scan. Your task is to design and create indexes that are best suited to your environment so that the query optimizer has a selection of efficient indexes from which to select.
- Avoid using SELECT*.
- Choose the correct JOIN operation: These include left join, inner join, right join and outer join.
- Use common table expressions.
- Manage data retrieval volume with LIMIT and TOP.
When considering database indexing, both clustered and non-clustered indexes play a crucial role in determining your database’s performance. The precision and strategic implementation of these indexes are vital aspects that significantly impact the overall efficiency of your database system. It’s important to know when to use each type of index to maximize the benefits they bring.
The primary benefits of nonclustered indexes are that you can have hundreds of them per table if necessary, and they're "cheaper" during write operations because less disk IO is involved. In general, you want to have a clustered primary key comprised of one or (very few) more columns whose values will rarely change. Columns that are frequently updated should be covered by nonclustered indexes instead. This gives your common select queries the performance boost of the clustered index, without slowing your updates way down.
A clustered index organizes the data rows on the disk to mirror the index’s order. This leads to highly efficient data retrieval for columns that are frequently accessed. Think of it like a well-organized bookshelf where every book is exactly where you expect it to be. However, a table can only have one clustered index, so it’s crucial to apply it judiciously. It’s best to apply it to columns that are frequently searched for, and the data is largely unique.
- With a clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index.
- Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.
With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records.
Beyond the classic index types, there’s a specialized approach for text columns: full-text search indexes. Optimizing text columns with these indexes elevates your database’s search capabilities beyond simple pattern matching, enabling complex searches through large volumes of text with remarkable ease. It’s essential to harness the full potential of these text-specific indexes to ensure that your database remains as responsive as the speed of thought.
You can implement this by creating a full-text index on your database table, which indexes text columns based on the words and phrases they contain. This allows for faster search results when looking for specific words or phrases in large blocks of text, with minimal overhead.
