SQL - Query performance tuning

Transcript

1. SQL - QUERY PERFORMANCE TUNING Understanding how index works
2. DEVELOPERS NEED TO INDEX
3. • Database indexing is a developer task • An index makes the query fast • It requires its own disk space and holds a copy of the indexed table data • Creating an index does not change the table data Index
4. • The database combines two data structures to meet the challenge: a doubly linked list and a search tree • Each node refers to the preceding and the following node. The database can read the index in any direction. • Insert new entries without moving large amounts of data
5. • The index leaf nodes are stored in an arbitrary order. A database needs a second structure to find the entry: a balanced search tree - the B-tree. • The doubly linked list establishes the logical order between the leaf nodes. The root and branch nodes support quick searching among the leaf nodes.
6. • The structure is a balanced search tree because the tree depth is equal at every position; • The database maintains the index automatically. It applies every insert, delete and update to the index and keeps the tree in balance • The tree traversal is a very efficient operation. It works almost instantly - even on a huge data set. That is primarily because of the tree balance, which allows accessing all elements with the same number of steps, and secondly because of the logarithmic growth of the tree depth.
7. • The first problem is the leaf node chain. The database must read the next leaf node to see if there are any more matching entries • The second problem is accessing the table. Even a single leaf node might contain many hits—often hundreds Slow indexes
8. Slow indexes An index lookup requires three steps: 1. The tree traversal; 2. Following the leaf node chain; 3. Fetching the table data.
9. WHERE CLAUSE
10. Example
11. What happens? Modifying
12. Concatenated indexes In general, a database can use a concatenated index when searching with the leading (leftmost) columns
13. Slow index
14. • The first step is the tree traversal on the EMPLOYEES_PK index. That index does not cover the LAST_NAME column • The next step is the fetch the table data operation • Once the LAST_NAME column is available, the database can evaluate the remaining part of the where clause • The statement's response time does not depend on the result set size but on the number of employees in the particular subsidiary.
15. FUNCTIONS
16. Case sensitive Solution:
17. Over-indexing • A single index cannot support both methods of ignoring the case. • To make one index suffice, you should consistently use the same function throughout your application
18. QUESTIONS?
19. THANK YOU!
20. References https://use-the-index-luke.com/ https://www.amazon.co.uk/SQL-Performance-Explained-Markus-Winand/d p/3950307826/?_encoding=UTF8&m=AJFZUYB7XQGQN&tag=winandat-2 1&linkCode=ur2&camp=1634&creative=6738