Please enable JavaScript.

Coggle requires JavaScript to display documents.

Query optimization (Simplification (Trivial search (Statistics update…

- - - - Cost-based optimization
        
        Execution plan generation
        
        Stage 0 - Transaction processing
        
        Stage 1 - Quick plan
        
        Stage 2 - Full optimization
        
        Optimization can be completed at any stage, as soon as a “good enough” plan is found
        
        If thera are less than three tables to join SQL Server'll skip this phase
      - if any auto-updated statistics are outdated and triggers a
        statistics update if needed
      - if the statistics need to be updated synchronously (by default)
        
        no - an optimization is done based on old, outdated
        statistics, while statistics are updated asynchronously in another thread.
        
        Query
        Optimizer waits until the statitistics update is finished
- - - - Merge
        
        works with two sorted inputs. It compares two rows, one at time, and returns their join to the client if they are equal. Otherwise, it discards the lesser value and moves on to the next row in the input. Contrary to nested loop joins, a merge join requires at least one equality predicate on the join keys.
      - Hash join
        
        designed to handle large unsorted inputs
        
        The first phase (build) - scans one of inputs, calculates the hash values of the join key, and
        places it into the hash table
        
        The second phase (probe) - it scans the second input, and checks, or probes, if the
        hash value of the join key from second input exists in the hash table
      - Nested loop
        
        Outer
        
        efficient when at least one of the inputs is small
        
        Inner
    - - Stream - performs the aggregation based on sorted input, for example, when data is sorted on a column,
        which is specified in a group by clause.
      - Hash - It is targeted towards large input and requires memory to store the
        hash table